Proc中打开游标：ORA-01722

Question

memset(inqstr, SETMEM, sizeof(inqstr));
strcpy(inqstr, "2,3");  /* 如果改为strcpy(inqstr, "2");则在open的时候不会报错ora-01722*/

EXEC SQL DECLARE inqKindData1 CURSOR for
SELECT MERCHANT_ID,sum(TRANS_CNT_V),sum(TRANS_AMT_LOC_V),sum(TRANS_CNT_M),sum(TRANS_AMT_LOC_M),
sum(TRANS_CNT_T),sum(TRANS_AMT_LOC_T),sum(TRANS_NET_AMT),sum(TRANS_COMM_AMT),sum(TRANS_BANK_COMM_AMT),
sum(BANK_GRAND_TOTAL_AMT),sum(BANK_DCC_INCOME_T),sum(MERCHANT_BACK_AMT_T)
FROM TB_MER_STATISTICS_D
WHERE REPORT_DATE >= :date_s AND REPORT_DATE <= :date_e
AND DISTRICT_CODE like :inqdiscd AND LEGWORK_ID in (:inqstr)
GROUP BY MERCHANT_ID;
EXEC SQL OPEN inqKindData1;/*这里会报错ora-01722*/
字段LEGWORK_ID是NUMBER型的。为什么一个数字就是正常的，而多个数字就会报错？
请问这是哪里出了问题？谢谢

Answer 1

或许是因为"2,3"不能转换成数字，但是"2" 可以转换成数字。
如果 条件a (defined as int) = '2',oracle会自动把'2’转换成为数字2的。搂主可能要动态sql了。
ORA-01722: invalid number 
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates. 
Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.  

Answer 2

谢谢welyngj
好像不需要用到动态sql就可以了，我是这样实现的：
将and LEGWORK_ID in [color=#FF0000](:inqstr)
改为and instr(:inqstr, TO_CHAR(LEGWORK_ID))>0
就可以了
请问这样正确吗？
谢谢

Answer 3

and instr(:inqstr, TO_CHAR(LEGWORK_ID))>0 
改成
and instr(','||:inqstr||',', ','||TO_CHAR(LEGWORK_ID)||',')>0 上面的容易出现错误数据，比如前面的串是123,344,234 后面如果是12，实际上应该是不匹配，但上面的会把这种情况匹配进去

Answer 4

赞成落花惊魂。
楼主可否试一下：
改成
to_char(LEGWORK_ID) in (:inqstr)

Answer 5

To superhsj
你的提醒是对的，如果按照我先前的方法，的确会出错，数据不准确。To welyngj :
我试过了，用to_char(LEGWORK_ID) in (:inqstr)也不行。因为这样写也是只能处理单个数字。如果是多个数字则会报错1403，找不到记录。奇怪的是直接在sql中执行，都是正常的。

Answer 6

我看楼主得采用dynamic sql 了，不知道其他人有没有好方法？

Answer 7

用and instr(','||:inqstr||',', ','||TO_CHAR(LEGWORK_ID)||',')>0这个就可以

Answer 8

其实用superhsj 的方法是可以实现的，数据也没问题。不知道这样效率如何？但是用to_char(LEGWORK_ID) in (:inqstr)不能实现的原因，我估计是(:inqstr)中只是一个字符串('2,3')，而to_char(LEGWORK_ID)需要和多个字符串比较('2','3')吧。