CODEWITHSUNDEEP
oracle-databasevbaexcelodbc
logan
logan

Reputation: 8346

Oracle database query throws error on second run

i have an VBA code where i am calling oracle to retrieve data twice using ODBC. First data retrieval is fine. But 2nd time it is saying ,

**RunTime Error '-2147467259 (80004005)'; Unspecified error**

My Code is as follows,

Note: Same code works for connecting Teradata but failed when i use Oracle

'First Data retrieval
Query1 = "Select TableName from all_tables"
CmdSQLData.CommandText = Query1
CmdSQLData.CommandType = adcmdText 
CmdSQLData.Timeout=0
set rs = CmdSQLData.Execute()
'Then code to store data ...
'This part gives proper result ...
rs.close()

'Second Data retrieval
Query2 = "Select * from db.Event"
CmdSQLData.CommandText = Query2
CmdSQLData.CommandType = adcmdText 
CmdSQLData.Timeout=0
set rs = CmdSQLData.Execute() 'This line Gives Error - RunTime Error '-2147467259 (80004005)'; Unspecified error

Also i tried creating new command object as cmdSQLData1 but still same error

May i know why the error is coming for second query ? There is no problem with query as i have tested in oracle directory. Please let me know

Upvotes: 3

Views: 1431

Answers (2)

Richard Spencer
Richard Spencer

Reputation: 631

You do not need to use command text at all for those types of queries what you could do is :-

` Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset

con.Open "DSN=Oracle", "User", "Password"
rs.Open "select * from table_a", con

' Read all results

rs.Close

rs.Open "select * from table_b", con
' Read all results

rs.Close

con.Close

You only need to use "Command" if you plan to use a store procedure or a query with bound parameters.

Upvotes: 0

Dan
Dan

Reputation: 11069

You won't see this documented much of anywhere, but reusing Command objects with different comamndText is actually a bad practice. You don't say what kind of connection you're using, but for example if it's ODBC, this will internally send a fake invalid SQL to Oracle to force a cleanup of some kind. So instead, throw away your Command object after use and create a new one.

Reusing Command objects is a good practice when you're re-executing the same query with different parameter values, but that's not the case here.

Upvotes: 1

Related Questions