Home » SQL & PL/SQL » SQL & PL/SQL » REF CURSOR - With For Loop
REF CURSOR - With For Loop [message #188243] Thu, 17 August 2006 09:42 Go to next message
Messages: 3
Registered: August 2006
Junior Member

I had a question about ref Cursors and if we can open refcursors directly using a for loop e.g for x in <refcurvariable>

Here is the scenarion

I am creating a stored procedure which takes in a few input parameters based on which I am building the cursor Query at runtime I receive at runtime. Once this query has been built at runtime, i associate it with with a ref cursor variable and use
open , fetch and Close statement to work on the data. Instead of Using the Open , Fetch and Close statement, I want to open a cursor directly in a for loop. I am not sure if refcursor supports it but in case anyone has managed to achieve it,it will help me.

here the e.g

create or replace PROCEDURE Test (p_column_1 IN varchar2,
p_column_2 IN varchar2,
p_val1 IN NUMBER,
p_val2 IN NUMBER)

sql_stmt varchar2(2000);

type ref_cur is Ref Cursor;
t_cursor ref_cur;


sql_stmt := 'Select * from Test_table';

if p_column_1 is not null and p_column_2 is not null then
sql_stmt := sql_stmt || ' where '|| p_column_1 || '=' || p_val1 || ' and ' || p_column_2 || '=' || p_val2 ;
End if;

if p_column_1 is not null and p_column_2 is null then
sql_stmt := sql_stmt || ' where '|| p_column_1 || '=' || p_val1;
End if;

if p_column_1 is null and p_column_2 is not null then
sql_stmt := sql_stmt || ' where '|| p_column_2 || '=' || p_val2 ;
End if;


for x in (open t_cursor for sql_stmt)

dbms_output.put_line ('Carrier Key ' || x.column1);
dbms_output.put_line ('Account Key ' ||x.column);

end loop;


Although the code above doesn't compile, I want to use the ref cursor in the for loop as shown above instead of using a OPEN, FETCH and Close statement which are traditionally associated with For Loops.

Can anyone advise if they have already implemented it. I am thinking that there is an limitation in ORACLE achieving this.

Re: REF CURSOR - With For Loop [message #188253 is a reply to message #188243] Thu, 17 August 2006 11:00 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Note: You do not need to report your own message to the moderators.

A cursor FOR loop can only work with static SQL. Since you are using dynamic SQL, you will need to use the open/fetch/close sequence.

Also, you are embedding the literal search values in the generated SQL - not a good idea.
Previous Topic: How to find out whether an object is function/procedure under a package using oracle dictionery ?
Next Topic: Mutating Tables
Goto Forum:

Current Time: Wed Oct 26 18:11:08 CDT 2016

Total time taken to generate the page: 0.15369 seconds