ORA-12537 when invoking bulk Merge - Oracle.ManagedDataAccess.Client

Question

I'm trying to insert/update many records using ArrayBindCount.

It works just fine for 1 and 2 records, but if I try to insert 3 or more I get a ORA-12537 exception.

I'm using Oracle.ManagedDataAccess.dll version 4.121.1.0. I tried on OracleXE 11g and Oracle Standard 11g too.

This is the sql I'm using:

MERGE INTO tb_medidor_ene t1 
USING (select :pId cd_medidor, :pDate dt_hr_instante, :pValor vl_eneat_del from dual) t2 ON (t1.cd_medidor = t2.cd_medidor and t1.dt_hr_instante = t2.dt_hr_instante) 
WHEN MATCHED THEN update set t1.vl_eneat_del = t2.vl_eneat_del, dt_hr_insercao = :pInsertDate 
WHEN NOT MATCHED THEN INSERT (t1.cd_medidor, t1.dt_hr_insercao, t1.dt_hr_instante, t1.vl_eneat_del) 
VALUES (t2.cd_medidor, :pInsertDate, t2.dt_hr_instante, t2.vl_eneat_del)

And this (simplified) code:

    int num = 3;
    int index;
    var ids = new int[num];
    var insertDates = new DateTime[num];
    var dates = new DateTime[num];
    var values = new double[num];

    for (index = 0; index < num; index++) {
        ids[index] = 1;
        insertDates[index] = DateTime.Now;
        dates[index] = DateTime.Today.AddMinutes(index * 5);
        values[index] = index;
    }
    using (var conn = new OracleConnection(Program.ConnString)) {
        conn.Open();
        using (var command = conn.CreateCommand()) {
            command.ArrayBindCount = num;
            command.CommandText = sql;
            command.BindByName = true;
            command.Parameters.Add(new OracleParameter("pId", ids));
            command.Parameters.Add(new OracleParameter("pInsertDate", insertDates));
            command.Parameters.Add(new OracleParameter("pDate", dates));
            command.Parameters.Add(new OracleParameter("pValor", values));
            command.ExecuteNonQuery();
        }
    }

"An unhandled exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Oracle.ManagedDataAccess.dll"

{"ORA-12537: Biblioteca de Rede: Fim do arquivo"}

Stack trace:

at Oracle.ManagedDataAccess.Client.OracleException.HandleError(OracleTraceLevel level, OracleTraceTag tag, Exception ex) at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int32 initialLOBFetchSize, Int64[] scnFromExecution, Boolean& bAllPureInputBinds, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Boolean bDefineDone) at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int32 lobPrefetchSize, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() at OraclePlayground.Program.Main(String[] args) in c:\dev\way2\DataIn\OraclePlayground\OraclePlayground\Program.cs:line 114 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

I am having the same problem but my limit is 4. From 5 merge statements on I get the same error. Were you able to fix the problem? — mosu
Thanks andrecarlucci but that is a workaround I would prefer not to use. I used a batch command instead. — mosu

andrecarlucci andrecarlucci · Accepted Answer · 2014-06-25T19:50:33

For someone with the same problem:

I ended up creating a stored procedure that does nothing but execute the same SQL I was trying to execute directly.

You can then bulk call the procedure and it works fine.

So far, this is the only way to do it until the Oracle guys fix the issue.

ORA-12537 when invoking bulk Merge - Oracle.ManagedDataAccess.Client

Stack trace:

2 Answers