none
oracle sp in a loop RRS feed

  • Question

  • Hi all,

    I'm executing a SP in Oracle via adapter pack with BTS2006. I have a loop to call the SP multiple times. If I call the SP 1 time everything works fine. But if I execute 2 SP's in loop the second SP call fails with following error. I even cleared all the data in SP but no luck.

    An error occurred while processing the message, refer to the details section for more information

    Message ID: {471894A5-CD2E-40DB-A9B6-F17388D96040}

    Instance ID: {2D4C706F-7BAD-486F-A5AE-0B11C0E1796C}

    Error Description: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06502: PL/SQL: numeric or value error

    ORA-06512: at line 1 ---> Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value error

    ORA-06512: at line 1 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.ExecuteNonQuery()

    at Microsoft.Adapters.OracleDB.OracleDBXmlParser.ExecuteSingleCommand(OracleCommand command, Boolean recordPresent, Boolean accumateRowAffected)

    at Microsoft.Adapters.OracleDB.OracleDBXmlParser.ExecutePackProcFunc(AdapterDataTable parsedMessage)

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

     

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Monday, March 23, 2009 11:30 PM

All replies

  • Hi,

    1. Are you seeing this behavior just for one particular SP or for all SPs that you are calling?
    2. Does the error happen in the first iteration itself, or does one iteration succeeds and then subsequent iterations fail?
    3. Can you describe what is it that you're doing in your SPs?

    Thanks,
    Manas
    Tuesday, March 24, 2009 4:03 AM
  • Hi,

    This is a very general error thrown from Oracle for any constraint violation, e.g. assigning a NULL value to a variable which can not be null.

    So I think this error is not related to executing the two SPs in loop. Try running second SP alone and see if same error occurs. Check whether you are passing values to SP that satisfy all the constraints given in SP definition.

    Also, if possible can you share the definition of SP and the values you are passing while calling SP?

    Thanks

    Gaurav

    Tuesday, March 24, 2009 4:08 AM
  • Hi guys,

    Thanks for the quick replies. Now first of all for Manas questions.
    1. I see this in all SP's, but actually they are all the same.
    2. The error occures after the first iteration.
    3. SP is just selecting a record according to primary key and does insert or update accordingly. nothing fancy.

    Actually I have removed all the code in the SP's and I see the same behaviour. If I run the same SP's one by one in Biztalk both of them work fine. The one thing that I may try is to set the input parms nullable.

    Hakan
    Tuesday, March 24, 2009 7:40 AM
  • Hi,

    1. Can you try setting the UseOracle ConnectionPool binding property to false and see if the problem still repros ? [this is only for testing - disabling ConnectionPool has -ve performance impact]
    2. Can you share the code for the SPs you're invoking?

    Thanks,
    Manas
    Tuesday, March 24, 2009 1:16 PM
  • Hi Manas,

    It isa dynamic port. I set the connection pooling to false as follows. It still gives the error.

    The ORchestration part is as :

    OracleRequest(BTS.OutboundTransportType)="WCF-Custom";
    OracleRequest(WCF.Action)="http://Microsoft.LobServices.OracleDB/2007/03/FLASH/Procedure/SP_BIZ_CUSTOMER";
    OracleRequest(WCF.BindingType)="oracleDBBinding";
    OracleRequest(WCF.BindingConfiguration)="<binding name=\"OracleDBBinding\" closeTimeout=\"00:01:00\" openTimeout=\"00:01:00\" receiveTimeout=\"00:10:00\" sendTimeout=\"00:01:00\" enableBizTalkCompatibilityMode=\"true\" dataFetchSize=\"65536\" metadataPooling=\"true\" statementCachePurge=\"false\" statementCacheSize=\"10\" longDatatypeColumnSize=\"32767\" insertBatchSize=\"1\" pollingStatement=\"\" postPollStatement=\"\" pollingInterval=\"500\" pollingRetryCount=\"0\" useOracleConnectionPool=\"false\" minPoolSize=\"1\" maxPoolSize=\"100\" incrPoolSize=\"5\" decrPoolSize=\"1\" connectionLifetime=\"0\" transactionIsolationLevel=\"ReadCommitted\" enablePerformanceCounters=\"false\" acceptCredentialsInUri=\"false\" enableSafeTyping=\"false\" useSchemaInNameSpace=\"true\" />";
    OracleRequest(WCF.UserName)="***";
    OracleRequest(WCF.Password)="***";

    The SP Code is :

    CREATE OR REPLACE PROCEDURE FLASH.sp_biz_customer (
       i_musteri_kodu       IN       VARCHAR2 default null,
       i_unvan              IN       VARCHAR2 default null,
       i_adres1             IN       VARCHAR2 default null,
       i_adres2             IN       VARCHAR2 default null,
       i_vergi_daire        IN       VARCHAR2 default null,
       i_vergi_no           IN       VARCHAR2 default null,
       i_telefon_no         IN       VARCHAR2 default null,
       i_fax_no             IN       VARCHAR2 default null,
       i_email              IN       VARCHAR2 default null,
       o_musteri_adet       out      NUMBER
      
    )
    AS
     
      
          BEGIN
      
     o_musteri_adet :=0;
      

    END;
    /

    The 2 calls that I send to Oracle. First one passes, second one is suspended with the error.

    Message    1

    <ns0:SP_BIZ_CUSTOMER xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/FLASH/Procedure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><ns0:I_MUSTERI_KODU>73</ns0:I_MUSTERI_KODU><ns0:I_UNVAN>ZER MERKEZİ HİZMETLER VE TİCARET A changed by yahya akkoca</ns0:I_UNVAN><ns0:I_ADRES1>ÜNALAN MAH. AYAZMA CAD. ÇAMLICA İŞ MRK.B BLOK KAT:1</ns0:I_ADRES1><ns0:I_ADRES2>ÜSKÜDAR İSTANBUL</ns0:I_ADRES2><ns0:I_VERGI_DAIRE>BÜYÜK MÜKELLEF</ns0:I_VERGI_DAIRE><ns0:I_VERGI_NO>11900510271</ns0:I_VERGI_NO><ns0:I_TELEFON_NO>0 216 5560100</ns0:I_TELEFON_NO><ns0:I_FAX_NO>0 216 4540184</ns0:I_FAX_NO><ns0:I_EMAIL>aliyea@aygaz.com.tr</ns0:I_EMAIL></ns0:SP_BIZ_CUSTOMER>

    Message 2

    <ns0:SP_BIZ_CUSTOMER xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/FLASH/Procedure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><ns0:I_MUSTERI_KODU>253</ns0:I_MUSTERI_KODU><ns0:I_UNVAN>OPET PETROLCULUK A.Ş.</ns0:I_UNVAN><ns0:I_ADRES1>BULGURLU MAH.SARIGAZİ CAD.NO:47 LİBADİYE KAVŞAĞI</ns0:I_ADRES1><ns0:I_ADRES2>ÜSKÜDAR İSTANBUL</ns0:I_ADRES2><ns0:I_VERGI_DAIRE>BÜYÜK MÜKELLEF</ns0:I_VERGI_DAIRE><ns0:I_VERGI_NO>6440009046</ns0:I_VERGI_NO><ns0:I_TELEFON_NO>(216) 522 90 00</ns0:I_TELEFON_NO><ns0:I_FAX_NO>(216) 522 91 73</ns0:I_FAX_NO><ns0:I_EMAIL>info@opet.com.tr</ns0:I_EMAIL></ns0:SP_BIZ_CUSTOMER>

    Thanks
    Hakan


    Tuesday, March 24, 2009 9:53 PM
  •  following up offline with Hakan. Will update the thread once a solution is found.
    Thursday, March 26, 2009 6:46 PM
  • Did you manage to get a solution for this?

    Colin Meade (MCTS BizTalk Server)

    Tuesday, June 12, 2012 8:52 AM