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!

Advertisements

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

  1. Hi Jana,
    It would be very helpful if you shared your code, OS versions, Oracle versions (client and server) as without any of that there is no context to what you actually fixed.
    Thanks,
    Gaz.

    1. Hi Gaz,

      It is on Win 7 Enterprise running the Unmanaged provider client of Oracle ODP.net version 11.2 against Oracle server 11.2.0.4. The SQL and error are exactly as they are shown in the post. I wish I could show you the PowerShell code but it is part of a bigger library and I have to prune a lot of code to reproduce it here. Hope this helps. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s