Hello all,
since two days now I'm stuck with a weird problem. I searched the interwebz and tried lots of suggestions, but nothing actually worked. I'm always receiving the following error:
Oracle.DataAccess.Client.OracleException: ORA-06513: PL/SQL: index for PL/SQL table out of range for host language array
ORA-06512: at line 1
I am trying to call a stored function that returns an associative array of varchar2 from C# code. The function resides in a package whose body is unknown to me, I just can see the declaration of the function, which looks like this:
FUNCTION GetSomething( pSomeValue1 IN VARCHAR2,
pSomeValue2 IN NUMBER DEFAULT 1,
pSomeValue3 IN VARCHAR2 DEFAULT NULL )
RETURN FILECOL;
And the definition for FILECOL:
TYPE FILECOL IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
My code looks like the following:
OracleCommand oracleCommand = new OracleCommand
{
Connection = connection,
CommandText = "MY_PACKAGE.GetSomething",
CommandType = CommandType.StoredProcedure,
BindByName = true,
};
OracleParameter docNumber = new OracleParameter("pSomeValue1", OracleDbType.Varchar2, "ABCDE",
ParameterDirection.Input);
OracleParameter fileType = new OracleParameter("pSomeValue2", OracleDbType.Int16, 1,
ParameterDirection.Input);
OracleParameter fileExtension = new OracleParameter("pSomeValue3", OracleDbType.Varchar2, DBNull.Value,
ParameterDirection.Input);
OracleParameter returnValue = new OracleParameter("ret", OracleDbType.Varchar2,
ParameterDirection.ReturnValue);
const int arraySize = 100;
returnValue.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
returnValue.Size = arraySize;
returnValue.ArrayBindSize = Enumerable.Repeat(100, arraySize).ToArray();
oracleCommand.Parameters.Add(returnValue);
oracleCommand.Parameters.Add(docNumber);
oracleCommand.Parameters.Add(fileType);
oracleCommand.Parameters.Add(fileExtension);
try
{
connection.Open();
oracleCommand.ExecuteNonQuery(); // <-- Here the error occurrs
}
finally
{
connection.Close();
}
Does anybody have an idea what is wrong here? I saw people receiving this error when they iterated over the result, but I don't even get a result, since it fails before. The function should return ~14 values, so I should also be safe with the array size of 100. For the curious: I already tried an array size of 1000000, with the same exceptional result.
Best regards,
MHR