subratoc
asked on
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_col
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_col
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.
[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_col
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_col
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.
morgulo
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ;
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 ;
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
you "could" use the USING clause to be safe with binding but that's just a less efficient way of doing the same thing
ASKER
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.
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
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
@ 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
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
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 ;
better is to use the USING clause
EXECUTE IMMEDIATE
'<<<your query here>>>> WHERE some_column= :some_parameter ' USING parameter1 ;
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