display resultset from oracle 10g stored procedure

I am using PL/SQL Developer and i have written a procedure to run a report and i need to procedure 开发者_StackOverflowto 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

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)

To the bottom of this in pl/sql developer, the code is as follows.

Create a object to store the resultset

CREATE OR REPLACE TYPE ABC.TEST_TYPE
AS OBJECT
( 
  "Site"                       VARCHAR2(25),
);

Create a type as a table of he above object

CREATE OR REPLACE TYPE ABC.TEST_COL 
    AS TABLE OF ABC.TEST_TYPE

Create a package to excute the SQL

CREATE OR REPLACE PACKAGE ABC.TEST_RPT AS
    FUNCTION get_report(vPart     VARCHAR2,
                        vBuyer    VARCHAR2,
                        vSupplier VARCHAR2) RETURN ABC.TEST_COL 
    PIPELINED;
END;

Create the package body to excute the SQL

CREATE OR REPLACE PACKAGE BODY ABC.TEST_RPT AS
  FUNCTION get_report(vPart     VARCHAR2,
                      vBuyer    VARCHAR2,
                      vSupplier VARCHAR2) RETURN ABC.TEST_COL 
    PIPELINED IS
    CURSOR cTest(vPart     VARCHAR2,
                 vBuyer    VARCHAR2,
                 vSupplier VARCHAR2) IS

          SELECT Site
            FROM table
           WHERE Part = vPart
             AND Buyer = vBuyer
             AND Supplier = vSupplier;



  BEGIN
    FOR part_rec IN cTest(vSite, vBuyer, vSupplier) LOOP
      PIPE ROW(ABC.TEST_TYPE(part_rec.Site));

    END LOOP;
    RETURN;
    CLOSE cTest;
  END;
END;

The code to excute and output the resultset

SELECT * FROM TABLE(ABC.TEST_RPT.get_report('','',''))

I assume you're using Sql Plus to execute this. First, define a Sql Plus refcursor.

variable vcursor refcursor

Next, execute an anonymous pl/sql block. Notice the declare keyword.

DECLARE
          vsite       := 'S03';
          vbuyer      := 'AW';
          vsupplier   := '%';
          -- do not declare this, use sql plus bind var     vcursor     refcursor;
BEGIN

       IFSINFO.SHORTAGE_SHEET(vsite => vsite, -- no colon
              vbuyer =>    vbuyer,
              vsupplier => vsupplier,
              vcursor   => :vcursor); 
END;
/

Then run this sql plus command.

print vcursor

print is not pl/sql

Your ref cursor variable is rc. But then you use rc2.

Change rc2 to rc and it should work