Get resultset from oracle stored procedure
In SQL Plus:
SQL> create procedure myproc (prc out sys_refcursor) 2 is 3 begin 4 open prc for select * from emp; 5 end; 6 /Procedure created.SQL> var rc refcursorSQL> execute myproc(:rc)PL/SQL procedure successfully completed.SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-NOV-1981 4999 10 7698 BLAKE MANAGER 7839 01-MAY-1981 2849 30 7782 CLARKE MANAGER 7839 09-JUN-1981 2449 10 7566 JONES MANAGER 7839 02-APR-1981 2974 20 7788 SCOTT ANALYST 7566 09-DEC-1982 2999 20 7902 FORD ANALYST 7566 03-DEC-1981 2999 20 7369 SMITHY CLERK 7902 17-DEC-1980 9988 11 20 7499 ALLEN SALESMAN 7698 20-FEB-1981 1599 3009 30 7521 WARDS SALESMAN 7698 22-FEB-1981 1249 551 30 7654 MARTIN SALESMAN 7698 28-SEP-1981 1249 1400 30 7844 TURNER SALESMAN 7698 08-SEP-1981 1499 0 30 7876 ADAMS CLERK 7788 12-JAN-1983 1099 20 7900 JAMES CLERK 7698 03-DEC-1981 949 30 7934 MILLER CLERK 7782 23-JAN-1982 1299 10 6668 Umberto CLERK 7566 11-JUN-2009 19999 0 10 9567 ALLBRIGHT ANALYST 7788 02-JUN-2009 76999 24 10
Oracle is not sql server. Try the following in SQL Developer
variable rc refcursor;exec testproc(:rc2);print rc2
My solution was to create a pipelined function. The advantages are that the query can be a single line:
select * from table(yourfunction(param1, param2));
- You can join your results to other tables or filter or sort them as you please..
- the results appear as regular query results so you can easily manipulate them.
To define the function you would need to do something like the following:
-- Declare the record columns TYPE your_record IS RECORD( my_col1 VARCHAR2(50), my_col2 varchar2(4000) ); TYPE your_results IS TABLE OF your_record; -- Declare the function function yourfunction(a_Param1 varchar2, a_Param2 varchar2) return your_results pipelined is rt your_results; begin -- Your query to load the table type select s.col1,s.col2 bulk collect into rt from your_table s where lower(s.col1) like lower('%'||a_Param1||'%'); -- Stuff the results into the pipeline.. if rt.count > 0 then for i in rt.FIRST .. rt.LAST loop pipe row (rt(i)); end loop; end if; -- Add more results as you please.... return; end find;
And as mentioned above, all you would do to view your results is:
select * from table(yourfunction(param1, param2)) t order by t.my_col1;