//创建包,在包中创建refcursor
CREATE OR REPLACE PACKAGE "TYPES"
AS
TYPE refcursor IS REF CURSOR;
END; //创建存储过程
CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
|| leftobid; OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;
//在java中我是这样调用的
CallableStatement cstmt = null ;
ResultSet rs = null ;
try{
string callSql = "{ call obtainpartnumberproc( ?,? )}";
cstmt = con.prepareCall(callSql);
//leftobid为一字符串变量
cstmt.setString( 1,leftobid);
cstmt.registerOutParameter( 2,oracle.jdbc.OracleTypes.CURSOR ) ;
cstmt.execute();
rs = ( ResultSet )cstmt.getObject( 2 );
while (rs.next()) {
……
}
}
执行时报下列错误:
java.sql.SQLException: ORA-00904: 无效列名
ORA-06512: 在"SYS.OBTAINPARTNUMBERPROC", line 12
ORA-06512: 在line 1 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
v_sqlstring :='SELECT * FROM PDMM31A.ASSMSTRC WHERE LEFT='|| leftobid;
CREATE OR REPLACE PACKAGE TYPES
AS
TYPE refcursor IS REF CURSOR; PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
) ;
END;
/
create or replace package body TYPES
//创建存储过程
PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
|| leftobid; OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;
end;
用这种方式写,直接调用包中的存储规程
TYPES.obtainpartnumberproc CREATE OR REPLACE PACKAGE TYPES
AS
TYPE refcursor IS REF CURSOR; PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
) ;
END;
/
create or replace package body TYPESPROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
|| leftobid; OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;
end;
像这样创建包报错啊:
错误:(S1979) Expecting: ; AS AUTHID IS
LEFT是PDMM31A.ASSMSTRC表中的一个列
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE "LEFT"=' || leftobid;
还是不行啊,还报一样的错误:
ORA-00904: 无效列名
ORA-06512 : 在"SYS.OBTAINPARTNUMBERPROC", line 11
ORA-06512 : 在line 9
1、CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
2、 leftobid IN VARCHAR2,
3、 rst IN OUT TYPES.refcursor
4、)
5、IS
6、 v_sqlstring VARCHAR2 (1000);
7、BEGIN
8、 v_sqlstring :='SELECT CLASS2,"RIGHT",W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE "LEFT"=' 9、|| leftobid; 10、 OPEN rst FOR v_sqlstring;
11、END obtainpartnumberproc;创建成功!
编译时时报
ORA-00904: 无效列名
ORA-06512 : 在"SYS.OBTAINPARTNUMBERPROC", line 11
ORA-06512 : 在line 9
'SELECT CLASS2,"RIGHT",W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE "LEFT"=' || leftobid;
is
TYPE refcursor IS REF CURSOR; PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
);
END;
/
create or replace package body TYPES isPROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
|| leftobid; OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;
end;
按你的写法,包创建成功,可是存储过程创建时报错:
Warning: compiled but with compilation errors
CREATE OR REPLACE PACKAGE TYPES
is
TYPE refcursor IS REF CURSOR; PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
);
END;
/
create or replace package body TYPES isPROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='
|| leftobid; OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;
end;
这么写包和存储过程都可以正常创建了,可是编译是还是报相同的错误:
ORA-00904: 无效列名
ORA-06512 : 在"SYS.TYPES", line 15
ORA-06512 : 在line 914楼说的我也照做了,把RIGHT和LEFT 都加了双引号但还包相同的错误,即使我把SQL语句中的RIGHT和LEFT都换成表中别的字段也不行
在sqlplus中直接运行可以吗?
改成:
refcursor
SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT=''
这个语句是否可以执行!!
SQL语句在sqlplus中执行正常,如下:
SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 1月 13 13:37:48 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.连接到:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - ProductionSQL> select /*+RULE*/ CLASS2,RIGHT,W2ISBORROWED from PDMM31A.ASSMSTRC where LEFT='sieczKcpdmtstpdmm3
1a-bJ1';CLASS2
----------------------------------------
RIGHT W2
------------------------------------------------ --
W2BStMtr
siejgKhpdmtstpdmm31a-bLy -
…………
|| leftobid || '''';
AS
TYPE refcursor IS REF CURSOR;
END;CREATE OR REPLACE PROCEDURE obtainpartnumberproc (
leftobid IN VARCHAR2,
rst IN OUT TYPES.refcursor
)
IS
v_sqlstring VARCHAR2 (1000);
BEGIN
v_sqlstring :=
'SELECT CLASS2,RIGHT,W2ISBORROWED FROM PDMM31A.ASSMSTRC WHERE LEFT='''
|| leftobid || '''';
OPEN rst FOR v_sqlstring;
END obtainpartnumberproc;谢谢参与本贴的所有朋友们,鉴于分数有限,就把分给ygjdatou 和 linzi两为朋友吧!再次谢谢以上所有楼层的所有朋友,谢谢你们!!