Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL tables as Parameters in Package: Advice Please

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

From: Keith Jamieson <jamiesonk_at_phoenix.ie>
Date: Tue, 19 Oct 1999 17:02:47 +0100
Message-ID: <7ui4oi$f70$1@ezekiel.eunet.ie>


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;

Thomas Kyte wrote in message <8tcJOHjAqU14YbWWKh4wlifdGFBf_at_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 Tue Oct 19 1999 - 11:02:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US