How to display REF_CURSOR out Parameter from a procedure -Oracle12C

Question

Am i doing anything wrong here , i am trying to display the SYS_REFCURSOR out values in DBMS_OUTPUT. here is my code so far, its giving invalid cursor exception . can someone help me on this .

DECLARE
out_dcursor SYS_REFCURSOR;
snumber                     VARCHAR2(5);
   inumber                 VARCHAR2(15);

BEGIN

 my_proc(out_dcursor, 'INPUT_VALUE', NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,'INPUT_VALUE2', NULL, NULL);
 
 loop
 fetch out_dcursor into snumber
           ,inumber;
 exit when out_dcursor%NOTFOUND;
 end loop;
 
END;

Does this answer your question? [PL/SQL print out ref cursor returned by a stored procedure](https://stackoverflow.com/questions/5822241/pl-sql-print-out-ref-cursor-returned-by-a-stored-procedure) — astentx, Jul 22 '23 at 09:40
"Invalid cursor" errors don't seem to be relevant if your question is about how to print the results of a cursor. Test it with a valid cursor. — William Robertson, Jul 22 '23 at 23:04

score 1 · Answer 1 · answered Jul 21 '23 at 20:11

1

You're complaining about dbms_output and invalid cursor, but - there's no first term in code you posted, and you didn't say which part returns the second one.

Here's example which shows something that works. See if you can use it with your code, as we don't know what my_proc is, accepts, does nor returns.

SQL> create or replace procedure my_proc (par_rc out sys_refcursor) is
  2  begin
  3    open par_rc for select deptno, dname from dept;
  4  end;
  5  /

Procedure created.

SQL> set serveroutput on;
SQL> declare
  2    rc       sys_refcursor;
  3    l_deptno dept.deptno%type;
  4    l_dname  dept.dname%type;
  5  begin
  6    my_proc(rc);
  7    loop
  8      fetch rc into l_deptno, l_dname;
  9      exit when rc%notfound;
 10      dbms_output.put_Line(l_deptno ||' '|| l_dname);
 11    end loop;
 12  end;
 13  /
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

PL/SQL procedure successfully completed.

SQL>

answered Jul 21 '23 at 20:11

Littlefoot

131,892
15
35
57

Thanks for the response . Before coming to DBMS part , i am getting error while fetching refcursor variable to the local variables . its says its 'Invalid cursor '.Do we need to open that cursor ?. The procedure returns multiple values through OUT variable using SYS_REFCURSOR datatype. Your code is same as my code . – user1402648 Jul 21 '23 at 20:16
1

You're welcome. My example shows how to do that. You don't have to *open* any cursor (as my code demonstrates). – Littlefoot Jul 21 '23 at 20:29
Thanks much for the example , but i am thinking what could be the reason for this error in my case . if i remove the loop part completely the block works . only problem arises while fetching into local variables . – user1402648 Jul 21 '23 at 20:34
Can some one conform if the OUT SYS_REFCURSOR not returns any values will lead to 'INVALID CURSOR' error ? – user1402648 Jul 21 '23 at 21:14
@user1402648 no, a cursor fetch not returning a row doesn't make it invalid and isn't an exception condition. – William Robertson Jul 22 '23 at 14:35

How to display REF_CURSOR out Parameter from a procedure -Oracle12C

1 Answers1