?
Solved

ref cursor fetch hangs if parameters are not hardcoded

Posted on 2011-03-24
9
Medium Priority
?
1,862 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 74

Accepted Solution

by:
sdstuber earned 1600 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 74

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
 

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 74

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 74

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 74

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

719 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