. Net calls the Oracle stored procedure and uses array parameters (such as arraylist)

Today, a friend from a project team asked: how to call the stored procedure of Oracle in. NET and input it as an array.

Oracle's PL/SQL statements are very powerful. They support fixed-length arrays and variable-length arrays, and support any custom data types. By reading the ODP documentation, we found that Oracle fully supports using arrays as stored procedure parameters. The document information is provided below.

Array binding

The array bind feature enables applications to bind arrays of A type usingOracleParameterClass. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip.

The following example inserts three rows intoDeptTable with a single database round-trip.OracleCommand ArrayBindCountProperty defines the number of elements of the array to use when executing the statement.

// C# using System;using System.Data;using Oracle.DataAccess.Client;  class ArrayBindSample{  static void Main()  {    OracleConnection con = new OracleConnection();    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";    con.Open();    Console.WriteLine("Connected successfully");     int[] myArrayDeptNo = new int[3] { 10, 20, 30 };    OracleCommand cmd = new OracleCommand();     // Set the command text on an OracleCommand object    cmd.CommandText = "insert into dept(deptno) values (:deptno)";    cmd.Connection = con;     // Set the ArrayBindCount to indicate the number of values    cmd.ArrayBindCount = 3;     // Create a parameter for the array operations    OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);     prm.Direction = ParameterDirection.Input;    prm.Value = myArrayDeptNo;     // Add the parameter to the parameter collection    cmd.Parameters.Add(prm);     // Execute the command    cmd.ExecuteNonQuery();    Console.WriteLine("Insert Completed Successfully");     // Close and Dispose OracleConnection object    con.Close();    con.Dispose();  }}

See also:

"Value" for more information

Oracleparameter array bind Properties

TheOracleParameterClass provides two properties for granular control when using the array bind feature:

• ArrayBindSize

  TheArrayBindSizeProperty is an array of integers specifying the maximum size for each corresponding value in an array.ArrayBindSizeProperty is similar toSizeProperty ofOracleParameterObject, cannotArrayBindSizeProperty specifies the size for each value in an array.

  Before the execution, the application must populateArrayBindSizeProperty; after the execution, ODP. Net populates it.

  TheArrayBindSizeProperty is used only for parameter types that have variable length suchClob,Blob, AndVarchar2. The size is represented in bytes for Binary datatypes, and characters for the Unicode string types. the count for string types does not include the terminating character. the size is inferred from the actual size of the value, if it is not explicitly set. for an output parameter, the size of each value is set by ODP. net. theArrayBindSizeProperty is ignored for fixed-length datatypes.

• ArrayBindStatus

  TheArrayBindStatusProperty is an arrayOracleParameterStatusValues that specify the status of each corresponding value in an array for a parameter. This property is similar toStatusProperty ofOracleParameterObject, cannot thatArrayBindStatusProperty specifies the status for each array value.

  Before the execution, the application must populateArrayBindStatusProperty. After the execution, ODP. Net populates the property. Before the execution, an application usingArrayBindStatusProperty can specifyNULLValue for the corresponding element in the array for a parameter. After the execution, ODP. Net populatesArrayBindStatusProperty, indicating whether the corresponding element in the array hasnullValue, or if data truncation occurred when the value was fetched.

Error Handling for Array binding

If an error occurs during an array bind execution, it can be difficult to determine which element inValueProperty caused the error. ODP. NET provides a way to determine the row where the error occurred, making it easier to find the element in the row that caused the error.

WhenOracleExceptionObject is thrown during an array bind execution,OracleErrorCollectionObject contains one or moreOracleErrorObjects. Each of theseOracleErrorObjects represents an individual error that occurred during the execution, and contains a provider-specific property,ArrayBindIndex, Which indicates the row number at which the error occurred.

The following example demonstrates error handling for Array binding:

