Get resultset from oracle stored procedure

Question

I m working on converting a stored procedure from SQL server  to Oracle   This stored procedure provides a direct resultset  I mean that if you call the stored procedure in eg Management Studio you directly obtain the resultset    By converting to Oracle I walk against the problem that I in Oracle will not display the resultset  I searched on the Internet and have seen that the stored procedure should yield a REF CURSOR  but I still walk with the problem to write a little piece of code to obtain the resultset en process that   Pseudo Code    Call stored procedure and obtain cursor Do something with that cursor so that my resultset appears  Someone an idea

User · Answer

FYI as of Oracle 12c  you can do this  CREATE OR REPLACE PROCEDURE testproc n number  AS   cur SYS REFCURSOR  BEGIN     OPEN cur FOR SELECT object id object name from all objects where rownum  lt  n      DBMS SQL RETURN RESULT cur   END     EXEC testproc 3    OBJECT ID OBJECT NAME                                                                                                                      ---------- ------------ 100 ORA BASE                                                                                                                         116 DUAL                                                                                                                              This was supposed to get closer to other databases  and ease migrations  But it s not perfect to me  for instance SQL developer won t display it nicely as a normal SELECT  I prefer the output of pipeline functions  but they need more boilerplate to code  more info  https   oracle-base com articles 12c implicit-statement-results-12cr1

User · Answer

Hi I know this was asked a while ago but I ve just figured this out and it might help someone else  Not sure if this is exactly what you re looking for but this is how I call a stored proc and view the output using SQL Developer  In SQL Developer when viewing the proc  right click and choose  Run  or select Ctrl F11 to bring up the Run PL SQL window  This creates a template with the input and output params which you need to modify  My proc returns a sys refcursor  The tricky part for me was declaring a row type that is exactly equivalent to the select stmt   sys refcursor being returned by the proc   DECLARE   P CAE SEC ID N NUMBER    P FM SEC CODE C VARCHAR2 200     P PAGE INDEX NUMBER    P PAGE SIZE NUMBER    v Return sys refcursor    type t row is record  CAE SEC ID NUMBER FM SEC CODE VARCHAR2 7  rownum number  v total count number     v rec t row   BEGIN   P CAE SEC ID N    NULL    P FM SEC CODE C    NULL    P PAGE INDEX    0    P PAGE SIZE    25     CAE FOF SECURITY PKG GET LIST FOF SECURITY      P CAE SEC ID N   gt  P CAE SEC ID N      P FM SEC CODE C   gt  P FM SEC CODE C      P PAGE INDEX   gt  P PAGE INDEX      P PAGE SIZE   gt  P PAGE SIZE      P FOF SEC REFCUR   gt  v Return        -- Modify the code to output the variable   -- DBMS OUTPUT PUT LINE  P FOF SEC REFCUR         loop     fetch v Return into v rec      exit when v Return notfound      DBMS OUTPUT PUT LINE  sec id        v rec CAE SEC ID     sec code       v rec FM SEC CODE     end loop   END

User · Answer

CREATE OR REPLACE PROCEDURE SP Invoices p nameClient IN CHAR  AS BEGIN            FOR c invoice IN               SELECT CodeInvoice  NameClient FROM Invoice        WHERE NameClient   p nameClient           LOOP         dbms output put line  Code Invoice       c invoice CodeInvoice           dbms output put line  Name Client         c invoice NameClient        END LOOP  END   Executing in SQL Developer  BEGIN     SP Invoices  Perico de los palotes    END  -- Or  EXEC SP Invoices  Perico de los palotes     Output   gt  Code Invoice  1   gt  Name Client   Perico de los palotes  gt  Code Invoice  2   gt  Name Client   Perico de los palotes

User · Answer

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  gt  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

User · Answer

In SQL Plus   SQL gt  create procedure myproc  prc out sys refcursor    2  is   3  begin   4     open prc for select   from emp    5  end    6     Procedure created   SQL gt  var rc refcursor SQL gt  execute myproc  rc   PL SQL procedure successfully completed   SQL gt  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

User · Answer

In SQL Plus   SQL gt  var r refcursor SQL gt  set autoprint on SQL gt  exec  r    function returning refcursor      Replace the last line with a call to your procedure   function and the contents of the refcursor will be displayed

User · Answer

Oracle is not sql server  Try the following in SQL Developer  variable rc refcursor  exec testproc  rc2   print rc2

[oracle] Get resultset from oracle stored procedure

Examples related to oracle

Examples related to stored-procedures

Examples related to plsql