none
Oracle Procedure for Biztalk 2006 R2 RRS feed

  • Question

  • Hi,

    I m working on biztalk 2006 r2 + oracle. i created one select oracle procedure (as below) which is inside package. but when i try to generate receive schema, i m not able to see the procedure. Is their any diffrent way to write procedure?
    Is anyone can give me the step bu step process for same.


    PROCEDURE sp_select_biztalk(OUT_refcursor OUT p_ref_appdetails)
    IS
    BEGIN
      OPEN OUT_refcursor FOR

      SELECT NAME,EMPNO, ADDRESS,PHONE,PROJECT
              FROM TBLBIZTALK WHERE FLAG='Y';


      EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END sp_select_biztalk;  

    Thanks

    Regards
    Girish Fuluskar

    Sunday, March 7, 2010 5:36 AM

All replies

  • Hi,

    I m working on biztalk 2006 r2 + oracle. i created one select oracle procedure (as below) which is inside package. but when i try to generate receive schema, i m not able to see the procedure. Is their any diffrent way to write procedure?
    Is anyone can give me the step bu step process for same.


    PROCEDURE sp_select_biztalk(OUT_refcursor OUT p_ref_appdetails)
    IS
    BEGIN
      OPEN OUT_refcursor FOR

      SELECT NAME,EMPNO, ADDRESS,PHONE,PROJECT
              FROM TBLBIZTALK WHERE FLAG='Y';


      EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END sp_select_biztalk;  

    Thanks

    Regards
    Girish Fuluskar

    • Merged by Ben Cline1Moderator Monday, March 8, 2010 10:13 PM Duplicate lead to 2 separate threads.
    Sunday, March 7, 2010 5:38 AM
  • make sure that you included the procedure in the Package definition as well as the package body. My guess is that you included it in the package body but not in the package definition.
    Monday, March 8, 2010 4:14 AM
  • Hi Manas,

    Thanks for ur reply. I din't get ur 1st reply on forum.

    But as ur msg received on my alert mail, i already created procedure (mentioned above) in pakage and in pakage defination.

    I am not able to see select or get procedure in receive port configuration or nor at the time of generating metadata for same.

    I am doing following steps.

    1) Created oracle adapter receive port and location (configured receive location with oracle connection string and able to see oracle database in managing event property. But not able to see get or select procedures)

    2) Created empty biztalk project.

    3) Generated metadata for receive schema using configured receive location. (Right click on project --> Add generated items --> Generate metadata...through this wizard i m using configured receive location).

    i am able to see insert, update procedures but unable to see get or select procedures. How do i do this?  

    Regards
    Girish Fuluskar
    Monday, March 8, 2010 7:11 AM
  • Hi Manas,

    I already created procedure (mentioned above) in pakage and in pakage defination.

    I am not able to see select or get procedure in receive port configuration or nor at the time of generating metadata for same.

    I am doing following steps.

    1) Created oracle adapter receive port and location (configured receive location with oracle connection string and able to see oracle database in managing event property. But not able to see get or select procedures)

    2) Created empty biztalk project.

    3) Generated metadata for receive schema using configured receive location. (Right click on project --> Add generated items --> Generate metadata...through this wizard i m using configured receive location).

    i am able to see insert, update procedures but unable to see get or select procedures. How do i do this?  


    Regards
    Girish Fuluskar
    Wednesday, March 10, 2010 3:49 AM
  • Hi,

    I m working on biztalk 2006 r2 + oracle. i created one select oracle procedure (as below) which is inside package and also define inside package defination but when i try to generate receive schema, i m not able to see the procedure. Is their any diffrent way to write procedure?
    Is anyone can give me the step by step process for same.


    PROCEDURE sp_select_biztalk(OUT_refcursor OUT p_ref_appdetails)
    IS
    BEGIN
      OPEN OUT_refcursor FOR

      SELECT NAME,EMPNO, ADDRESS,PHONE,PROJECT
              FROM TBLBIZTALK WHERE FLAG='Y';


      EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END sp_select_biztalk; 


     i already created procedure (mentioned above) in pakage and in pakage defination.

    I am not able to see select or get procedure in receive port configuration or nor at the time of generating metadata for same.

    I am doing following steps.

    1) Created oracle adapter receive port and location (configured receive location with oracle connection string and able to see oracle database in managing event property. But not able to see get or select procedures)

    2) Created empty biztalk project.

    3) Generated metadata for receive schema using configured receive location. (Right click on project --> Add generated items --> Generate metadata...through this wizard i m using configured receive location).

    i am able to see insert, update procedures but unable to see get or select procedures. How do i do this? 

    Regards
    Girish Fuluskar

    Sunday, March 14, 2010 5:16 AM
  • Taking a step back - looks like you are not using BizTalk adapter pack adapter. AFAIK, Enterprise adapter for Oracle DB does not work with Ref Cursors.

    Wednesday, March 17, 2010 6:48 AM
  • Hi Manas,

    Thanks for your reply.

    I'll uninstall my enterprise adapter pack and reinstall biztalk adapter pack again. I'll try my same solution again.

    Thanks again.

    Regards

    Girish Fuluskar

     

    Friday, March 19, 2010 3:46 AM
  • Hi Manas,

    I installed following features in biztalk for Oracle Adapter.

    1) WCF LOB Adapter SDK SP2 x86

    2) Microsoft BizTalk Adapter Pack 2_0 Evaluation x86

    Then i created new oracle adapter in Biztalk Administration and configured it with WCF-OracleDB adapter.

    How can i use this adapter for calling oracle procedure?

    Kindly provide any sample example if you have.

    Thanks and Regards

    Girish Fuluskar

     

    Wednesday, March 24, 2010 2:56 AM
  • Hi Manas,

    I manage to use WCF-OracleDB Adapter but when i m consuming any oracle procrdure with ref cursor then it not showing me any columns in schema. I have written following proc.

    PROCEDURE sp_get_all_ppwo_ref_biztalk
      (   
       p_ref_projdtls OUT  t_cursor
      
      )
        IS
      BEGIN

                 OPEN p_ref_projdtls FOR


                      SELECT projhdr.pp_wo, projhdr.project_no, projhdr.proj_title, projhdr.proj_location, projhdr.exch_code,
                            projhdr.scheme_typ_code, projhdr.chargeable_flg                 
                      FROM wfp_proj_hdr_biztalk_temp projhdr
                      WHERE projhdr.isactive =1
                      AND projhdr.co_flag=0;                 
                      
      END sp_get_all_ppwo_ref_biztalk;

    Is this the right way to write procedure or any other way i have to write? Can we use XMLAUTO , XML DATA like sql adapter?

     

    Regards

    Girish Fuluskar

    Wednesday, March 31, 2010 8:14 AM
  • The issue here is that you are using a weakly typed ref cursor instead of a strongly typed one. Following is a simplistic example of a strongly typed ref cursor:

    in package definition:

    type refcursor2 is ref cursor RETURN emp%ROWTYPE;

    (can also use a record type %TYPE instead of ROWTYPE)

    procedure refcursorout(name1 in VARCHAR2, refcursorout out refcursor2);

    in package body:

    procedure refcursorout(name1 in VARCHAR2, refcursorout out refcursor2)
    begin
    open refcursorout for select * from emp;
    return refcursor;
    end refcursorouttest;

     

    Wednesday, March 31, 2010 10:10 AM
  • Hi Manas,

    Thanks for ur reply,

    i tried in the way u said and it worked but after generating metadata from adapter i m getting 2 schemas, one with procedure name (OracleDBBinding_BIZDTLS.xsd) and another with package name (OracleDBBinding_IMPACT.xsd).

    Which one to consider? and for mapping is i have to consider the response section of the schema to map it?

    I deploy the solution to generate data from procedure to destination but it thrown following error. I simply created receive port(configured with oracle) and send port (file adapter) and used pass thru pipeline for both.

    Where i am doing wrong? Kindly suggest me for same, i think i m close to the solution.

    ERROR:

    The Messaging Engine failed to add a receive location "OraReceiveLocation" with URL "oracledb://ngisdev/" to the adapter "OracleDB_LOB". Reason: "Microsoft.ServiceModel.Channels.Common.MetadataException: Metadata resolution failed for OperationId: "http://Microsoft.LobServices.OracleDB/2007/03/POLLINGSTMT". ---> Oracle.DataAccess.Client.OracleException ORA-00911: invalid character    at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheckForOra1000Error)
       at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheckForOra1000Error)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(CommandBehavior behavior)
       at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteReader(OracleCommonConnectionWrapper connection, String commandText, IEnumerable`1 parameters, CommandBehavior commandBehavior, IOracleAdapterCommonBindingProperties properties, OracleCommonExecutionHelper executionHelper)
       --- End of inner exception stack trace ---
       at Microsoft.Adapters.OracleDB.OracleCommonMetadataResolverHandler.ResolveOperationMetadata(String operationId, TimeSpan timeout, TypeMetadataCollection& extraTypeMetadataResolved)
       at Microsoft.ServiceModel.Channels.Common.Design.MetadataCache.GetOperationMetadata(String uniqueId, Guid clientId, TimeSpan timeout)
       at Microsoft.ServiceModel.Channels.Common.MetadataLookup.GetOperationDefinitionFromInputMessageAction(String action, TimeSpan timeout)
       at Microsoft.Adapters.OracleDB.OracleDBInboundContract..ctor(OracleDBConnection connection, IOracleCommonUDTHelper oracleUdtHelper, MetadataLookup metadataLookup)
       at Microsoft.Adapters.OracleDB.OracleDBConnection.Microsoft.ServiceModel.Channels.Common.IConnection.BuildHandler[TConnectionHandler](MetadataLookup metadataDictionary)
       at Microsoft.ServiceModel.Channels.Common.Design.ConnectionPool.GetConnectionHandler[TConnectionHandler](Guid clientId, TimeSpan timeout, MetadataLookup metadataLookup, String& connectionId)
       at Microsoft.ServiceModel.Channels.Common.Channels.AdapterChannelListener`1.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Dispatcher.ChannelDispatcher.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.ServiceHostBase.OnOpen(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open(TimeSpan timeout)
       at System.ServiceModel.Channels.CommunicationObject.Open()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint.Enable()
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiveEndpoint..ctor(BizTalkEndpointContext endpointContext, IBTTransportProxy transportProxy, ControlledTermination control)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfReceiver`2.AddReceiveEndpoint(String url, IPropertyBag adapterConfig, IPropertyBag bizTalkConfig)".

     

     

    Regards

    Girish Fuluskar

    Thursday, April 1, 2010 7:56 AM
  • Which version of adapter pack are you using? I did not realize that you wanted to use this in a polling scenario. Polling of stored procedures is supported in BizTalk adapter pack 2.0 and is described here: http://msdn.microsoft.com/en-us/library/dd788064(BTS.10).aspx
    Thursday, April 1, 2010 8:26 AM
  • Hi Manas,

    I installed following features in biztalk for Oracle Adapter.

    1) WCF LOB Adapter SDK SP2 x86

    2) Microsoft BizTalk Adapter Pack 2_0 Evaluation x86

    I am using WCF-OracleDB adapter for same.

    As mentioned in MSDN have given following request in polling statement property.

    <BIZDTLS xmlns="http://Microsoft.LobServices.OracleDB/2007/03/WORKFLOW/Package/IMPACT/BIZDTLS">
      <refcursorout>OPEN ? FOR SELECT * FROM wfp_proj_hdr_biztalk_temp</refcursorout>
    </BIZDTLS>

    I am still getting 2 schemas.

    Regards

    Girish Fuluskar

    Thursday, April 1, 2010 9:34 AM
  • What action did you provide in PollingAction?
    Thursday, April 1, 2010 9:42 AM
  • i given http://Microsoft.LobServices.OracleDB/2007/03/WORKFLOW/Package/IMPACT/BIZDTLS  (IMPACT is the name of my package and BIZDTLS is procedure name).

    I did following steps:

    1) Created Receive port and Location and configured it with WCF-OracleDB Adapter (Which i Created in Adapters)

    2) On Configure click, in Endpoint Address i configures as follows:

                        a)Service Name : OraBiz (This which i created in System DNS data source in Administrative Tools)

                       b) DataSource Name : NGISDEV (This is my oracle server)

      In Binding

                       a) PollingDataAvailableStatement: SELECT 1 FROM wfp_proj_hdr_biztalk_temp

                       b) Polling Action: http://Microsoft.LobServices.OracleDB/2007/03/WORKFLOW/Package/IMPACT/BIZDTLS

                       c) PollWhileDataFound: True

    After receive location configuration i created one blank biztalk solution and

                          1) Right click on solution--> Add generated Items --> Add Adapter Metadata, in wizard window i select the WCF-OracleDB adapter and select the configured receive location.

                          2) In Consume Adapter service Wizard Screen ,

                               a) Binding --> OracleDBBinding

                               b) Configure Click ---> Client Credential Type -->UserName (i given Username and password)

                                    In URI Property Tab --> Service Name-->OraBiz (This which i created in System DNS data source in Administrative Tools)

                                                                        DataSourceName-->NGISDEV (My Oracle Server Name) 

    Then i click on connect and select the Impact Package and from that i m selecting BizDtls (My Oracle Procedure).

    Then it generate 2 schemas one with procedure name (OracleDBBinding_BIZDTLS.xsd) and another with package name (OracleDBBinding_IMPACT.xsd).

    Regards

    Girish Fuluskar

    Thursday, April 1, 2010 10:21 AM
  • Hi Manas,

    Is my Installation and Oracle Adapter process is right?

    The steps which i m following r right?

     

    Regards

    Girish Fuluskar

    Monday, April 5, 2010 2:05 AM
  • Hi,

    BizTalk Adapter Pack Samples can be found at BizTalk Development Center.

    Regards,

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


    BizTalk
    Wednesday, August 25, 2010 7:55 AM
    Moderator