Input arrays to Oracle and execute SQL statements in batches. oraclesql

Input arrays to Oracle and execute SQL statements in batches. oraclesql

1. Create a package using PL/SQL

1 create or replace package excuteBatchOperate2 as3  type sqlStr_Array is table of varchar2(1000) index by binary_integer;4  procedure excuteBulkData(sqlStr in sqlStr_Array);5  end excuteBatchOperate;

2. Create a stored procedure in packagebody

 1 create or replace package body excuteBatchOperate 2 as 3 procedure excuteBulkData(sqlStr in sqlStr_Array) 4   as 5   begin 6     for i in 1..sqlStr.count loop 7        execute immediate sqlStr(i); 8     end loop; 9   end excuteBulkData;10   end excuteBatchOperate;

3. the. Net demo is as follows. You can remove comments and modify the SQL statement for testing.

1 public static int excuteBulkData (IList <string> list) 2 {3 using (ODAC. oracleConnection conn = new ODAC. oracleConnection (connStr) 4 {5 using (ODAC. oracleCommand comm = conn. createCommand () 6 {7 // IList <string> list = new List <string> (); 8 // list. add ("insert into parts1 (pname) values ('sadfsa ')"); 9 // list. add ("insert into parts1 (pnum, pname) values (22222, 'rrrrrr ')"); 10 conn. open (); 11 comm. commandType = CommandType. storedProcedure; 12 comm. commandText = "excuteBatchOperate. excuteBulkData "; 13 ODAC. oracleParameter Param1 = new14 ODAC. oracleParameter (@ "v_string", ODAC. oracleDbType. varchar2); 15 Param1.Direction = ParameterDirection. input; 16 Param1.CollectionType = ODAC. oracleCollectionType. PLSQLAssociativeArray; 17 Param1.Value = list. toArray (); 18 comm. parameters. add (Param1); 19 return comm. executeNonQuery (); 20} 21} 22}Example