PL/SQL tables as Parameters in Package: Advice Please

From: Keith Jamieson <jamiesonk_at_phoenix.ie>
Date: Fri, 15 Oct 1999 16:37:40 +0100
Message-ID: <7u7hpd$5ci$1@ezekiel.eunet.ie>

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;
END workqueue_cur;  

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 workqueue_cur;

END idt_cursors; Received on Fri Oct 15 1999 - 10:37:40 CDT