Cursor fetch loop taking a long time

From: <pmartin513_at_my-deja.com>
Date: Wed, 12 Jan 2000 12:11:00 GMT
Message-ID: <85hr0e$rbh$1@nnrp1.deja.com>

Hello,

I've got a bit of a problem with a cursor fetch loop in Oracle 7.3.4 plsql 3.3.4.

The cursor is in a procedure in a package with the variables needed in the query where clause passed as parameters.

Ie.

Procedure TestFetch(P1 in tableColumn%Type,

                    P2 in tableColumn%Type);

   Cursor cTest is
           Select Column1,Column2
           From   Table
           Where Column3 = P1
           and   Column4 = P2

To use the cursor I use

   Open cTest;
   Loop;

      Fetch cTest Into V1,V2;
      Exit when cTest%NOTFOUND;

   End Loop;

When this procedure is run it takes about 30 min. for 14000 rows.

However the SAME query run in a third party tool (sqlNavigator,SqlPlus) takes 1min. to return ALL the 14000 rows.

Now for the weird part.

If I modifiy the cursor in the package and instead of passing the parameters I put then in the query

ie.

   Cursor cTest is

           Select Column1,Column2
           From   Table
           Where Column3 = 1 /*P1*/
           and   Column4 = 2 /*p2*/

the same cusor loop as before only takes 1 min.

Can anybody explain this difference ?

Thanks in advance for any answers

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 12 2000 - 06:11:00 CST