Im new to cursors in Oracle. I have a piece of SQL that is contained in a variable. I want to open a cursor with this sql. How do I do this? Seems simple but all the examples I find just have the sql typed directly below the "open cursor_name for" statement.
Here is what I would like to run (assume I have variable v_sql with my sql query):
open my_cursor for v_sql;
Oracle doesnt like this though. I also tried
open my_cursor for
execute immediate v_sql;
Help please.
You need to declare it as a ref cursor and then open it for the your SQL statement. Please look at the example below. This, of course, is assuming you do not have any input bindings to your sql.
sql> ed
Wrote file afiedt.buf
1 declare
2 c1 sys_refcursor;
3 v_empno number;
4 v_ename varchar2(30);
5 begin
6 open c1 for 'select empno, ename from emp';
7 loop
8 fetch c1 into v_empno, v_ename;
9 dbms_output.put_line(v_empno || '--' || v_ename);
10 exit when c1%notfound;
11 end loop;
12 close c1;
13* end;
sql> /
7369--SMITH
7499--ALLEN
7521--WARD
7566--JONES
7654--MARTIN
7698--BLAKE
7782--CLARK
7788--SCOTT
7839--KING
7844--TURNER
7876--ADAMS
7900--JAMES
7902--FORD
7934--MILLER
7934--MILLER
Check this link... http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/11_dynam.htm#i13057
The first snippet you have will work fine, as long as v_sql is a VARCHAR and my_cursor is declared as a REF CURSOR. You can then FETCH from that just like you would with a static cursor.
But as OMG Ponies says, you have to be careful about where your SQL is coming from.
my_cursor ref cursor;. But starting in Oracle 9i there is a built in type called SYS_REFCURSOR which you can use instead of declaring your own type every time. Code that predated 9i will still have the old style in it.OMG Ponies is completely correct,
but here is just a different way to do the same thing
Var X Refcursor;
Begin
Open :X For
Select 1 Num, 'b' Co
From Dual
Union
Select 2 Num, 'c' Co
From Dual;
end;
/
print x;
Note when you do anything in Oracle like opening cursors or whatnot you will need to be within a BEGIN/END and you cannot simply do:
Var X Refcursor;
Open X For
Select 1 Num, 'b' Co
From Dual
Union
Select 2 Num, 'c' Co
From Dual;
This will not Work! You must enclose the OPEN cursor within a BEGIN/END block (be it an anonomous block or a procedure...)
Create or replace Procedure Ccc(X Out sys_Refcursor)
As
begin
Open X For
Select 1 Num, 'b' Co
From Dual
Union
Select 2 Num, 'c' Co
From Dual;
End Ccc;
/
Var X Refcursor;
Begin
Ccc(:X);
End;
/
print x;
note the :x in the begin/end in the anonomous blocks is to tell the sql engine you are utilizing a variable created outside the block. within packages/procs it is unnecessary.
v_sql. It is perfectly safe to assemble a SQL query inside a procedure.open my_cursor for v_sql;is the correct usage, so the problem must lie with the surrounding code context, or the statement you're executing.