display resultset from oracle 10g stored procedure
Solution 1
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('','',''))
Solution 2
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)
RobLaw84
Updated on June 04, 2022Comments
-
RobLaw84 almost 2 years
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