Calling Oracle Associative Arrays from .NET – Passing empty array

Oracle may throw binding exceptions if the array to be passed is empty. Moreover, an empty array cannot be substituted by Nothing value as Nothing value of Oracle Parameter means the parameter value was not set (all input parameters values must be set before the command execution). DBNull value (which is equivalent of Oracle Nothing value) cannot be converted to an associative array.

When it is required to pass an empty array an array containing single null value can be used. That allows correct binding of the parameter by Oracle Provider and passing an empty array to stored procedure:

.NET caller:

'--------- INPUT parameter -------
Dim paramInput As New OracleParameter("thearray", OracleDbType.Varchar2, ParameterDirection.Input)
'1. set CollectionType property
paramInput.CollectionType = OracleCollectionType.PLSQLAssociativeArray
'2. set Value property
paramInput.Value = New String() {Nothing}

‘——- OUTPUT parameter ——–

Dim paramOutput As New OracleParameter(“results”, OracleDbType.Varchar2, ParameterDirection.Output)
‘1. set CollectionType property
paramOutput.CollectionType = OracleCollectionType.PLSQLAssociativeArray
‘2. set Size property
paramOutput.Size = 3
‘3. set ArrayBindSize property
paramOutput.ArrayBindSize = New Integer() {100, 100, 100}> lCmd.Parameters.Clear()
lCmd.Parameters.Add(paramInput)
lCmd.Parameters.Add(paramAgentIds)

lCnn.Open()
lCmd.ExecuteNonQuery()
lCnn.Close()

Dim result() As OracleString = CType(paramOutput.Value, OracleString())

PL/SQL stored procedure:

create or replace
package A_mypack5 as
PROCEDURE test_it(
thearray IN INS_PKG_GLB.strings, -- it is an array of strings indexed by PLS_INTEGER
results OUT NOCOPY
END;

create or replace
PACKAGE BODY A_MYPACK5 AS
PROCEDURE test_it(
thearray IN INS_PKG_GLB.strings,
results OUT NOCOPY INS_PKG_GLB.strings) IS
begin
results(1) := ‘array has ‘ || thearray.count || ‘ elements’;
results(2) := ‘second’;
results(3) := ‘third’;
END;
END;

* This source code was highlighted with Source Code Highlighter.