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

  • 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

    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
    FOR i IN p_answers_tab.FIRST .. p_answers_tab.LAST LOOP
    INSERT INTO answers
    VALUES (
    p_answers_tab( i ),
    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.
    Monday, December 5, 2011 12:37 PM


  • User-1732324381 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 6, 2011 2:16 PM