Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> PL/SQL tables as Parameters in Package: Advice Please
I am currently writing a package which has calls to overloaded =
procedures within the package body.
The procedures are populating PL/SQL tables. These are currently defined = in the procedures and cursor parameters are being used.
What I want to do is to be able to call the procedure(or function), with = the ability to retrieve the PLSQL table.
A sample specification and body are included below.
CREATE OR REPLACE
PACKAGE idt_cursors
IS
PROCEDURE workqueue_cur (p_memberid_in IN NUMBER);
PROCEDURE workqueue_cur (p_workqueueid_in IN NUMBER);
END idt_cursors;
CREATE OR REPLACE
PACKAGE BODY idt_cursors
IS
PROCEDURE workqueue_cur(p_memberid_in IN NUMBER) IS
CURSOR workqueue_cur (p_memberid_in IN NUMBER) IS
SELECT DISTINCT iq.workqueueid as workqueueid, iq.memberid, pq.name, vw.name as vwqueuename, vw.vwqueueid, pq.type FROM indexingworkqueue iq, workqueue pq, vwqueue vw WHERE vw.vwqueueid = pq.vwqueueid AND pq.workqueueid = iq.workqueueid AND iq.owned = 'Y' AND iq.memberid = p_memberid_in ORDER BY pq.name;
workqueue_rec workqueue_cur%ROWTYPE;
TYPE workqueue_tabletype IS TABLE OF workqueue_cur%ROWTYPE
INDEX BY BINARY_INTEGER;
total_rows PLS_INTEGER;
workqueue_table workqueue_tabletype;
rowno BINARY_INTEGER := 0;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR workqueue_rec IN workqueue_cur(p_memberid_in)
LOOP
rowno := rowno+1; workqueue_table(rowno) := workqueue_rec; total_rows := workqueue_table.COUNT; DBMS_OUTPUT.PUT_LINE('No of Rows in PLSQL table is ' || = total_rows); DBMS_OUTPUT.PUT_LINE('Name is ' || workqueue_rec.name);END LOOP;
PROCEDURE workqueue_cur(p_workqueueid_in IN NUMBER) IS
CURSOR workqueue_cur (p_workqueueid_in IN NUMBER) IS
SELECT DISTINCT iq.workqueueid as workqueueid, iq.memberid, pq.name, vw.name as vwqueuename, vw.vwqueueid, pq.type FROM indexingworkqueue iq, workqueue pq, vwqueue vw WHERE vw.vwqueueid = pq.vwqueueid AND pq.workqueueid = iq.workqueueid AND iq.owned = 'Y' AND pq.workqueueid = p_workqueueid_in ORDER BY pq.name;
workqueue_rec workqueue_cur%ROWTYPE;
TYPE workqueue_tabletype IS TABLE OF workqueue_cur%ROWTYPE
INDEX BY BINARY_INTEGER;
total_rows PLS_INTEGER;
workqueue_table workqueue_tabletype;
rowno BINARY_INTEGER := 0;
BEGIN
DBMS_OUTPUT.ENABLE;
FOR workqueue_rec IN workqueue_cur(p_workqueueid_in)
LOOP
rowno := rowno + 1; workqueue_table(rowno) := workqueue_rec; total_rows := workqueue_table.COUNT; DBMS_OUTPUT.PUT_LINE('No of Rows in PLSQL table is ' || = total_rows); DBMS_OUTPUT.PUT_LINE('Name is ' || workqueue_rec.name);END LOOP;
END idt_cursors; Received on Fri Oct 15 1999 - 10:37:40 CDT