To pass in an array to Oracle, bulk Execute SQL statements

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