none
WCF-Oracle DB Adapter Insert Issue with more number of records in a batch RRS feed

  • Question

  • All-

    When I try to send more than 120 records in a data file , I am getting the following error error(PLS-00123: program too large (Diana nodes) ---> Oracle.DataAccess.Client.OracleException)from Oracle. See below.I dont know it is related to Oracle DB or setting issue in BizTalk Adapter Cinfiguration.

    I have set InsertBatchSize to 65000

    A message sent to adapter "WCF-OracleDB" on send port "Send_Port" with URI "<OracleDB>" is suspended.
     Error details: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 314, column 988:
    PLS-00123: program too large (Diana nodes) ---> Oracle.DataAccess.Client.OracleException

    ORA-06550: line 314, column 988:
    PLS-00123: program too large (Diana nodes)    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
       at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
       at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)
       --- End of inner exception stack trace ---

    Server stack trace:
       at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)
     MessageId:  {48AFD66D-4F7A-4750-B2FB-C545DE184B91}
     InstanceID: {649248EA-084B-4631-BB93-10C9F5F9BFBF}

     

    Any thoughts will be appreciated.

    Thursday, April 15, 2010 3:39 PM

Answers

All replies

  • Hi,

    My thought is that too much data is send in a file as error message tells you. You have so send data in smaller messages to Oracle database. I do not beleive a setting in the adapter can prevent error from happening.

    Regards,

    Steef-Jan Wiggers
    MCTS BizTalk Server
    http://soa-thoughts.blogspot.com/
    If this answers your question please mark it accordingly


    BizTalk
    Thursday, April 15, 2010 10:52 PM
    Moderator
  • You should set the insertbatchsize property to a lower value - so that the adapter breaks the insert into more than 1 inserts.
    Friday, April 16, 2010 4:05 AM
  • Manas-

    Thank for your reply.

    I need this action should be in transactional.Means, some thing wrong in inserting last record, then whole transaction should be rolled back. I am sending just 14 KB file which has 120 records. If add couple of records to that, then it si giving this error.

    I have set the insertbatchsize property as 10000, but if I send just more than 120 records (around 130), then I am getting this error. Any seeting on Oracle side is required?

    Thanks

    Srinivas

    Friday, April 16, 2010 2:52 PM
  •  

      Its an issue with size of batch size in Oracle. Change into smaller batch and group them.

      Check the following: http://database.asia/documentation/oracle/database/11.1/appdev.111/b28370/limits.htm

      - SHR

     

    Friday, April 16, 2010 9:02 PM
  • Hi I have the same question now. I insert 5000 records  as a table into my Oracle DB ,can you tell me how to resolve this problem??
    Monday, June 3, 2013 5:53 AM