I want to create a procedure that uses cursor as an input in Oracle. I tried something like this:
cursor cur is select * from table t1;
and then also:
create or replace procedure (I want to input cursor cur here)
is
begin
end;/
How can I do that?
Here's an example which shows one way to do it.
Procedure that accepts cursor as a parameter, loops through it and displays it contents:
SQL> create or replace procedure p_test (par_cursor in sys_refcursor)
2 is
3 l_ename emp.ename%type;
4 l_sal emp.sal%type;
5 begin
6 loop
7 fetch par_cursor into l_ename, l_sal;
8 exit when par_cursor%notfound;
9 dbms_output.put_line(l_ename ||' - '|| l_sal);
10 end loop;
11 end;
12 /
Procedure created.
How to use it?
SQL> set serveroutput on
SQL> declare
2 l_rc sys_refcursor;
3 begin
4 open l_rc for
5 select ename, sal
6 from emp
7 where deptno = 10;
8 p_test (l_rc);
9 end;
10 /
CLARK - 2450
KING - 5000
MILLER - 1300
PL/SQL procedure successfully completed.
SQL>
REF CURSOR. Use thesys_refcursortype for an easily available weak ref cursor, open it for the query desired, and pass it to the procedure which should be expecting an argument of typesys_refcursor. It can then fetch from the cursor, but it is it's responsibility to ensure it fetches into the correctly aligned row structure. It aids immensely to standardize a column list for cursors being passed to a particular proc. Do be aware that using ref cursors is an intermediate technique, not something that should be used unnecessarily.sys_refcursorto the procedure. You can't connect to the remote database, open a cursor there, and then pass that to the local procedure if that is what you are thinking of. You'd have to address the underlying problem that you want data from a remote database but the local DBA does not want connections to that remote database.