How to Resolve – “ORA-24381: error(s) in array DML”

“Oops! Something went wrong” seems to be the fashionable thing to do when throwing errors these days. Not surprisingly, when issuing a query from PowerShell against Oracle, it kept producing this error at random

ORA-24381: error(s) in array DML

The error did not always happen. It only happened once in a while and it was always on this statement:

SELECT NVL(MAX(USERNAME),'NOT_THERE') as usr1
FROM DBA_USERS
WHERE USERNAME = 'AN_USER_ID'

The error gave me no clue about what the underlying issue is and more importantly I had no idea why it was so random. The complete error is below:

System.Management.Automation.MethodInvocationException: Exception calling “Fill” with “1” argument(s): “ORA-24381: error(s) in array DML”
—> Oracle.DataAccess.Client.OracleException: ORA-24381: error(s) in array DML
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at CallSite.Target(Closure , CallSite , Object , Object )
— End of inner exception stack trace —
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

As one can see from the error message, I do use the Oracle-provided ODP.NET OLEDB drivers and not the Microsoft provider for Oracle. No amount of frantic Googling helped although the issue seemed to have a theme. The theme was that this error happened to others when the data would not fit into the target usually when doing BULK INSERT. In my case, I was just doing a simple SELECT.

Solution: CAST to length

In any case, without beating around the bush, I figured that my problem had to do Oracle’s bad estimation of the string length (that led to the issue). So, I forced a CAST to limit the string to 30 characters and the issue went away!

SELECT NVL(MAX(USERNAME),
           CAST('NOT_THERE' AS VARCHAR2(30))
          ) as usr1
FROM DBA_USERS
WHERE USERNAME = 'AN_USER_ID'

In your case, the problem might be a similar data length issue and you might be able to resolve it too with a simple cast. I hope that this post helps you. Good luck!

Advertisement