有如下两个存储过程:
CREATE OR REPLACE Package Pack As
Type Ref_Cur Is Ref Cursor;
Procedure a(Param1 In Varchar2,
Os_Flag Out Varchar2,
Or_Cursor1 Out Ref_Cur,
Or_Cursor2 Out Ref_Cur);
End Pack;
/
CREATE OR REPLACE Package Body Pack As
Procedure a(Param1 In Varchar2,
Os_Flag Out Varchar2,
Or_Cursor1 Out Ref_Cur,
Or_Cursor2 Out Ref_Cur) As
Begin
Open Or_Cursor1 For
Select 1 From Dual;
Open Or_Cursor2 For
Select 2 From Dual;
Os_Flag := 0;
End a;
End Pack;
/
create or replace package pack_b
as
type ref_cur is ref cursor;
procedure b (param1 in varchar2,os_flag out varchar2,or_cursor1 out pack.Ref_Cur,or_cursor2 out pack.Ref_Cur);
end pack_b;
/
create or replace package body pack_b
as
procedure b (param1 in varchar2,os_flag out varchar2,or_cursor1 out pack.Ref_Cur,or_cursor2 out pack.Ref_Cur)
as
sqlstr varchar2(2000);
begin
sqlstr := 'begin pack.a(:1,:2,:3,:4); end;';
execute immediate sqlstr using in param1 ,out os_flag ,out or_cursor1,out or_cursor2;
end b;
end pack_b;
/
直接执行pack.a能正常执行(PL/SQL test方式)
PL/SQL TEST pack_b.b 報 ORA-01001 错误,求达人
CREATE OR REPLACE Package Pack As
Type Ref_Cur Is Ref Cursor;
Procedure a(Param1 In Varchar2,
Os_Flag Out Varchar2,
Or_Cursor1 Out Ref_Cur,
Or_Cursor2 Out Ref_Cur);
End Pack;
/
CREATE OR REPLACE Package Body Pack As
Procedure a(Param1 In Varchar2,
Os_Flag Out Varchar2,
Or_Cursor1 Out Ref_Cur,
Or_Cursor2 Out Ref_Cur) As
Begin
Open Or_Cursor1 For
Select 1 From Dual;
Open Or_Cursor2 For
Select 2 From Dual;
Os_Flag := 0;
End a;
End Pack;
/
create or replace package pack_b
as
type ref_cur is ref cursor;
procedure b (param1 in varchar2,os_flag out varchar2,or_cursor1 out pack.Ref_Cur,or_cursor2 out pack.Ref_Cur);
end pack_b;
/
create or replace package body pack_b
as
procedure b (param1 in varchar2,os_flag out varchar2,or_cursor1 out pack.Ref_Cur,or_cursor2 out pack.Ref_Cur)
as
sqlstr varchar2(2000);
begin
sqlstr := 'begin pack.a(:1,:2,:3,:4); end;';
execute immediate sqlstr using in param1 ,out os_flag ,out or_cursor1,out or_cursor2;
end b;
end pack_b;
/
直接执行pack.a能正常执行(PL/SQL test方式)
PL/SQL TEST pack_b.b 報 ORA-01001 错误,求达人
应该是上面语句中多了个'in'吧!!
execute immediate sqlstr using in param1 ,out os_flag ,out or_cursor1,out or_cursor2;
java里也不能调用游标啊 给java返回结果集
给别人看问题,要简要代码,例如参数实际20个,贴出来是可以写1到2个就可以,代表一下就可以了create or replace package pack_b
as
type ref_cur is ref cursor;
procedure b (param1 in varchar2,os_flag out varchar2,or_cursor1 out pack.Ref_Cur,or_cursor2 out pack.Ref_Cur);
end pack_b;
/
create or replace package body pack_b
as
procedure b (param1 in varchar2,os_flag out varchar2,or_cursor1 out pack.Ref_Cur,or_cursor2 out pack.Ref_Cur)
as
--sqlstr varchar2(2000);
begin
--sqlstr := 'begin pack.a(:1,:2,:3,:4); end;';
--execute immediate sqlstr using in param1 ,out os_flag ,out or_cursor1,out or_cursor2;
pack.a(param1,os_flag,or_cursor1,or_cursor2);---这样不更简单吗
end b;
end pack_b;
/
简单点,就是调用PACK.B的时候为什么会包01001,哪里需要改吗?
Cause: Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF.
Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.1.首先确保正确登陆
2.确保没有语法错误
3.调整系统允许的最大游标打开数值(MAXOPENCURSORS)
如果在PACK.A中就把游标关闭的话,那PACK_B.B传出来的游标就是没执行查询的游标了,取数据的时候还是会报错误的。我的想法就是把PACK.A中的游标数据,通用在PACK_B.B中用变量绑定的形式输出。还请各位大大看看还有哪里不对的吗~谢谢