Oracle数据库查询在第二次运行时抛出错误 [英] Oracle database query throws error on second run

问题描述

我有一个VBA代码，我在使用ODBC调用oracle来检索数据两次。
第一次数据检索是罚款。但是第二次说，

  ** RunTime Error'-2147467259（80004005）';未指定的错误** 
  

我的代码如下，

注意：相同的代码适用于连接Teradata，但是当我使用
Oracle时，失败Oracle

 '第一个数据检索
 Query1 =从all_tables中选择TableName
 CmdSQLData.CommandText = Query1 
 CmdSQLData.CommandType = adcmdText 
 CmdSQLData .Timeout = 0 
 set rs = CmdSQLData.Execute（）
'然后代码存储数据... 
'这部分给出正确的结果... 
 rs.close（ ）
 
'第二个数据检索
 Query2 =从db.Event中选择*
 CmdSQLData.CommandText = Query2 
 CmdSQLData.CommandType = adcmdText 
 CmdSQLData .Timeout = 0 
 set rs = CmdSQLData.Execute（）'这一行给出错误 -  RunTime错误'-2147467259（80004005）';未指定的错误
  

此外，我尝试创建新的命令对象作为cmdSQLData1但仍然相同
错误

我可以知道为什么错误来了第二个查询？
我已经在oracle目录中测试了查询没有问题。
请让我知道

解决方案

你不会在任何地方看到这些记录，但是重复使用命令具有不同comamndText的对象实际上是一个不好的做法。你不会说你正在使用什么样的连接，但是例如如果是ODBC，那么内部会向Oracle发送一个假的无效SQL，以强制执行某种清理。所以相反，使用后抛出你的命令对象，并创建一个新的。

重用对象是一个 实践，但这不是这种情况。

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

解决方案

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.

这篇关于Oracle数据库查询在第二次运行时抛出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！