Sql语句在oracle SQL开发人员工具中运行，没有任何错误，但是当从程序调用时出现相同的语句抛出错误。 [英] Sql statement runs in oracle SQL developer tool without any error but same statement when called from program throws error.

问题描述

我在VB 
 
中有一个插入语句strSTMT =INSERT INTO TABLE_ONE（T_NUM，T_BATCH_ID，CREATE_DATE，LAST_UPDATE_DATE）& _ 
SELECT'& iStoreNum& '，T_BATCH_ID，SYSDATE，SYSDATE FROM Tbl_BATCH a& _ 
WHERE T_BATCH_TYPE ='C'& _ 
AND T_BATCH_AUTO_ASSIGN ='Y'& _ 
AND NOT EXISTS（从TABLE_ONE中选择ROWID，其中T_NUM =& iStoreNum& _ 
和a.T_BATCH_ID = b.T_BATCH_ID）
 
每当我试图执行该语句，它抛出异常：
 
 ORA-06550：第1行，第351列：
 PL / SQL：ORA-00933：SQL命令未正确结束
 ORA -06550：第1行第7列：
 PL / SQL：SQL语句忽略
 
我使用相同的语句并在Oracel SQL开发工具中执行，
它不会抛出任何错误。 

我的尝试：



我甚至结束了声明;半冒号，但没有奏效。

为了让人更清楚，应用程序是在.NET 2.0上编写的，我将其更改为4.5。之前的应用程序正在恢复到oracle.dataaccess.client dll但是因为我不再支持我安装了OracleManagedAccess客户端，是不是因为这个？

解决方案

1 。将strSTMT替换为OracleCommand对象，传入SQL查询。

2.更改要查询的SQL查询，例如

SELECT：iStoreNum，

和

WHERE T_NUM =：iStoreNum等等。

3.在执行Oracle Command之前构造Oracle Parameters对象。

以下是一个示例：

c＃ - OracleCommand SQL参数绑定 - 堆栈溢出 [ ^ ]

您的查询似乎错了

< pre> strSTMT =   INSERT INTO TABLE_ONE（T_NUM，T_BATCH_ID，CREATE_DATE，LAST_UPDATE_DATE）& _ 
   SELECT'& iStoreNum&  '，T_BATCH_ID，SYSDATE，SYSDATE FROM Tbl_BATCH a& _ 
   WHERE T_BATCH_TYPE ='C'& _ 
   AND T_BATCH_AUTO_ASSIGN ='Y'& _ 
   AND （缺少列名） NOT EXISTS（从TABLE_ONE中选择ROWID） b WHERE T_NUM =& iStoreNum& _ 
  和a.T_BATCH_ID = b .T_BATCH_ID） 

你在某个条件下错过了一个列名，表示一个更改正确的列名（缺少列名）



让我知道它是否无法正常工作。

I have an Insert Statement in VB

strSTMT = "INSERT INTO TABLE_ONE (T_NUM, T_BATCH_ID,CREATE_DATE,LAST_UPDATE_DATE) " & _
"SELECT '" & iStoreNum & "', T_BATCH_ID,SYSDATE,SYSDATE FROM Tbl_BATCH a" & _
" WHERE T_BATCH_TYPE = 'C'" & _
" AND T_BATCH_AUTO_ASSIGN = 'Y'" & _
" AND NOT EXISTS (SELECT ROWID FROM TABLE_ONE b WHERE T_NUM = " & iStoreNum & _
" AND a.T_BATCH_ID = b.T_BATCH_ID )"

whenever i tried to execute the statement, it throws exception : 

ORA-06550: line 1, column 351:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored

I took the same Statement and executed in Oracel SQL developer tool, 
It does not throws any error. 

What I have tried:

I even ended the statement with ; semi colon, but did not work.
To shed more light,Application was written on .NET 2.0, I changed it to 4.5. Previously app was refrencing to oracle.dataaccess.client dll but since it isn't supported any more I installed OracleManagedAccess Client, is it because of this?

解决方案

1. Replace the strSTMT with a OracleCommand object, passing in the SQL query.
2. Change the SQL Query to be parameterised e.g.
SELECT :iStoreNum,
and
WHERE T_NUM = :iStoreNum and so on.
3. Construct the Oracle Parameters objects on the Oracle Command before executing it.
Here is an example:
c# - OracleCommand SQL Parameters Binding - Stack Overflow[^]

Your query seems wrong

<pre>strSTMT = "INSERT INTO TABLE_ONE (T_NUM, T_BATCH_ID,CREATE_DATE,LAST_UPDATE_DATE) " & _
"SELECT '" & iStoreNum & "', T_BATCH_ID,SYSDATE,SYSDATE FROM Tbl_BATCH a" & _
" WHERE T_BATCH_TYPE = 'C'" & _
" AND T_BATCH_AUTO_ASSIGN = 'Y'" & _
" AND (Missing Column Name) NOT EXISTS (SELECT ROWID FROM TABLE_ONE b WHERE T_NUM = " & iStoreNum & _
" AND a.T_BATCH_ID = b.T_BATCH_ID )"

You missed a column name in where condition, Indicate a correct column name instead (Missing Column Name)

Let me know if it will not going to work.

这篇关于Sql语句在oracle SQL开发人员工具中运行，没有任何错误，但是当从程序调用时出现相同的语句抛出错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！