Re: pl/sql table and commit

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 16 Nov 2005 06:51:54 -0800
Message-ID: <1132152715.615483@yasure>

Morff wrote:
> Dnia 16-11-2005 o 12:57:25 buko <ivan.bukovcan_at_gmail.com> napisał:
>

>> No you do not need commit when filling pl/sql table variables. If all
>> data are processed in one session, variable content is visible and
>> accesible instantly.
>>
>> I would also suggest using a BULK COLLECT instead of cursor LOOP, it's
>> better technique and also performs faster - example:
>>
>> OPEN c1;
>> LOOP
>> FETCH c1 BULK COLLECT INTO v_DateTable [LIMIT rows];
>>  EXIT WHEN c1%NOTFOUND;
>> END LOOP;
>> CLOSE c1;
>>
>> Ivan
>>

>
> So .. why code like that is not working ? :
>
> in package body :
>
> type myrow_tp is record (field1,field2 ...)
> type mytab_tp is table of myrow_tp index binary_integer
>
>
> function fillmytable (var1,var2 ..) return mytab_tp
> is
> mytab_tmp mutabtp;
>
> cursor rowtmp is
> select ..... from ....
> where field1=var1 and
> ...
> begin
>
> open rowtmp;
> LOOP
> FETCH rowtmp BULK COLLECT INTO mytab_tmp; <-- error here
> EXIT WHEN rowtmp%NOTFOUND;
> END LOOP;
> CLOSE rowtmp;
> RETURN mytab_tmp;
> end;
>
> An error message is :
> expression mytab_tmp in the INTO list is of wrong type

The problem is actually here:

 > type mytab_tp is table of myrow_tp index binary_integer

Take a look at the examples in Morgan's Library at www.psoug.org under Bulk Collection. I both declare my own data types and use the DBMS_SQL built-ins.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Wed Nov 16 2005 - 08:51:54 CST