Oracle: How to pass empty associative array to Stored Procedure
One of the possible ways to pass a collection of values to a stored procedure is to use Associative Array (also known as Index-By Tables). For example, the declaration of the stored procedure accepting array of strings may resemble the following:
TYPE str_table_type IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER;
PROCEDURE Save_Something (
p_str_array IN str_table_type,
...
p_MSG_OUT OUT VARCHAR2
);
To call such stored procedure we need to create and properly fill out the p_str_array parameter. The code doing that may look like shown below:
// create the parameter.
// In case of Associative Array the passed OracleDbType.Varchar2 is a type of the elements
OracleParameter array = new OracleParameter("p_str_array",
OracleDbType.Varchar2, ParameterDirection.Input);
// Specify it as PL/SQL Associative Array
array.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
string[] values = new[] { "Value One", "Value Two", "Value Three" };
// Set the values for PL/SQL Associative Array
array.Value = values;
// Set the maximum number of elements in the PL/SQL Associative Array,
// in case of Input direction, Size usually equals to the number of passed values
array.Size = values.Length;
// Pass to the command and call one
using (var cmd = new OracleCommand("DNF.SOME_PKG.Save_Something")
{ CommandType = CommandType.StoredProcedure })
{
cmd.Parameters.Add(array);
cmd.Parameters.Add(new OracleParameter("p_MSG_OUT", OracleDbType.Varchar2,
ParameterDirection.Output) { Size = 2000 });
cmd.ExecuteNonQuery();
// process p_MSG_OUT somehow
...
}
The use of the Associative Array is pretty straightforward unless we are trying to pass an empty array. I tried a few ideas coming to mind first (listed below), none of them doesn’t work though:
array.Value = null;
array.Size = 0;
...
array.Value = new string[0];
array.Size = 0;
...
array.Value = new string[1] { null };
array.Size = 1;
The “OracleParameter.Value is invalid” was the most popular exception I was getting at that moment. Ultimately, however, I found a working variant:
// for array of strings
array.Value = new OracleString[1] { OracleString.Null };
array.Size = 1;
...
// for array of Int32s, decimals and so on
array.Value = new OracleDecimal[1] { OracleDecimal.Null };
array.Size = 1;
As the result, the following common method and a few accompanying ones have been born to create Associative Arrays of different types:
// Creates an Associative Array parameter, knows how to treat empty collections
// Note: specify the maxNumberOfElementsInArray as an expected number of returned elements
public OracleParameter CreateAssociativeArray<ValueType, OracleType>(
string name, List<ValueType> values,
ParameterDirection direction, OracleDbType oracleDbType,
OracleType nullValue, int? maxNumberOfElementsInArray)
{
bool isArrayEmpty = values == null || values.Count == 0;
OracleParameter array = new OracleParameter(name, oracleDbType, direction);
array.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
array.Value = !isArrayEmpty ? values.ToArray() :
(object)new OracleType[1] { nullValue };
array.Size = !isArrayEmpty ? values.Count : 1;
// if it's Output/InputOutput parameter, set the maximum possible number of elements.
if (maxNumberOfElementsInArray != null &&
(direction == ParameterDirection.Output || direction == ParameterDirection.InputOutput))
array.Size = Math.Max(array.Size, maxNumberOfElementsInArray.Value);
return array;
}
public OracleParameter CreateInt32AssociativeArray(string name,
List<int> values, ParameterDirection direction = ParameterDirection.Input,
int? maxNumberOfElementsInArray = null)
{
return CreateAssociativeArray<int, OracleDecimal>(name, values,
direction, OracleDbType.Int32, OracleDecimal.Null, maxNumberOfElementsInArray);
}
public OracleParameter CreateDecimalAssociativeArray(string name,
List<decimal> values, ParameterDirection direction = ParameterDirection.Input,
int? maxNumberOfElementsInArray = null)
{
return CreateAssociativeArray<decimal, OracleDecimal>(name, values, direction,
OracleDbType.Decimal, OracleDecimal.Null, maxNumberOfElementsInArray);
}
public OracleParameter CreateStringAssociativeArray(string name,
List<string> values, ParameterDirection direction = ParameterDirection.Input,
int? maxNumberOfElementsInArray = null, int maxLength = 255)
{
var res = CreateAssociativeArray<string, OracleString>(name, values, direction,
OracleDbType.Varchar2, OracleString.Null, maxNumberOfElementsInArray);
if(direction == ParameterDirection.Output || direction == ParameterDirection.InputOutput)
{
int curMaxLen = maxLength;
if(values != null)
values.ForEach(s => { if (curMaxLen < s.Length) curMaxLen = s.Length; });
res.ArrayBindSize = new int[res.Size];
for (int i = 0; i < res.Size; i++)
res.ArrayBindSize[i] = curMaxLen;
}
return res;
}
There are two important things to note here. The first one, in case of Output or InputOutput direction, the Size has to be set to the maximum number of elements you expect to get from the stored procedure (see the CreateAssociativeArray method). Specify this maximum number in the maxNumberOfElementsInArray. For example, you want to pass 3 elements to the stored procedure and get 10 back (InputOutput direction). Set the maxNumberOfElementsInArray to 10, otherwise CreateAssociativeArray will set Size to 3.
The second thing is the special processing of array of strings (see the CreateStringAssociativeArray method). String is variable-length element type (Varchar2 and so on). So, for strings we need to define the ArrayBindSize property. ArrayBindSize is the collection each element of which specifies the length of the corresponding element in the Value property. Being longer than specified, the element will be truncated. Pass the maximum allowed length of elements in maxLength, otherwise it will be set to 255 (default) or the maximum length found in the Value property.
Use the following code to create arrays of strings, Int32s and decimals in case of Input direction (or create your own “accompanying” methods for other types):
OracleParameter intArray = CreateInt32AssociativeArray("p_int_array",
new List<int> { 1, 2, 3 });
// pass empty array to the stored procedure
OracleParameter decimalArray = CreateDecimalAssociativeArray("p_dec_array", null);
OracleParameter strArray = CreateStringAssociativeArray("p_str_array",
new List<string> { "Value One", "Value Two", "Value Three" });
If Associative Array should be an Output or InputOutput parameter, the code may look like:
// Output parameter
// we expect no more than 10 elements to be returned
OracleParameter intArray = CreateInt32AssociativeArray("p_int_array", null,
ParameterDirection.Output, 10);
...
// InputOutput parameter
// declare input values
string[] tmpAtrArray = new string[3] { "Value One", "Value Two", "Value Three" };
// we expect no more than 20 strings to be returned
// returned strings suppose to be no longer than 100
OracleParameter strArray = CreateStringAssociativeArray("p_str_array",
new List<string>(tmpAtrArray), ParameterDirection.InputOutput, 20, 100);
To read, for example, number values (presented as OracleDecimal) from the output Associative Array I use the following two utility methods:
public Nullable<T> GetValue<T>(OracleDecimal oracleDecimal,
Func<OracleDecimal, T> convert) where T : struct
{
if (oracleDecimal == null || oracleDecimal.IsNull)
return null;
return convert(oracleDecimal);
}
public List<T> GetValues<T>(OracleParameter parameter,
Func<OracleDecimal, T> convert) where T : struct
{
List<T> res = new List<T>();
if(parameter.Value != null)
{
OracleDecimal[] values = parameter.Value as OracleDecimal[];
if(values != null)
foreach(OracleDecimal oDecimal in values)
{
Nullable<T> val = GetValue(oDecimal, convert);
if (val != null)
res.Add(val.Value);
}
}
return res;
}
So, the reading of integers looks as follows:
OracleParameter intArray = CreateInt32AssociativeArray("p_int_array", null,
ParameterDirection.Output, 10);
...
// read returned values
List<int> res = GetValues<int>(intArray, od => { return od.ToInt32(); });
All code has been written and tested with the ODAC 12c Release 2.
