Before reading this article, please go through the following articles
Code Example
This example binds three OracleParameter objects as PL/SQL Associative Arrays: Param1 as an In parameter, Param2 as an InputOutput parameter, and Param3 as an Output parameter.
PL/SQL Package: My Pack
CREATE OR REPLACE PACKAGE MYPACK AS
TYPE AssocArrayVarchar2_t IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
PROCEDURE TestVarchar2(
Param1 IN AssocArrayVarchar2_t,
Param2 IN OUT AssocArrayVarchar2_t,
Param3 OUT AssocArrayVarchar2_t
);
END MYPACK;
CREATE OR REPLACE PACKAGE BODY MYPACK AS
PROCEDURE TestVarchar2(
Param1 IN AssocArrayVarchar2_t,
Param2 IN OUT AssocArrayVarchar2_t,
Param3 OUT AssocArrayVarchar2_t
)
IS
i INTEGER;
BEGIN
-- Copy a few elements from Param2 to Param3
Param3(1) := Param2(1);
Param3(2) := NULL;
Param3(3) := Param2(3);
-- Copy all elements from Param1 to Param2
Param2(1) := Param1(1);
Param2(2) := Param1(2);
Param2(3) := Param1(3);
FOR i IN 1..3 LOOP
INSERT INTO T1 VALUES (i, Param2(i));
END LOOP;
FOR i IN 1..3 LOOP
SELECT COL2 INTO Param2(i) FROM T2 WHERE COL1 = i;
END LOOP;
END TestVarchar2;
END MYPACK;
ODP.NET Example
public void BindAssocArray()
{
// ...
OracleCommand cmd = new OracleCommand(
"begin MyPack.TestVarchar2(:1, :2, :3); end;", con);
OracleParameter Param1 = cmd.Parameters.Add(...);
OracleParameter Param2 = cmd.Parameters.Add(...);
OracleParameter Param3 = cmd.Parameters.Add(...);
Param1.Direction = ParameterDirection.Input;
Param2.Direction = ParameterDirection.InputOutput;
Param3.Direction = ParameterDirection.Output;
// Specify that we are binding PL/SQL Associative Array
Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
// Setup the values for PL/SQL Associative Array
Param1.Value = new string[3]{"First Element",
"Second Element",
"Third Element"};
Param2.Value = new string[3]{"First Element",
"Second Element",
"Third Element"};
Param3.Value = null;
// Specify the maximum number of elements in the PL/SQL Associative Array
Param1.Size = 3;
Param2.Size = 3;
Param3.Size = 3;
// Setup the ArrayBindSize for Param1
Param1.ArrayBindSize = new int[3]{13, 14, 13};
// Setup the ArrayBindStatus for Param1
Param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};
// Setup the ArrayBindSize for Param2
Param2.ArrayBindSize = new int[3]{20, 20, 20};
// Setup the ArrayBindSize for Param3
Param3.ArrayBindSize = new int[3]{20, 20, 20};
// execute the cmd
cmd.ExecuteNonQuery();
// print out the parameter's values
// ...
}
Array Binding
The array bind feature enables applications to bind arrays of a type using the OracleParameter class. Using the array bind feature, an application can insert multiple rows into a table in a single database round-trip. The following code example inserts three rows into the Dept table with a single database round-trip. The OracleCommand ArrayBindCount property defines the number of elements of the array to use when executing the statement.
// Create an array of values that need to be inserted
int[] myArrayDeptNo = new int[3]{10, 20, 30};
// Set the command text on an OracleCommand object
cmd.CommandText = "insert into dept(deptno) values (:deptno)";
// 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();
OracleParameter Array Bind Properties
The OracleParameter object provides two properties for granular control when using the array bind feature.
- ArrayBindSize Property: The ArrayBindSize property is an array of integers specifying the maximum size for each corresponding value in an array. The ArrayBindSize property is similar to the Size property of an OracleParameter except ArrayBindSize specifies the size for each value in an array. Before the execution, the application must populate ArrayBindSize; after the execution, ODP.NET populates the ArrayBindSize. ArrayBindSize is used only for parameter types that have variable length such as Clob, Blob and Varchar2. 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. The ArrayBindSize property is ignored for fixed length datatypes.
- ArrayBindStatus Property: The ArrayBindStatus property is an array of OracleParameterStatus values specifying status of each corresponding value in an array for a parameter. This property is similar to the Status property of OracleParameter, except that ArrayBindStatus specifies the status for each value in an array. Before the execution, the application must populate the ArrayBindStatus property and after the execution, ODP.NET populates it. Before the execution, an application using ArrayBindStatus can specify a NULL value for the corresponding element in the array for a parameter. After the execution, ODP.NET populates the ArrayBindStatus array, indicating whether the corresponding element in the array has a NULL value 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 in the Value property 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.
When an OracleException is thrown during an Array Bind execution, the OracleErrorCollection contains one or more OracleError objects. Each of these OracleError objects 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.
Code Example
try
{
// An Array Bind execution errors out
}
catch (OracleException e)
{
Console.WriteLine($"OracleException {e.Message} occurred");
for (int i = 0; i < e.Errors.Count; i++)
{
Console.WriteLine($"Array Bind Error {e.Errors[i].Message} occurred at Row Number {e.Errors[i].ArrayBindIndex}");
}
}
OracleParameterStatus Enumeration Types
Table 3.10 provides different values for OracleParameterStatus enumeration.
To gain a more comprehensive understanding of the subject, please read the next part,