ref cursor fetch hangs if parameters are not hardcoded

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.
subratocAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
morguloCommented:
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
 
wasserCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
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
 
subratocAuthor Commented:
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
 
sdstuberCommented:
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
 
wasserCommented:
@ 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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
All Courses

From novice to tech pro — start learning today.