4 Replies Latest reply: Apr 15, 2014 7:06 AM by 6e58adbf-e6d4-404e-90be-b03f06b06364 RSS

    Error when trying to access return value (associative array) of a stored function via C#

    6e58adbf-e6d4-404e-90be-b03f06b06364

      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