Solved

ref cursor fetch hangs if parameters are not hardcoded

Posted on 2011-03-24
9
1,807 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
Technology Partners: 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!

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 127
Oracle database T-1 Setup 7 45
Convert summed columns to Rows 6 23
Query for non-UTF8 characters in a column in Oracle 11 37
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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

734 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