PL/SQL tables as Parameters in Package: Advice Please 
Author Message
 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;
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;



Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL tables as Parameters in Package: Advice Please

The actual problem that occurred as a result of moving the cursor from the package body to the specification was that it had no return clause. Cursors in package specifications must always have return clauses.

The solution was to declare a programmer defined record before the cursor and then tell the cursor to return this record.

A PLSQL tableType was then declared as a table of the record.

The actual working code is shown below.

CREATE OR REPLACE
PACKAGE test_idt_cursors
IS
   type indexingarea_rec is RECORD(memberid member.memberid%TYPE,
                                   name     member.name%TYPE,
                                   defaultworkclassid indexingarea.defaultworkclassid%TYPE,
                                   deferralworkqueueid indexingarea.deferralworkqueueid%TYPE,
                                   capturerepository indexingarea.capturerepository%TYPE);
   CURSOR indexingarea_cur(p_memberid_in NUMBER)
    RETURN indexingarea_rec IS
        SELECT
      DISTINCT ia.memberid,
               m.name as name,
               ia.defaultworkclassid,
               ia.deferralworkqueueid,
               ia.capturerepository
          FROM indexingarea ia,
               member m
         WHERE m.memberid  = ia.memberid
              AND m.memberid = p_memberid_in;

 TYPE indexingarea_tabletype IS TABLE OF indexingarea_rec INDEX BY BINARY_INTEGER;
PROCEDURE indexingarea_p(p_memberid_in IN NUMBER,
                       p_table_out OUT indexingarea_tabletype);
END test_idt_cursors;
/

CREATE OR REPLACE
PACKAGE BODY test_idt_cursors
IS
PROCEDURE indexingarea_p(p_memberid_in NUMBER,
                       p_table_out OUT indexingarea_tabletype )

IS

   total_rows PLS_INTEGER;
   rowno BINARY_INTEGER := 0;
BEGIN
    DBMS_OUTPUT.ENABLE;
    FOR indexingarea_rec IN indexingarea_cur(p_memberid_in)
    LOOP
        rowno := rowno+1;
        p_table_out(rowno) := indexingarea_rec;
        total_rows := p_table_out.COUNT;
        DBMS_OUTPUT.PUT_LINE('No of Rows in PLSQL table is ' || total_rows);
    END LOOP;
END indexingarea_p;      

END test_idt_cursors;

And here is a little block to verify that the table gets populated.

declare
 p_out test_idt_cursors.indexingarea_tabletype;
 begin test_idt_cursors.indexingarea_p(p_memberid_in=>61,
                                       p_table_out =>p_out);
 DBMS_OUTPUT.PUT_LINE('No of rows is ' || P_out.COUNT);
 end;

Quote:


>(if that email address didn't require changing)

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


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


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




>  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


>Oracle Service Industries     Reston, VA   USA

>Opinions are mine and do not necessarily reflect those of Oracle Corporation



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. PL/SQL - IN OUT PARAMETER problem: ORA-06502: PL/SQL: Numeric or value error

2. Help Needed Passing a VB Array to a PL/SQL Table Variable in Package

3. PL/SQL package to create tables and/or triggers

4. PL/SQL Package for generating HTML Tables

5. Compilation of a package with pl/sql table type

6. Return PL/SQL Table as output parameter in Oracle Stored Procedure

7. PL/SQL constants in packages/stored packages

8. How to call a ORACLE stored procedure which parameters are PL/SQL table and %ROWTYPE

9. Passing table names as parameter to a PL/SQL program


 
Powered by phpBB® Forum Software