Open cursor from a dynamic statement : Cursor Open « Cursor « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Cursor
  3. Cursor Open

Open cursor from a dynamic statement

 
SQL>
SQL> CREATE TABLE MyTable(yourRow INTEGER, yourDesc VARCHAR2(50));

Table created.

SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2      TYPE your_cursor_type IS REF CURSOR;
  3      your_cursor  your_cursor_type;
  4
  5      TYPE dyn_record IS RECORD (
  6          yourrow    INTEGER,
  7          yourdesc   VARCHAR2(50)
  8          );
  9
 10      dyn_rec dyn_record;
 11
 12      dynamic_select_stmt VARCHAR2(100);
 13  BEGIN
 14      dynamic_select_stmt := 'SELECT yourrow, yourdesc FROM mytable';
 15      dynamic_select_stmt := dynamic_select_stmt || ' ORDER BY yourrow DESC';
 16
 17      OPEN your_cursor FOR dynamic_select_stmt;
 18
 19      LOOP
 20          FETCH your_cursor
 21              INTO dyn_rec;
 22          EXIT WHEN your_cursor%NOTFOUND;
 23
 24          DBMS_OUTPUT.PUT_LINE(dyn_rec.yourrow || ' ' || dyn_rec.yourdesc);
 25      END LOOP;
 26      CLOSE your_cursor;
 27  END;
 28  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table mytable;

Table dropped.

SQL>
SQL> --








Related examples in the same category

1.Cursor OPEN Example
2.Open a cursor for read
3.Open a cursor based on the result of row_number over partition by, order by
4.ORA-6511 error: cursor already open
5.An illegal location for a cursor.