No indication of index for culprit in case of error with array bind and stored procedure

[v-pi]

Hello,

We use stored procedures a lot in our code base and I was trying to identify the culprit of an exception when I use array binding with a stored procedure. But unlike for direct SQL text commands, stored procedures do not seem to return the OracleError with ArrayBindIndex that would allow me to find which of my elements caused an exception.

Repro case below : PL/SQL :

CREATE TABLE TEST_1
(
COL_1 NUMBER,
CONSTRAINT TEST_1_PK PRIMARY KEY (COL_1)
);

INSERT INTO TEST_1 (COL_1) VALUES (5);
COMMIT;

CREATE OR REPLACE PACKAGE TEST_PACKAGE AS
PROCEDURE SAVE(pValue IN NUMBER);
END TEST_PACKAGE;

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE AS
PROCEDURE SAVE(pValue IN NUMBER)
IS
BEGIN
INSERT INTO TEST_1 (COL_1) VALUES (pValue);
END SAVE;
END TEST_PACKAGE;

C# :

using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace OracleArrayBindTest
{
    internal class Program
    {
        private const string _connectionString = "XXXXXXXXXXXXX";

        static void Main(string[] args)
        {
            var values = Enumerable.Range(1, 10).ToArray();

            Console.WriteLine("Test using direct SQL");
            try
            {
                Execute(values, CommandType.Text, "INSERT INTO TEST_1 (COL_1) VALUES (:pValue)");
            }
            catch (OracleException ex)
            {
                // here we get :
                // ORA-00001: unique constraint (STA$OWNER.TEST_1_PK) violated at array bind index 4
                // this is what I need but for Stored Procedures
                LogError(ex);
            }

            Console.WriteLine();
            Console.WriteLine("Test using stored procedure");
            try
            {
                Execute(values, CommandType.StoredProcedure, "TEST_PACKAGE.SAVE");
            }
            catch (OracleException ex)
            {
                // here there is no mention of array bind index 4 :sadpanda:
                LogError(ex);
            }
        }

        private static void LogError(OracleException ex)
        {
            Console.WriteLine(ex.Message);
            foreach (OracleError oracleError in ex.Errors)
            {
                Console.WriteLine($"Exception : {oracleError.Message} at array bind index {oracleError.ArrayBindIndex}");
            }
        }

        private static void Execute(int[] values, CommandType commandType, string commandText)
        {
            using var connection = new OracleConnection(_connectionString);
            connection.Open();

            using var command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            command.ArrayBindCount = values.Length;

            OracleParameter inParameter = command.CreateParameter();
           inParameter.ParameterName = "pValue";
            inParameter.OracleDbType = OracleDbType.Int32;
            inParameter.Direction = ParameterDirection.Input;
            inParameter.Value = values;
            inParameter.Size = values.Length;

            command.Parameters.Add(inParameter);

            command.ExecuteNonQuery();
        }
    }
}

Is this the intended behavior ? Or is there some other way to find which element caused the issue ? If this is not intended, is a fix possible in a future version ?

Thanks and regards, Valentin

[alexkeh]

Since the insert operation occurs using the SQL in the PL/SQL context, you would need to use PL/SQL to perform the error handling if you are expecting an error collection to be returned, However, I don't know if PL/SQL has this capability. You can try asking the question on the Oracle SQL and PL/SQL discussion forum.

If it's not available, then this would be a new feature request for the PL/SQL team to implement.

If available, you could pass this PL/SQL error collection to ODP.NET.