Solved

ref cursor fetch hangs if parameters are not hardcoded

Posted on 2011-03-24
9
1,834 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 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

622 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