Re: cursor result return

On 21 Feb 2005 13:08:18 -0800, zibinyang_at_gmail.com wrote:

>Hi all,
>when I try to select * from test_table it takes 9 seconds. (4000
>records)
>but when I try to run this in PL/SQL
>
>declare
>cursor c_bill is
> select * from test_table
> begin
> for l_c_bill in c_bill loop
> dbms_output.put_line (l_c_bill.rownum) ;
> end loop;
> end;
>it take more then 588 seconds, it's anyway to reduce this time in
>pl/sql?
>
>Thanks

A version number would help.

The difference is probably being caused by the fact you are implicitly using array set in sql*plus, and in your pl/sql code you don't use array fetch.
This means you get one round trip per array in sql*plus and you get one round trip per record in pl/sql.
In order to demonstrate the impact of array fetch issue set array 1 in your sql*plus code and time, and preferably also at the same time enable trace.

The only way to address in pl/sql (prior to 10G), is you implement a bulk collect in your pl/sql code. You should declare an appropiate pl/sql table, open an explicit cursor and issue an appropiate FETCH (you can look this up in the pl/sql documentation). You won't be able to use a cursor for loop anymore.
10G automatically bulk collects everything.  

--
Sybrand Bakker, Senior Oracle DBA