SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_InsertRecords VARCHAR2(500);
4 v_NUMRows INTEGER;
5
6 BEGIN
7 v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
8 v_InsertRecords := 'INSERT INTO MyTable(MyRow,MyDesc)VALUES (:mynum,:mytext)'; -- Write SQL to insert records
9
10 DBMS_SQL.PARSE(v_CursorID,v_InsertRecords,DBMS_SQL.V7);
11 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mynum',1);
12 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mytext','One');
13 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
14 DBMS_OUTPUT.PUT_LINE('The number of records just processed is: ' || v_NUMRows);
15
16 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mynum',2);
17 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mytext','Two');
18 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
19 DBMS_OUTPUT.PUT_LINE('The number of records just processed is: '|| v_NUMRows);
20
21 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mynum',3);
22 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mytext','Three');
23 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
24 DBMS_OUTPUT.PUT_LINE('The number of records just processed is: '|| v_NUMRows);
25
26 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mynum',4);
27 DBMS_SQL.BIND_VARIABLE(v_CursorID, ':mytext','Four');
28 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
29 DBMS_OUTPUT.PUT_LINE('The number of records just processed is: '|| v_NUMRows);
30
31 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
32 DBMS_OUTPUT.PUT_LINE('The number of records just processed is: '|| v_NUMRows);
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 RAISE;
37
38 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
39 COMMIT;
40 END;
41 /
The number of records just processed is: 1
The number of records just processed is: 1
The number of records just processed is: 1
The number of records just processed is: 1
The number of records just processed is: 1
PL/SQL procedure successfully completed.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL>
SQL> --