Re: PL/SQL tables as Parameters in Package: Advice Please

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 17 Oct 1999 10:07:52 -0400
Message-ID: <8tcJOHjAqU14YbWWKh4wlifdGFBf@4ax.com>

A copy of this was sent to "Keith Jamieson" <jamiesonk_at_phoenix.ie> (if that email address didn't require changing) On Fri, 15 Oct 1999 16:37:40 +0100, you wrote:

>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.
>

are you asking how to declare the procedures and such to allow for a plsql table to be passed as an OUT parameter? If so:

tkyte_at_8i> create or replace package my_pkg   2 as
  3 type array is table of number index by binary_integer;   4
  5 procedure get_array( the_array OUT array );   6 end;
  7 /

Package created.

tkyte_at_8i> create or replace package body my_pkg   2 as
  3
  4 procedure get_array( the_array OUT array )   5 as
  6 begin

  7          the_array(1) := 1;
  8          the_array(2) := 2;

  9 end;
 10
 11 end;
 12 /

Package body created.

tkyte_at_8i> 
tkyte_at_8i> 
tkyte_at_8i> declare
  2          my_array        my_pkg.array;
  3  begin
  4          my_pkg.get_array( my_array );
  5          for i in 1 .. my_array.count loop
  6                  dbms_output.put_line( my_array(i) );
  7          end loop;

  8 end;
  9 /
1
2

PL/SQL procedure successfully completed.

The trick is to put the type definition in the SPEC and then use THAT EXACT type in all references to that procedure (as i did in the anonymous block -- i did not create yet another TYPE -- I used the type my_pkg.array).

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

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Oct 17 1999 - 09:07:52 CDT