Dynamic sql statement with variable binding

  
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> --

Related examples in the same category

1.	First DBMS_SQL Example
2.	Pass a query statement to a stored procedure
3.	Use dbms_sql.open_cursor create a cursor
4.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
5.	Use dbms_sql.bind_array to bind array to a cursor
6.	Use dbms_sql.parse to bind a select statement to a cursor
7.	Call dbms_sql.describe_columns2 to get info for a column
8.	Non-Query DML and DDL Statements
9.	Use DBMS_SQL with the RETURNING clause.
10.	DBMS_SQL.NATIVE;
11.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
12.	Dynamic select statement and row fetch
13.	Execute PL/SQL block
14.	DBMS_SQL package: dynamic SQL
15.	Pass where clause to a stored procedure
16.	Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
17.	Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
18.	drop the supplied table using dynamic SQL.
19.	DBMS_SQL.BIND_VARIABLE_CHAR
20.	illustrate the importance of setting out_value_size.
21.	Calling a function which uses dynamic SQL from within an SQL statement.
22.	Illustrates the interaction of roles and dynamic SQL.
23.	illustrates a DDL statement which is built dynamically from the procedure parameters.
24.	Use dbms_sql to process query, cursor and value
25.	Use dbms_sql.bind_variable to bind variable
26.	dbms_sql.varchar2_table type variable

Dynamic sql statement with variable binding : dbms_sql « System Packages « Oracle PL / SQL

Related examples in the same category