Solved

ref cursor fetch hangs if parameters are not hardcoded

Posted on 2011-03-24
9
1,766 Views
Last Modified: 2013-12-19
Hi, I have got the following piece of code in an oracle procedure (10g):
[NOTE: cur1 has been defined as a refcursor]
......................................
  OPEN cur1 FOR
    SELECT col1,col2,col3      FROM tab1     WHERE col1=<parameter1> ;
    LOOP
    FETCH cur1
       INTO l_col1,l_col2,l_col3;
       EXIT WHEN cur1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(l_col1);
    END LOOP;
.......................................

Using the debugger in PL/SQL developer, I found that the code hangs and stays at 'FETCH cur1' statement.

Then I hardcoded <parameter1> to 1 , so the code became:
......................................
  OPEN cur1 FOR
    SELECT col1,col2,col3      FROM tab1     WHERE col1=1 ;
    LOOP
    FETCH cur1
       INTO l_col1,l_col2,l_col3;
       EXIT WHEN cur1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(l_col1);
    END LOOP;
.......................................

Now the procedure runs smoothly without any issues.

Any idea why does it not get past the Fetch statement in the first case?
Thanks in advance.
0
Comment
Question by:subratoc
9 Comments
 
LVL 5

Expert Comment

by:morgulo
ID: 35210607
Try to execute this select statement. I think the table is big and there are no index on col1 or col1 has only few distinct values. Query is simply executing very long time...
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 35212883
what's the difference between the explain plans of

SELECT col1,col2,col3      FROM tab1     WHERE col1=:b1;

and

SELECT col1,col2,col3      FROM tab1     WHERE col1=1;

please post results using  dbms_xplan.display
0
 
LVL 1

Expert Comment

by:wasser
ID: 35262690
Do you need to use dynamic SQL to use the parameter value?
So does it run when you change:
OPEN cur1 FOR
    SELECT col1,col2,col3      FROM tab1     WHERE col1=<parameter1> ;
 
TO:
EXECUTE IMMEDIATE  'OPEN cur1 FOR
    SELECT col1,col2,col3      FROM tab1     WHERE col1=  '|| parameter1 ;

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35262723
using execute immediate like that opens up the possibility of sql injection

you "could" use the USING clause to be safe with binding but that's just a less efficient way of doing the same thing
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:subratoc
ID: 35281301
Thanks sdstuber. I looked at the execution plans and found that different indexes are being used with and without the hardcoded values. Now I need to find out why that is happening. The statistics are updated though. I have suppressed the use of the undesired index by using a function for now.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35281377
glad I could help.

The "why" is because Oracle has more information with hardcoded value than with a variable.
Bind peeking is supposed to help with that but it isn't perfect and can cause more problems than it solves
with some queries
0
 
LVL 1

Expert Comment

by:wasser
ID: 35283326
@ sdstuber,
Thanks for your comment regarding the Execute Immediate dynamic SQL.  
Everytime I come to EE, I learn something.  
Would first creating a sql_stmt variable, and putting the dynamic SQL in the sql_stmt variable, then validating format, data type, length etc. be a valid way to do the dynamic SQL route?  

Thanks,
wasser
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35283410
you could do that as a way to protect against sql injection, but you'll need to write a good sql parser which is a lot of work.

better is to use the USING clause

EXECUTE IMMEDIATE  
    '<<<your query here>>>> WHERE some_column=  :some_parameter '   USING parameter1 ;
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35283433
the query itself could also be a varchar2 variable if it would aid in construction of the dynamic sql, but in an example like this query there is nothing dynamic about it so no need
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now