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