|
|
|
|
|
|
| Re: create stored procedure with cursor as an out argument [message #651201 is a reply to message #651192] |
Thu, 12 May 2016 09:58   |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Formatted:
declare
ref_cur sys_refcursor;
v_tab pmanrl%rowtype;
begin
dbms_output.put_line('test1');
p_test(ref_cur);
loop
fetch ref_cur into v_tab;
exit when ref_cur%notfound;
dbms_output.put_line('testttt' );
end loop;
close ref_cur;
dbms_output.put_line('test2');
end;
/
Should be posted with the SQL*Plus session like:
SQL> declare
2 ref_cur sys_refcursor;
3 v_tab pmanrl%rowtype;
4 begin
5 dbms_output.put_line('test1');
6 p_test(ref_cur);
7 loop
8 fetch ref_cur into v_tab;
9 exit when ref_cur%notfound;
10 dbms_output.put_line('testttt' );
11 end loop;
12 close ref_cur;
13 dbms_output.put_line('test2');
14 end;
15 /
v_tab pmanrl%rowtype;
*
ERROR at line 3:
ORA-06550: line 3, column 7:
PLS-00201: identifier 'PMANRL' must be declared
ORA-06550: line 3, column 7:
PL/SQL: Item ignored
ORA-06550: line 6, column 2:
PLS-00201: identifier 'P_TEST' must be declared
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored
ORA-06550: line 8, column 25:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored
Of course, I have not the same error than you because I have not your objects, so with any SQL or PL/SQL question post everything that is necessary for us to reproduce your case. Here the CREATE TABLE statement for "pmanrl" table and CREATE PROCEDURE for "p_test" procedure.
We can't obviously know where the error comes from without the code.
Are you able to debug code you don't see?
What we can say is that "p_test" does not set "ref_cur" or set it with an invalid value.
[Updated on: Thu, 12 May 2016 14:43] Report message to a moderator |
|
|
|
| Re: create stored procedure with cursor as an out argument [message #651209 is a reply to message #651192] |
Thu, 12 May 2016 14:40 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
-- table:
SCOTT@orcl_12.1.0.2.0> create table pmanrl as select * from dept
2 /
Table created.
-- stored procedure with cursor as an out argument:
SCOTT@orcl_12.1.0.2.0> create or replace procedure p_test
2 (p_ref_cur out sys_refcursor)
3 as
4 begin
5 open p_ref_cur for select * from pmanrl;
6 end p_test;
7 /
Procedure created.
SCOTT@orcl_12.1.0.2.0> show errors
No errors.
-- anonymous pl/sql block that calls stored procedure above and tests it:
SCOTT@orcl_12.1.0.2.0> declare
2 ref_cur sys_refcursor;
3 v_tab pmanrl%rowtype;
4 begin
5 dbms_output.put_line ('test1');
6 p_test (ref_cur);
7 loop
8 fetch ref_cur into v_tab;
9 exit when ref_cur%notfound;
10 dbms_output.put_line ('testttt');
11 end loop;
12 close ref_cur;
13 dbms_output.put_line ('test2');
14 end;
15 /
test1
testttt
testttt
testttt
testttt
test2
PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
