Question

创建一个存储过程，编译通过，但是java程序调用的时候 会报错！“无效数字”！为什么呀？急求答案！！谢谢

我的存储过程如下：
CREATE OR REPLACE PROCEDURE P_SALE_REPORT(V_NUM_SQL IN VARCHAR2,
V_BOOKID_SQL IN VARCHAR2) AS
BEGIN
INSERT INTO CRM.INFO_SALE_REPORT
(SALE_DATE,
SCHOOL_ID,
SCHOOL_NAME,
SALE_NO,
AREA_ID,
DESCRIPTION,
MARKET_USER,
USER_NAME,
TSR,
USER_NAME1,
SALE_NUM,
SALE_VALUE,
REAL_VALUE,
EXPENSES1,
EXPENSES2)
SELECT to_char(E.SALE_DATE, 'yyyy-MM-dd'),
E.SCHOOL_ID,
B.SCHOOL_NAME,
E.SALE_NO,
B.AREA_ID,
D.DESCRIPTION,
B.MARKET_USER,
(SELECT USER_NAME
FROM SYS_INFO_USER
WHERE USER_ID = B.MARKET_USER) AS USER_NAME,
B.TSR,
(SELECT USER_NAME FROM SYS_INFO_USER WHERE USER_ID = B.TSR) AS USER_NAME1,
NVL(E.SUM_NUM, 0),
NVL(E.SALE_TOTALPRICE, 0),
NVL(E.REAL_TOTALPRICE, 0),
NVL(E.EXPENSES1, 0),
NVL(E.EXPENSES2, 0)
FROM INFO_SCHOOLS B,
CODE_AREA D,
(SELECT A.SCHOOL_ID,
A.SALE_NO,
A.SALE_TYPE,
A.SALE_DATE,
SUM(A.SALE_PRICE * A.NUM) AS SALE_TOTALPRICE,
SUM(A.SALE_PRICE * A.NUM * A.DIS_AVG) AS REAL_TOTALPRICE,
SUM(NUM) AS SUM_NUM,
SUM(OTHER_EXPENSES) AS EXPENSES1,
SUM(REAL_SCORE) AS EXPENSES2
FROM (SELECT B.ID,
C.SCHOOL_ID,
B.SALE_DATE,
B.SALE_NO,
B.SALE_TYPE,
B.BOOK_NO,
B.BOOK_TITLE,
B.SALE_PRICE,
B.DIS_AVG,
C.NUM,
C.OTHER_EXPENSES,
(SELECT SUM(D.REAL_SCORE)
FROM INFO_TEACHER_INTEGRAL_DETAILS D,
INFO_TEACHER E
WHERE D.TEACHER_ID = E.TEACHER_ID
AND D.SALE_NO_ID = B.ID
AND E.SCHOOL_ID = C.SCHOOL_ID) AS REAL_SCORE
FROM INFO_SALE_FROM_LOG B, INFO_SALE_FROM_LOG_SCHOOLS C
WHERE B.ID = C.ID
AND B.ID IN V_NUM_SQL
AND B.BOOK_NO IN V_BOOKID_SQL
ORDER BY B.ID) A
GROUP BY A.SCHOOL_ID, A.SALE_NO, A.SALE_DATE, A.SALE_TYPE) E
WHERE B.SCHOOL_ID = E.SCHOOL_ID
AND B.AREA_ID = D.AREA_ID(+);
END P_SALE_REPORT;

java调用时cs.execute();，错误为：
java.sql.SQLException: ORA-01722: 无效数字
ORA-06512: 在 "CRM.P_SALE_REPORT", line 4
ORA-06512: 在 line 1

Answer 1

贴下JAVA调用的代码，是不传入的参数有问题？
还有种情况，你传入的参数插入表的时候报错了。
编译的时候不报错只说明你的过程没有语法上的错误。