Use dbms_sql.bind_array to bind array to a cursor

  
SQL>
SQL>
SQL> create table foo (
  2         a   integer,
  3         b   dec( 9, 2 ),
  4         c   character varying( 30 ),
  5         d   national char( 3 )
  6      )
  7  /

Table created.

SQL>      declare
  2        l_cursor number := dbms_sql.open_cursor;
  3        l_ignore number;
  4        l_num dbms_sql.number_table;
  5        l_var dbms_sql.varchar2_table;
  6      begin
  7        dbms_sql.parse( l_cursor,'insert into foo(b,c) values ( :n, :c )',dbms_sql.native );
  8        l_num(1) := 3;
  9        l_num(2) := 4;
 10        l_var(1) := 'Tom';
 11        l_var(2) := 'Joel';
 12        dbms_sql.bind_array( l_cursor, ':N', l_num );
 13        dbms_sql.bind_array( l_cursor, ':C', l_var );
 14        l_ignore := dbms_sql.execute( l_cursor );
 15        dbms_sql.close_cursor( l_cursor );
 16      end;
 17      /

PL/SQL procedure successfully completed.

SQL>      select * from foo;
         A          B C                              D
---------- ---------- ------------------------------ ---
                    3 Tom
                    4 Joel

2 rows selected.

SQL>
SQL> drop table foo;

Table dropped.

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.parse to bind a select statement to a cursor
6.	Call dbms_sql.describe_columns2 to get info for a column
7.	Non-Query DML and DDL Statements
8.	Use DBMS_SQL with the RETURNING clause.
9.	DBMS_SQL.NATIVE;
10.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
11.	Dynamic sql statement with variable binding
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

Use dbms_sql.bind_array to bind array to a cursor : dbms_sql « System Packages « Oracle PL / SQL

Related examples in the same category