| REF CURSOR - With For Loop [message #188243] |
Thu, 17 August 2006 09:42  |
gd001
Messages: 3
Registered: August 2006
|
Junior Member |
|
|
Hi
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)
Is
sql_stmt varchar2(2000);
type ref_cur is Ref Cursor;
t_cursor ref_cur;
Begin
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)
Loop
dbms_output.put_line ('Carrier Key ' || x.column1);
dbms_output.put_line ('Account Key ' ||x.column);
end loop;
End;
/
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.
-Gautam
|
|
|
|
| Re: REF CURSOR - With For Loop [message #188253 is a reply to message #188243] |
Thu, 17 August 2006 11:00  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
