"Program too large" Exception RRS feed

  • Question

  • Hi all,


    Currently the interface is trying to do insert/update to multiple tables in a Oracle database. We do not know whether the data is insert or update, therefore we attempt to update then IF SQL%NOTFOUND we will do an insert operation. All the data originate from iDocs. One segment in the iDoc is one row in the table. The business requirement is that if insert/update of the data fails at any point, we will have to rollback. To rollback, we have decided to put all the statements in a bulk dynamic sql into a biztalk message and send it to the oracle database.


    This methods works fine but when tested with an idoc with two thousands and above segments in an idoc, the error below appears:


    Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 13784, column 7:
    PLS-00123: program too large ---> Oracle.DataAccess.Client.OracleException ORA-06550: line 13784, column 7:
    PLS-00123: program too large    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
       at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
       at Microsoft.Adapters.OracleDB.OracleDBXmlParser.ExecuteSqlExecute(AdapterDataTable parsedMessage)
       --- End of inner exception stack trace ---

    Server stack trace:
       at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)


    I have done some troubleshooting, tested with 1500 segments in an idoc, we are still able to insert/update without any error, when tested with 1800 segments, the same error appears. File size for 1500 segments is around 800kb and for 1800 segments, it is 900++kb.


    If there a way to extend the limit for the accepting more sql statements? Or is there another way to do database update in one transaction for multiple insert/updates?

    Wednesday, September 3, 2008 7:18 AM