CODEWITHSUNDEEP
c#oracle-databaseoracle11gbulkinsertodp.net
andrecarlucci
andrecarlucci

Reputation: 6296

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

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()

Upvotes: 0

Views: 2333

Answers (2)

Alan Ahlberg
Alan Ahlberg

Reputation: 1

I had a similar issue. Merged items causing a ORA-12537 error. It was odd because it worked for a while, then did not. I eventually narrowed it down to null values in a CLOB field. It would work if only one value of the CLOB was null, but if there was more than one, it would consistently die. I resolved it by changing it to put a space into the CLOB (even using '' caused an error). Waste of a space, but at least the error is gone.

This seems like a bug to me. I am running Oracle 11.2.0.3.0 64-bit locally. Hope this helps...

Upvotes: 0

andrecarlucci
andrecarlucci

Reputation: 6296

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.

Upvotes: 2

Related Questions