Last post Dec 06, 2011 02:16 PM by Diligent
Dec 05, 2011 12:37 PM|Diligent|LINK
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
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) ISBEGINFOR i IN p_answers_tab.FIRST .. p_answers_tab.LAST LOOPINSERT INTO answers(answer,created_ts)VALUES (p_answers_tab( i ),SYSTIMESTAMP);END LOOP;END create_answers;And the table structure is:Table name: Anwers1) Coulmn name: anwer, Data type: Raw2) Coulmn name: created_ts, Data type: Timestamp(6)Any insight is greatly appreciated.
Dec 06, 2011 02:16 PM|Diligent|LINK
The above code is all fine and can be used as is for passing array of byte array to Oracle SPs.
Here was the glitch...After beating around the bush for a long time, the DB programmer handling the type "g_param_raw_tab_type" found that the type was inadvertantly made to be an array of varchar instead of Raw. The type was changed to Raw
and BINGO!!!! the call went through.