none
Syntax for PostPollStatement for polling Oracle Package procedure in BizTalk WCF-OracleDB adapter RRS feed

  • Question

  • Please provide the syntax for PostPollStatement with IN parameters for after polling Oracle Package procedure in BizTalk WCF-OracleDB adapter.

    My Oracle package procedure looks like this:

    Procedure SetBizTalkFeedBatchCompleted (SourceFeedIn IN VARCHAR2,SourceSystemIn IN Varchar2,BatchNumberIn IN Integer Default null,rtnBatchNumber OUT Integer,rtnStatus Out Varchar2)

    Appreciate your quick response.

    Monday, July 21, 2014 6:09 AM

Answers

All replies

  • Please provide the syntax for PostPollStatement with IN parameters for after polling Oracle Package procedure in BizTalk WCF-OracleDB adapter.

    Hi,

    Please take a look at the article below.

    Polling Oracle Database Using Stored Procedures, Functions, or Packaged Procedures and Functions

    Hope it helps.

    Regards



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 22, 2014 2:21 AM
  • I need to configure the below package procedure in PostPollStatement

    Procedure SetBizTalkFeedBatchCompleted (SourceFeedIn IN VARCHAR2,SourceSystemIn IN Varchar2,BatchNumberIn IN Integer Default null,rtnBatchNumber OUT Integer,rtnStatus Out Varchar2)

    I configure as below:

    BEGIN
    ESTABIZTALKDATAFEED.SETBIZTALKFEEDBATCHCOMPLETED('SourceFeedIn','SourceSystemIn');
    END;

    I got the below error:

    The adapter "WCF-OracleDB" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 1:

    PLS-00306: wrong number or types of arguments in call to 'SETBIZTALKFEEDBATCHCOMPLETED'

    ORA-06550: line 2, column 1:

    PL/SQL: Statement ignored ---> Oracle.DataAccess.Client.OracleException: ORA-06550: line 2, column 1:

    PLS-00306: wrong number or types of arguments in call to 'SETBIZTALKFEEDBATCHCOMPLETED'

    ORA-06550: line 2, column 1:

    PL/SQL: Statement ignored

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

       at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)

       at Microsoft.Adapters.OracleCommon.ProcedureResponseBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)

       at System.ServiceModel.Channels.BodyWriterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)

       at Microsoft.Adapters.AdapterUtilities.AdapterMessage.OnWriteBodyContents(XmlDictionaryWriter writer)

       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterInputChannel.WrapperMessage.OnWriteBodyContents(XmlDictionaryWriter writer)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessageStream(Message wcfMessage, IAdapterConfigInboundMessageMarshalling config, TLConfig tlConfig, RLConfig rlConfig)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkMessage(IBaseMessageFactory messageFactory, IAdapterConfigInboundMessageMarshalling marshallingConfig, Message wcfMessage, TLConfig tlConfig, RLConfig rlConfig)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfMarshaller.CreateBizTalkSubmitMessage(IBaseMessageFactory factory, String inboundTransportLocation, String inboundTransportType, RLConfig config, Message wcfMessage, String ssoToken)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.BizTalkSubmitBase..ctor(Message message, BizTalkEndpointContext endpointContext, ControlledTermination control, AsyncCallback realCallback, String ssoToken)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.BizTalkSubmit..ctor(Message message, AsyncCallback callback, Object state, BizTalkEndpointContext endpointContext, String ssoToken, ControlledTermination control)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.BizTalkOperation.Create(Message message, AsyncCallback callback, Object state, String ssoToken, Boolean bizTalkOneWay, BizTalkEndpointContext endpointContext, ControlledTermination control)

       at Microsoft.BizTalk.Adapter.Wcf.Runtime.BizTalkServiceInstance.BeginOperation(Message message, AsyncCallback callback, Object state, Boolean bizTalkOneWay)".

    Tuesday, July 22, 2014 6:34 AM
  • You cannot use dynamic parameters in either the Polling or Post Polling statement.  So, what I believe you're asking is not possible.

    Your only realistic option is a Stored Procedure.

    Tuesday, July 22, 2014 2:00 PM
  • I dont see any dynamic parameters.

    These are not dynamic parameters. These are just IN parameters which are static to the oracle package procedure.

    My issue is here, I cannot provide IN parameters.The adpater is constantly throwing errors when I provide IN parameters for a package procedure in Post poll statement.

    Wednesday, July 23, 2014 1:25 PM
  • Are you sure it's the IN property that's the problem?

    Either way, you best option is to not use the Post Poll option and complete the entire transaction in the Polling Statement Stored Procedure.  I have never used the Post Poll statements with either Oracle or SQL Server.

    Wednesday, July 23, 2014 2:17 PM
  • The below is the Oracle package procedure to be called in post poll statement of WCF-OracleDB adapter. It has got 3 IN parameters.

    Procedure SetBizTalkFeedBatchCompleted (SourceFeedIn IN VARCHAR2,SourceSystemIn IN Varchar2,BatchNumberIn IN Integer Default null,rtnBatchNumber OUT Integer,rtnStatus Out Varchar2).

    Here is what I have configured:

    BEGIN
    ESTABIZTALKDATAFEED.SETBIZTALKFEEDBATCHCOMPLETED(SOURCEFEEDIN,SOURCESYSTEMIN,BATCHNUMBERIN);
    END;

    Error received is:

    The adapter "WCF-OracleDB" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 1:

    PLS-00306: wrong number or types of arguments in call to 'SETBIZTALKFEEDBATCHCOMPLETED'

    ORA-06550: line 2, column 1:

    PL/SQL: Statement ignored ---> Oracle.DataAccess.Client.OracleException: ORA-06550: line 2, column 1:

    PLS-00306: wrong number or types of arguments in call to 'SETBIZTALKFEEDBATCHCOMPLETED'

    ORA-06550: line 2, column 1:

    PL/SQL: Statement ignored

    Thursday, July 24, 2014 4:17 AM
  • Issue has been resolved.

    • Marked as answer by Angie Xu Thursday, July 24, 2014 7:10 AM
    Thursday, July 24, 2014 7:07 AM