我创建了一段存储过程,如下
CREATE OR REPLACE PROCEDURE obtainPartnumberProc (
tablename IN VARCHAR2,
obid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT PARTNUMBER FROM PDMM31A.'''+tablename+''' WHERE OBID=''' || obid || ''''; OPEN rst FOR v_sqlstring;
END obtainPartnumberProc;其中sql语句中的表名是动态的在java中调用存储过程代码如下:
CallableStatement cstmt2 = null;
callSql = "{ call obtainPartnumberProc( ?,?,? )}";
cstmt2 = con.prepareCall(callSql);
cstmt2.setString(1, tablename )
cstmt2.setString(2, obid )
cstmt2.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
cstmt2.execute();
ResultSet rss = (ResultSet) cstmt2.getObject(2);
……
可是编译时发生如下错误:
java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在"SYS.OBTAINPARTNUMBERPROC", line 9
ORA-06512: 在line 1
……
CREATE OR REPLACE PROCEDURE obtainPartnumberProc (
tablename IN VARCHAR2,
obid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT PARTNUMBER FROM PDMM31A.'''+tablename+''' WHERE OBID=''' || obid || ''''; OPEN rst FOR v_sqlstring;
END obtainPartnumberProc;其中sql语句中的表名是动态的在java中调用存储过程代码如下:
CallableStatement cstmt2 = null;
callSql = "{ call obtainPartnumberProc( ?,?,? )}";
cstmt2 = con.prepareCall(callSql);
cstmt2.setString(1, tablename )
cstmt2.setString(2, obid )
cstmt2.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
cstmt2.execute();
ResultSet rss = (ResultSet) cstmt2.getObject(2);
……
可是编译时发生如下错误:
java.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在"SYS.OBTAINPARTNUMBERPROC", line 9
ORA-06512: 在line 1
……
v_sqlstring := 'SELECT PARTNUMBER FROM PDMM31A.'||tablename||' WHERE OBID=''' || obid || '''';
'SELECT PARTNUMBER FROM PDMM31A.'''+tablename+''' WHERE OBID=''' || obid || ''''; 你这段怎么回事,用+作字符串连接吗?
是java的用法吧?
select PARTNUMBER from PDMM31A."+ tablename + " where OBID='"+obid+"' ;
执行一点问题没有,可是向我上面那样写成存储过程编译时就报错:
ava.sql.SQLException: ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在"SYS.OBTAINPARTNUMBERPROC", line 9
ORA-06512: 在line 1请问我应该怎么改呢??
'SELECT PARTNUMBER FROM PDMM31A.'''+tablename+''' WHERE OBID='|| obid ;
'SELECT PARTNUMBER FROM PDMM31A.'''||tablename||''' WHERE OBID='|| obid ;