none
WCF-OracleDB Out RefCursor ReceiveLocation Configuration RRS feed

  • Question

  • Hi All,

    I am trying to poll data from Oracle Database which had a RefCursor as Out parameter

    I am able to get result when I execute below query at PL/SQL tool.But,  I am not sure how to configure this at BizTalk Receive Location?

    Can any one help me the configuration settings.

    The below query returned the data at Pl/SQL
    Begin
      Packge.GetEmpData(Emp_refCurs =>:Emp_refCurs);
    End

    Monday, January 17, 2011 3:55 PM

Answers

  • Thanks to all,

    I fixed the issue. It all about Polling statement.

    Manas: Procedure is IN OUT and it has been designed by Oracle team. However I am able to Poll data from bizTalk.

    I used WCF-OracleDB Adapter rather than WCF-Custom and it worked.

    • Marked as answer by rajwebjunky Thursday, January 27, 2011 9:04 AM
    Thursday, January 27, 2011 9:04 AM

All replies

  • Generate the schemas in Visual Studio, and you'll see that the out ref cursor is handled by the adaptere automatically.


    -- Please mark as answered if this answers your question.
    Tuesday, January 18, 2011 8:47 AM
  • HI Manas,

    It does generated the files but its generated with WCF-Custom related binding file. However I used WCF-OracleDB adapter to extract the schema.

    I did a configuration as mention here ( http://msdn.microsoft.com/en-us/library/dd788064(BTS.10).aspx).

    WCF-OracleDB One-Way Receive port

    PollingAction = http://Microsoft.LobServices.OracleDB/2007/03/ETLSCHEMA/PollingPackage/AUDIT_QUERY/Emp_refCurs

    PollingStatement :

    <Emp_refCurs xmlns="http://Microsoft.LobServices.OracleDB/2007/03/ETLSCHEMA/PollingPackage/AUDIT_QUERY/Emp_refCurs">
      <OUTRECS>OPEN ? FOR SELECT * FROM P_TEST</OUTRECS>
    </Emp_refCurs>

    I endup with below Error.. Can any one has clue ?

    The adapter "WCF-OracleDB" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException:
    Unexpected start node "Emp_refCurs " with namespace "http://Microsoft.LobServices.OracleDB/2007/03/ETLSCHEMA/PollingPackage/AUDIT_QUERY/Emp_refCurs" found.
       at Microsoft.Adapters.OracleCommon.StoredProcWriter.WriteStartElement2(String prefix, String localName, String ns)
       at Microsoft.Adapters.OracleCommon.CommonDictionaryWriter.WriteStartElement(String prefix, String localName, String ns)
       at System.Xml.XmlElement.WriteTo(XmlWriter w)
       at System.Xml.XmlDocument.WriteContentTo(XmlWriter xw)
       at System.Xml.XmlDocument.WriteTo(XmlWriter w)
       at Microsoft.Adapters.OracleCommon.PlSqlApisHandler.InvokeProcedure(ProcedureMetadata requestMessageMetadata, ProcedureMetadata responseMessageMetadata, Message message, XmlDocument inputDocument, MetadataLookup metadataLookup,
       String postPollStatement, MessageVersion responseMessageVersion, IOracleAdapterCommonBindingProperties properties, IOracleCommonUDTHelper oracleUdtHelper, OracleCommonConnectionWrapper oracleConnection, OracleCommonExecutionHelper
        executionHelper, ITypeIdHelper typeIdHelper)
       at Microsoft.Adapters.OracleDB.OracleDBInboundContract.Polling_TryReceive_SpFuncPackageSP(OracleCommonExecutionHelper executionHelper, Message& wcfMessage)
       at Microsoft.Adapters.OracleDB.OracleDBInboundContract.TryReceive(TimeSpan timeout, Message& message, IInboundReply& reply)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterInputChannel.TryReceive(TimeSpan timeout, Message& message)
       at System.ServiceModel.Dispatcher.InputChannelBinder.TryReceive(TimeSpan timeout, RequestContext& requestContext)
       at System.ServiceModel.Dispatcher.ErrorHandlingReceiver.TryReceive(TimeSpan timeout, RequestContext& requestContext)".

    Tuesday, January 18, 2011 11:55 AM
  • I think you have an extra /Emp-refCurs at the end of your namespace. It should be this:


    <Emp_refCurs xmlns=http://Microsoft.LobServices.OracleDB/2007/03/ETLSCHEMA/PollingPackage/AUDIT_QUERY>
      <OUTRECS>OPEN ? FOR SELECT * FROM P_TEST</OUTRECS>
    </Emp_refCurs>

    Can you confirm that this is correct as per your schema?


    -- Please mark as answered if this answers your question.
    Wednesday, January 19, 2011 11:35 AM
  • No Luck,

    Still same error.

    Wednesday, January 19, 2011 4:12 PM
  • Can you generate a sample XML using the schema that was generated by the wizard, and see how it should look like?
    -- Please mark as answered if this answers your question.
    Thursday, January 20, 2011 4:18 AM
  • BizTalk WCF-Oracle support IN OUT cursor ?

    My Oracle packge looks like below


    CREATE OR REPLACE PACKAGE BODY MESchema.TestPackage AS

    PROCEDURE get_EmpData (EmpOrders_Cursor IN OUT SYS_REFCURSOR) IS

    OPEN EmpOrders_Cursor FOR
     SELECT  * from EMP
    END get_EmpData;

    End TestPackage;

    Thursday, January 20, 2011 8:39 AM
  • I got the clue, Instead of writing OUTRECF I repalced with EmpOrders_Cursor  which is my input

    <Emp_refCurs xmlns=http://Microsoft.LobServices.OracleDB/2007/03/ETLSCHEMA/PollingPackage/AUDIT_QUERY>
      <
    EmpOrders_Cursor >OPEN ? FOR SELECT * FROM P_TEST</EmpOrders_Cursor >
    </Emp_refCurs>

     

    Now I end up with below error

    The adapter "WCF-Custom" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 3, column 31:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 3, column 17:
    PL/SQL: SQL Statement ignored ---> Oracle.DataAccess.Client.OracleException: ORA-06550: line 3, column 31:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 3, column 17:
    PL/SQL: SQL Statement ignored
       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.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)
       --- End of inner exception stack trace ---
       at Microsoft.ServiceModel.Channels.Common.Design.AdapterAsyncResult.End()
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterInputChannel.EndTryReceive(IAsyncResult result, Message& message)
       at System.ServiceModel.Dispatcher.InputChannelBinder.EndTryReceive(IAsyncResult result, RequestContext& requestContext)
       at System.ServiceModel.Dispatcher.ErrorHandlingReceiver.EndTryReceive(IAsyncResult result, RequestContext& requestContext)".

    Thursday, January 20, 2011 10:09 AM
  • HI Buddu,

    I Did not pool result before. But I suggest this approach.

    BizTalk WCF-Oracle Adapter as the process to query to get the result set.

    I suggest using Sql Server Query Notification service within WCF to identify Changed/New Records and then Query for the Data.

    Thanks,

    TenaliNaga

    Thursday, January 20, 2011 1:23 PM
  • 1. Why are you using an IN-OUT ref cursor? From your usage, you probable only need an OUT ref cursor.

    2. Fully qualify your table name ie. SCHEMA.TABLE name in both the input and the SP.


    -- Please mark as answered if this answers your question.
    Friday, January 21, 2011 6:39 AM
  • Thanks to all,

    I fixed the issue. It all about Polling statement.

    Manas: Procedure is IN OUT and it has been designed by Oracle team. However I am able to Poll data from bizTalk.

    I used WCF-OracleDB Adapter rather than WCF-Custom and it worked.

    • Marked as answer by rajwebjunky Thursday, January 27, 2011 9:04 AM
    Thursday, January 27, 2011 9:04 AM