1. First create the package with PL/SQL
1 Create or ReplacePackage Excutebatchoperate2 as3Type Sqlstr_array is Table of varchar2( +)Index byBinary_integer;4 procedureExcutebulkdata (sqlstrinchSqlstr_array);5 EndExcutebatchoperate;
2. Create a stored procedure within Packagebody
1 Create or ReplacePackage Body Excutebatchoperate2 as3 procedureExcutebulkdata (sqlstrinchSqlstr_array)4 as5 begin6 forIinch 1.. Sqlstr.CountLoop7 Executeimmediate SQLSTR (i);8 EndLoop;9 EndExcutebulkdata;Ten EndExcutebatchoperate;
3. Net demo as follows, can remove comments, modify SQL statement test
1 Public Static intExcutebulkdata (ilist<string>list)2 {3 using(ODAC. OracleConnection conn =NewODAC. 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 ')");Ten Conn. Open (); OneComm.commandtype =CommandType.StoredProcedure; AComm.commandtext ="Excutebatchoperate.excutebulkdata"; -Odac. OracleParameter Param1 =New -Odac. OracleParameter (@"v_string", Odac. ORACLEDBTYPE.VARCHAR2); theParam1.direction =ParameterDirection.Input; -Param1.collectiontype =ODAC. Oraclecollectiontype.plsqlassociativearray; -Param1.value =list. ToArray (); - Comm. Parameters.Add (Param1); + returnComm. ExecuteNonQuery (); - } + } A}
Example
To pass in an array to Oracle, bulk Execute SQL statements