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: cursor result return

Re: cursor result return

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 21 Feb 2005 22:40:59 +0100
Message-ID: <s0lk11pqpj8qggj32al4d8rj3srcsqsu5s@4ax.com>


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
Received on Mon Feb 21 2005 - 15:40:59 CST

Original text of this message

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