SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_SelectRecords VARCHAR2(500);
4 v_NUMRows INTEGER;
5 v_MyNum INTEGER;
6 v_MyText VARCHAR2(50);
7
8 BEGIN
9 v_CursorID := DBMS_SQL.OPEN_CURSOR;
10 v_SelectRecords := 'SELECT * from MyTable';
11 DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
12 DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
13 DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50);
14
15 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
16 LOOP
17 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
18 EXIT;
19 END IF;
20
21 DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
22 DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
23
24 DBMS_OUTPUT.PUT_LINE(v_MyNum || ' ' || v_MyText);
25
26 END LOOP;
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 RAISE;
31 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
32 END;
33 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table mytable;
Table dropped.
SQL> --