Oracle PLSQL setting a cursor from a variable -


New to the cursor in the Oracle. I have a piece of SQL that is in a variable i can use this sql I want to open a cursor with How do I do this? Looks simple, but in all the instances I just get the SQL typed under the "open cursor_name" statement.

Here's what I'm running (assuming the variable v_sql is with my SQL query):

my_cursor for v_sql;

Oracle does not however like this. I also tried

  open my_cursor to execute instant v_sql;  

Please help.

You have to declare it as a reverse cursor and then open it for your SQL statement . Please see the example below. This is, of course, assuming that you do not have any input binding for your SQL.

  sql> Ed written file afiedt.buf declared 1 c 2 sys_refcursor; 3 v_empno number; 4 v_ename varchar2 (30); 5 '6 select empno, emame to emame' to open 6 open C1; 7 loop 8 bring c1 to v_empno, v_ename; 9 dbms_output.put_line (v_empno || '-' || v_ename); 10 exits when C1% is unavailable; 11 end loop; 12 closed C1; 13 * end; SQL & gt; / 736 9 - Smith 7499 - Ellen 7521 - Word 7566 - Jones 7654 - Martin 7698 - Black 7782 - Clarke 7788 - Scott 7839 - King 7844 - Turner 7876 - Adams 7900 - Jams 7902 - Ford 7934 - Miller 7934 - Miller  

Check this link ...


Comments

Popular posts from this blog

c# - sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible' -

Calling GetGUIThreadInfo from Outlook VBA -

Obfuscating Python code? -