display resultset from oracle 10g stored procedure

Question

I am using PL/SQL Developer and i have written a procedure to run a report and i need to procedure to output the resultset.

The procedure accepts input parameters and needs to output the resultset.

I cannot use a view because the procedure calls several APIs which accept the parameters i am passing into the procedure.

I understand from alot of searching that it's possible using ref_cursor but i cannot get ti to work.

A simplified version of the procedure is:

CREATE OR REPLACE PROCEDURE IFSINFO.SHORTAGE_SHEET (vSite     IN VARCHAR2,
                                                    vBuyer    IN VARCHAR2,
                                                    vSupplier IN VARCHAR2,
                                                    vCursor   OUT SYS_REFCURSOR)   
AS                                                    
BEGIN
    OPEN vCursor FOR                   
        SELECT blah blah blah blah blah blah;
END;

I have tried to execture the procedure and display the resultset using:

BEGIN
    vsite       := 'S03';
    vbuyer      := 'AW';
    vsupplier   := '%';    
    vcursor     refcursor;

    IFSINFO.SHORTAGE_SHEET(vsite => :vsite,
                           vbuyer => :vbuyer,
                           vsupplier => :vsupplier,
                           vcursor => :vcursor);                           
    print vcursor;                           
END;

And also:

variable rc refcursor; 
exec IFSINFO.SHORTAGE_SHEET('S03','AW','TQ1',:rc2); 
print rc2

But neither work. please can someone advise i am at my wits end.

Thank you Rob

Answer 1

Is there an error that you see when you execute this procedure or run it in SQLPLUS? Can you post your sqlplus session as is?

PRINT is a sqlplus specific command and it cannot be called inside the procedural block. If you need to print the results of a refcursor inside a procedure , then you'll need to fetch from it and print each record in the format that you need.

SQL> create or replace procedure test_REFCURSOR (
  2     i_number in number,
  3     o_cursor out sys_refcursor) 
  4  as
  5  begin
  6     open o_cursor for
  7        'select empno, ename from emp
  8             where rownum < ' || i_number ;
  9  end;
 10  /

Procedure created.

SQL> variable rc refcursor;
SQL> exec test_refcursor(5, :rc);

PL/SQL procedure successfully completed.

SQL> print rc;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES

You should also change your procedure (or) the procedure call to have different variable names.generallt, I prefix all input variables with "i_" and all output variables with "o_". This way, your procedure declaration would look like..

CREATE OR REPLACE PROCEDURE IFSINFO.SHORTAGE_SHEET (i_Site     IN VARCHAR2,
                                                    i_Buyer    IN VARCHAR2,
                                                    i_Supplier IN VARCHAR2,
                                                    o_Cursor   OUT SYS_REFCURSOR) AS ....

and the procedure call would be..

IFSINFO.SHORTAGE_SHEET(    i_site     => vsite,
                           i_buyer    => vbuyer,
                           i_supplier => vsupplier,
                           o_cursor   => vcursor);

You need not use the ":" in the beginning for these variables since they are not host environment variables ( this is the case for your second execution using SQLPLUS where you use the sqlplus variable "rc" inside the procedure call)