/* Database Setupconnect scott/tiger@oracledrop table depttest;create table depttest(deptno number(2));*/ // C# using System;using System.Data;using Oracle.DataAccess.Client;  class ArrayBindExceptionSample{  static void Main()  {    OracleConnection con = new OracleConnection();    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";    con.Open();     OracleCommand cmd = new OracleCommand();     // Start a transaction    OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted);     try    {      int[] myArrayDeptNo = new int[3] { 10, 200000, 30 };      // int[] myArrayDeptNo = new int[3]{ 10,20,30};       // Set the command text on an OracleCommand object      cmd.CommandText = "insert into depttest(deptno) values (:deptno)";      cmd.Connection = con;       // Set the ArrayBindCount to indicate the number of values      cmd.ArrayBindCount = 3;       // Create a parameter for the array operations      OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32);       prm.Direction = ParameterDirection.Input;      prm.Value = myArrayDeptNo;       // Add the parameter to the parameter collection      cmd.Parameters.Add(prm);       // Execute the command      cmd.ExecuteNonQuery();    }    catch (OracleException e)    {      Console.WriteLine("OracleException {0} occured", e.Message);      if (e.Number == 24381)        for (int i = 0; i < e.Errors.Count; i++)          Console.WriteLine("Array Bind Error {0} occured at Row Number {1}",             e.Errors[i].Message, e.Errors[i].ArrayBindIndex);       txn.Commit();    }    cmd.Parameters.Clear();    cmd.CommandText = "select count(*) from depttest";     decimal rows = (decimal)cmd.ExecuteScalar();     Console.WriteLine("{0} row have been inserted", rows);    con.Close();    con.Dispose();  }}

See also:

"Arraybindindex" for more information

Oracleparameterstatus enumeration types

Table: oracleparameterstatus members listsOracleParameterStatusEnumeration values.

Oracleparameterstatus members

Member names
Description

Success
For input parameters, indicates that the input value has been assigned to the column.

For output parameters, indicates that the provider assigned an intact value to the parameter.

NullFetched
Indicates thatNULLValue has been fetched from a column orOUTParameter.

NullInsert
Indicates thatNULLValue is to be inserted into a column.

Truncation
Indicates that truncation has occurred when fetching the data from the column.

Statement caching

Statement caching eliminates the need to parse each SQL or PL/SQL statement before execution by caching server cursors created during the initial statement execution. subsequent executions of the same statement can reuse the parsed information from the cursor, and then execute the statement without reparsing, for better performance.

In order to see performance gains from statement caching, Oracle recommends caching only those statements that will be repeatedly executed. furthermore, SQL or PL/SQL statements shocould use parameters rather than literal values. doing so takes full advantage of statement caching, because parsed information from parameterized statements can be reused even if the parameter values change in subsequent executions. however, if the literal values in the statements are different, the parsed information cannot be reused unless the subsequent statements also have the same literal values.

Statement caching connection string attributes

The following connection string attributes control the behavior of the ODP. Net statement caching feature:

• Statement Cache Size

  This attribute enables or disables ODP. Net statement caching. By default, this attribute is set0(Disabled). If it is set to a value greater0, ODP. net statement caching is enabled and the value specifies the maximum number of statements that can be cached for a connection. once a connection has cached up to the specified maximum cache size, the cursor least recently used is freed to make room to cache the newly created cursor.

• Statement Cache Purge

  This attribute provides a way for connections to purge all statements that are cached when a connection is closed or placed back into the connection pool. By default, this attribute is setfalse, Which means that cursors are not freed when connections are placed back into the pool.

Enabling statement caching through the Registry

To enable statement caching by default for all ODP. NET applications running in a system, without changing the application, set the registry keyHKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\ODP.NET\StatementCacheSizeTo a value greater0.(IDIs the appropriate Oracle Home ID.) This value specifies the number of cursors that are to be cached on the server. By default, it is set0.

Statement caching methods and Properties

The following property and method are relevant only when statement caching is Enabled:

• OracleCommand.AddToStatementCacheProperty

  If statement caching is enabled, having this property settrue(Default) adds statements to the cache when they are executed. If statement caching is disabled or if this property is setfalse, The executed statement is not cached.

• OracleConnection.PurgeStatementCacheMethod

  This method purges all the cached statements by closing all open cursors on the database that are associated with the participant connection. Note that statement caching remains enabled after this call.

Connections and statement caching

Statement caching is managed separately for each connection. Therefore, executing the same statement on different connections requires parsing once for each connection and caching a separate cursor for each connection.

Pooling and statement caching

Pooling and statement caching can be used in conjunction. If Connection pooling is enabled andStatement Cache PurgeAttribute is setfalse, Statements executed on each separate connection are cached throughout the lifetime of the pooled connection. IfStatement Cache PurgeAttribute is settrue, All the cached cursors are freed when the connection is placed back into the pool. When Connection pooling is disabled, cursors are cached during the life time of the connection, but the cursors are closed whenOracleConnectionObject is closed or disposed.

For more information, see:
OTN