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

  • Question

  • 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.

    Friday, December 2, 2011 11:36 PM

Answers

  • Hi dili,

     

    Thanks for your post.

    Per your description, this forum may not the best place to post questions.

    We'd suggest posting your question in one of the asp.net forums.

    Located at: Http://forums.asp.net/

    Hope this would be helpful.


    Thanks
    Liam Huang
    STO Application Team
    Server and Tools Online Operations Team
    Saturday, December 3, 2011 9:20 AM