How to pass an array of byte arrays using ODP.Net?

Question

User-1732324381 posted

Hi all,

I am trying to pass an array of byte arrays (byte[][]) to Oracle procedure using ODP.Net, which from the Oracle perpective is an array of Raw. I get the following exception:

Oracle.DataAccess.Client.OracleException was unhandled by user code
Message=ORA-06550: line 1, column 52:
PLS-00418: array bind type must match PL/SQL table row type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Source=Oracle Data Provider for .NET
ErrorCode=-2147467259

I am passing the string arrays to Oracle procedures without any problems, but array of byte arrays is an issue. Here is my c# code passing byte[][] to the procedure:

conn = new OracleConnection(tm_connectStr);

saveAnswers = new OracleCommand(commandName, conn);
saveAnswers.CommandType = CommandType.StoredProcedure;

// Input params
//byte array of hashed answers.
byte[][] answers = userAnswers.Select(a => a.Answer).ToArray<byte[]>(); //a.Answer is already a byte array.
OracleParameter pAnswers = saveAnswers.Parameters.Add("p_answers_tab", OracleDbType.Raw, ParameterDirection.Input);
pAnswers.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pAnswers.Value = answers;
pAnswers.Size = answers.Length;

if (conn.State != ConnectionState.Open) conn.Open();

saveAnswers.ExecuteNonQuery(); //This line throws exception.

The Oracle procedure is: 

PROCEDURE create_answers(
p_answers_tab IN g_param_raw_tab_type
) IS
BEGIN
FOR i IN p_answers_tab.FIRST .. p_answers_tab.LAST LOOP
INSERT INTO answers
(
answer,
created_ts
)
VALUES (
p_answers_tab( i ),
SYSTIMESTAMP
);
END LOOP;
END create_answers;

And the table structure is:

Table name: Anwers

1) Coulmn name: anwer, Data type: Raw
2) Coulmn name: created_ts, Data type: Timestamp(6)

Any insight is greatly appreciated.