Srinivas,
First, if you're on 9i use sys_refcursor.
Second, if you fetch in the proc, you
Can't display it in sqlplus, so, you
Have to pick where you want the result set displayed.
Consider:
SQL> create or replace procedure proc (p_rc out sys_refcursor)
2 is
3 type array is table of emp.ename%type;
4 names array := array();
5 begin
6 open p_rc for select ename from emp;
7 end;
8 /
Procedure created.
SQL> variable x refcursor
SQL> exec proc(:x)
PL/SQL procedure successfully completed.
SQL> print x
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
So, there I just return the result set.
it's sqlplus that does the fetch via the "print" command.
So, if we fetch it in the proc:
SQL> create or replace procedure proc (p_rc out sys_refcursor)
2 is
3 type array is table of emp.ename%type;
4 names array := array();
5 begin
6 open p_rc for select ename from emp;
7 fetch p_rc bulk collect into names;
8 dbms_output.put_line(names.count());
9 end;
10 /
Procedure created.
SQL> exec proc(:x)
14
PL/SQL procedure successfully completed.
/* that works, but if I try to print or fetch again immediately after */
[Email address protected] print x
ERROR:
ORA-01002: fetch out of sequence
no rows selected
it fails. So, use ref cursor, then decide whether or not to fetch in
sqlplus
and it will work fine.
Good luck,
Anthony
-----Original Message-----
From: Srinivas Shesham
[mailto:oracledba-ezmlmshield-x75560717.[Email address protected]
Sent: Thursday, April 28, 2005 7:44 PM
To: LazyDBA Discussion
Subject: PL/SQL question
Hi friends,
I am trying to do the following:
TYPE refcur IS REF CURSOR;
PROCEDURE myFunction(resultset OUT refcur)
IS
TYPE ids IS TABLE mytable.ids%TYPE;
Job_ids ids;
BEGIN
OPEN myFunction.resultset FOR 'select ids from mytable';
FETCH myFunction.resultset BULK COLLECT INTO Job_ids; <ERROR OCCURS
HERE>
END;
I obtain the following error when I run the procedure:
Error -6504: ORA-06504: PL/SQL: Return types of Result Set variables or
query do not match
ORA-06504: PL/SQL: Return types of Result Set variables or query do not
match
ORA-06512: at line 23
Program terminated with errors
Can anybody help with this. Using Oracle 9i
Regards,
Srinivas Shesham
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page