SQL>
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_MatchRecord 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_MatchRecord := 'BEGIN
11 SELECT MyRow,MyDesc
12 INTO :MyRow, :MyText FROM MyTable
13 WHERE MyRow = 2;
14 END;';
15
16 DBMS_SQL.PARSE(v_CursorID,v_MatchRecord,DBMS_SQL.V7);
17 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':MyRow',v_MyNum);
18 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':MyText',v_MyText,50);
19
20 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
21 DBMS_SQL.VARIABLE_VALUE(v_CursorId,':MyRow',v_MyNum);
22 DBMS_SQL.VARIABLE_VALUE(v_CursorId,':MyText',v_MyText);
23 DBMS_OUTPUT.PUT_LINE(v_MyNum || ' ' || v_MyText);
24 EXCEPTION
25 WHEN OTHERS THEN
26 RAISE;
27
28 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
29 END;
30 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 26
SQL>
SQL>
SQL> drop table mytable;
Table dropped.
SQL> --