locked
WCF Oracle Adapter in BizTalk Adapter Pack 2.0 RRS feed

  • Question

  • Hi

    I am trying to use Polling in the Oracle Adapter in BizTalk Adapter 2.0 with BizTalk 2006 R2. But I am getting the following warning. I have installed the 11 g client.

    The adapter "WCF-Custom" raised an error message. Details "Microsoft.ServiceModel.Channels.Common.ConnectionException: Data provider internal error(-3000) [System.String] ---> Oracle.DataAccess.Client.OracleException Data provider internal error(-3000) [System.String] 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, IntPtr opsErrCtx, Object src)

    at Oracle.DataAccess.Client.OracleConnection.Open()

    at Microsoft.Adapters.OracleCommon.OracleCommonConnectionWrapper..ctor(String connectionString, OracleCommonExecutionHelper executionHelper)

    at Microsoft.Adapters.OracleDB.OracleDBConnection.OpenConnection(OracleCommonExecutionHelper executionHelper)

    --- End of inner exception stack trace ---

    at Microsoft.Adapters.OracleDB.OracleDBConnection.OpenConnection(OracleCommonExecutionHelper executionHelper)

    at Microsoft.Adapters.OracleDB.OracleDBInboundContract.Polling_TryReceive_StatementPolling(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)".

    For more information, see Help and Support Center at


    Can you please help?

    Regards
    Rachana

    Thursday, June 18, 2009 7:50 PM

Answers

  • Hi

    Its working fine now. I reinstalled the ODP.net client and its working now. I had a question. Which version of Oracle.DataAccess assembly is actually required. When we install the Oracle client it puts the assembly version of Oracle.DataAccess 2.111.7.0 in the GAC. When I installed ODP.net after that the Oralce.DataAccess version 2.111.7.10 was installed. Does it require both the versions? The latest ODP.net from Oracle is the beta version. Will it work with a previous version of the ODP.net?
    • Marked as answer by Andrew_Zhu Friday, June 26, 2009 7:26 AM
    Tuesday, June 23, 2009 2:24 PM

All replies

  • Can you pls share the connection URI?

    Are there any other errors in the event veiwer?
    This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Thursday, June 18, 2009 8:01 PM
  • I used
    oracledb://DEV (where DEV is the name given in the tnsnames.ora ).  I provided the username and password to connect to the database.
    I used the same URI to connect via Visual Studio to create schemas. It connects without any errors. I am able to create schemas.

    There are no other errors in the event viewer.
    Thursday, June 18, 2009 8:32 PM
  • In order to debug this further, could you pls capture verbose traces and upload it to skydrive?

    Pls refer to the subsection "WCF Tracing Within the Adapter" in the tracing section of docs: http://msdn.microsoft.com/en-us/library/dd788183.aspx

    Use "Verbose" mode for Adapter instead of "Information".


    This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Thursday, June 18, 2009 9:10 PM
  • This is a problem related to ODP.NET configuration on your machine. These threads might be useful:

    http://forums.oracle.com/forums/thread.jspa?messageID=1625557&#1625557

    The following thread has a small code snippet that you can test on your machine:

    http://forums.oracle.com/forums/thread.jspa?threadID=376038

    Hope this helps,
    Manas

    Friday, June 19, 2009 2:14 AM
  • One more question. Is your system 32-bit or 64-bit?

    Thanks,
    Murali
    This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Friday, June 19, 2009 4:19 PM
  • Hi Murali

    I have uploaded the log file in SkyDrive. My machine is 32-bit.
    Friday, June 19, 2009 10:08 PM
  • Can you pls share the link to the skydrive location?

    Thanks,
    Murali


    This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Sunday, June 21, 2009 2:34 PM
  • Hi

    Its working fine now. I reinstalled the ODP.net client and its working now. I had a question. Which version of Oracle.DataAccess assembly is actually required. When we install the Oracle client it puts the assembly version of Oracle.DataAccess 2.111.7.0 in the GAC. When I installed ODP.net after that the Oralce.DataAccess version 2.111.7.10 was installed. Does it require both the versions? The latest ODP.net from Oracle is the beta version. Will it work with a previous version of the ODP.net?
    • Marked as answer by Andrew_Zhu Friday, June 26, 2009 7:26 AM
    Tuesday, June 23, 2009 2:24 PM
  • Hello Raci,

    Thank you for the update.

    Thanks,
    Murali
    This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
    Tuesday, June 23, 2009 5:15 PM
  • Rachana,

    I am trying to work with Oracle Client 11g and biz Talk adapter 2.0.
    After reading your posts, it looks like you are able to work with it successfully. Can you please list all the steps involved in the installation process?

    Regards
    Ashish
    Wednesday, June 24, 2009 10:23 PM
  • It seems the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

    Thank you!
    Microsoft Online Community Support
    Friday, June 26, 2009 7:28 AM
  • Hi

    Sorry for the very late reply. I apologize. The issue I faced was because the ODP.net was not installed correctly. I installed the Oracle Client 11g. That included ODP.net as well. The mistake I did was, I did not install ODP.net separately. It looks like installing Oracle Client is not enough, we have to install ODP.net as well.
    I downloaded both the softwares from Oracle site. It worked after the ODP.net was installed.
    Only thing is after the installation, I got two Oracle.DataAccess assemblies in the GAC : 2.111.7.0(from the Oracle client installation) ,2.111.7.10(ODP.net installation). Please do not remove the assemblies. BizTalk looks for 2.111.7.0 version.

    Let me know if you need any specific details
    Tuesday, July 14, 2009 4:38 AM
  • Hi there,

    It seems now the time has passed and the oracle client is not in beta version anymore, the problem I am facing now is that in the GAC I am faced with the Oracle.DataAccess assemblies  2.111.7.20. And of course as stated above the WCF-OracleDB needs the 2.111.7.0 version.

    Any hint on how I got this dll with the right version? I have access to metalink.orcale.com but I have no idea on what to look for ! what is the patch number I should install?

    Sadly the documentation of the adapter related to Oracle is OUT of DATE ! Since I passed all day trying to make it work with no success.

    Any help would be greatly appreciated

    Regards,
    Stefan
    Wednesday, October 21, 2009 4:11 PM
  • The patch that works for me is 6890831 on top of 11gR1 client.
    Thursday, October 22, 2009 6:21 AM
  • OK, I finally found out what was wrong with my installation. And I wanted to share it, because it might help someone else :

    The order of the installation is important ! otherwise you might get the error of the assembly not found with the version   2.111.7.20

    Now What I need to do is call an Oracle function to dequeue what is in Oracle Advanced Queuing. Any help on this?
    Because the adapter seems to accept only SELECT !, and a function in Oracle is not authorized to DEQUEUE becaue it updates data in tables!

    Could the calling function return user defined datatypes? how? what would be the syntax?

    Best Regards,
    Stefan
    http://www.itsconsulting.fr/


    • Proposed as answer by Mr. BizTalk Tuesday, September 25, 2012 2:40 PM
    Thursday, October 22, 2009 2:31 PM
  • What I meant is, I cannot put in the adapter for the polling statement SELECT myFunction FROM dual;

    Because myFunction would modify data, and the syntax in the SELECT myFunction is not authorized
    Thursday, October 22, 2009 2:33 PM
  • OK, I finally found out what was wrong with my installation. And I wanted to share it, because it might help someone else :

    The order of the installation is important ! otherwise you might get the error of the assembly not found with the version   2.111.7.20

    Now What I need to do is call an Oracle function to dequeue what is in Oracle Advanced Queuing. Any help on this?
    Because the adapter seems to accept only SELECT !, and a function in Oracle is not authorized to DEQUEUE becaue it updates data in tables!

    Could the calling function return user defined datatypes? how? what would be the syntax?

    Best Regards,
    Stefan
    http://www.itsconsulting.fr/


    I followed these instructions to the letter and I am still getting the;

    System.IO.FileNotFoundException: Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0

    error message. When I run the test Orch.

    Thursday, October 6, 2011 4:44 PM
  • Here is how you get it working, assuming Oracle 9i server or higher.

     

    Get the latest 32bit ODAC, the zip I think is ODTwithODAC112021.zip or something close depending on if they update the version.

     

    Get the latest 64 bit ODAC, it is ODAC112021Xcopy_x64.zip or something similar.

     

    Install the 32bit ODAC with the universal installer, I choose to install into C:\Oracle. Remember the Oracle home name. Let's call it "OraHome1" for this thread.

     

    Then, once 32bit is installed unzip the 64bit contents somewhere and open a command prompt as an admin. Navigate to the unzip path for the 64bit install in the prompt. Then run this command:

    install.bat all c:\oracle OraHome1

     

    Then add environmental variables:

    TNS_ADMIN : C:\Oracle32\product\11.2.0\client_1\Network\Admin

    ORACLE_HOME : C:\Oracle32\product\11.2.0\client_1

     

     

    Then restart the server. 

     

    That will work, I am pretty damn positive at this point.

    Of course, if you're not using TNS, ignore the TNS_ADMIN part. 
    • Edited by Bon Franklin Thursday, October 6, 2011 4:52 PM
    • Proposed as answer by Bon Franklin Thursday, October 6, 2011 5:42 PM
    Thursday, October 6, 2011 4:51 PM
  • Here is how you get it working, assuming Oracle 9i server or higher.

     

    Get the latest 32bit ODAC, the zip I think is ODTwithODAC112021.zip or something close depending on if they update the version.

     

    Get the latest 64 bit ODAC, it is ODAC112021Xcopy_x64.zip or something similar.

     

    Install the 32bit ODAC with the universal installer, I choose to install into C:\Oracle. Remember the Oracle home name. Let's call it "OraHome1" for this thread.

     

    Then, once 32bit is installed unzip the 64bit contents somewhere and open a command prompt as an admin. Navigate to the unzip path for the 64bit install in the prompt. Then run this command:

    install.bat all c:\oracle OraHome1

     

    Then add environmental variables:

    TNS_ADMIN : C:\Oracle32\product\11.2.0\client_1\Network\Admin

    ORACLE_HOME : C:\Oracle32\product\11.2.0\client_1

     

     

    Then restart the server. 

     

    That will work, I am pretty damn positive at this point.

    Of course, if you're not using TNS, ignore the TNS_ADMIN part. 

    Thnx, I got it working except for one thing.

    Every example uses that SCOTT (Oracle) Schema which we do not and cannot use. I loaded the scripts into our default  (Oracle) schema. But now i get this error when running the Orch:

    "SCOTT"."ACCOUNT_PKG" is a non-existent Package or Synonym.

    Where in this project can I change the  (Oracle) schema SCOTT to our  (Oracle) schema?

     

     

     

     

    Thursday, October 6, 2011 5:37 PM
  • Well, if you're using the Biztalk adapters then you change the server and schema you're connecting to in the send port (or receive location).

    In the BizTalk admin console go to the properties of the port (Your Application -> Send Ports -> Right click/properties)

     

    In the new window click the Configure button. The address is on the first screen and is for the server, the Credentials tab is where you select which schema you log into (SCOTT, whatever).

     

     

    Thursday, October 6, 2011 5:41 PM
  • Well, if you're using the Biztalk adapters then you change the server and schema you're connecting to in the send port (or receive location).

    In the BizTalk admin console go to the properties of the port (Your Application -> Send Ports -> Right click/properties)

     

    In the new window click the Configure button. The address is on the first screen and is for the server, the Credentials tab is where you select which schema you log into (SCOTT, whatever).

     

     

    That is exactly what I did. In "Oracle Transport Properties" | Click the Credentials tab | Select the "Do not user Single Sign-On radio button and enter credentials for the appropriate (Oracle) Schema. I did all of that. I still get:

    "SCOTT"."ACCOUNT_PKG" is a non-existent Package or Synonym.

    The ACCOUNT_PKG is there only it is: MySchema.ACCOUNT_PKG and I am logged in as MySchema. I'm not getting what the issue is unless there is somewhere else where the schema from the example is hardcoded.

    Thursday, October 6, 2011 5:55 PM
  • Weird, try restarting the host instance under which that port is handled. 
    Thursday, October 6, 2011 5:58 PM
  • Did that. Multiple times. The namespace shouldn't have anything to do with the Oracle schema used should it?
    • Edited by MSDEVTECH Thursday, October 6, 2011 6:01 PM
    Thursday, October 6, 2011 6:00 PM
  • Hrm, maybe the schema and SOAP Action header?

     

    Does the namespace for the schema you're using reference SCOTT instead of your schema? 

    Thursday, October 6, 2011 6:02 PM
  • Hrm, maybe the schema and SOAP Action header?

     

    Does the namespace for the schema you're using reference SCOTT instead of your schema? 

    Yes:

     

    http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACTIVITY

    Thursday, October 6, 2011 6:23 PM
  • Yay, well there is your problem.

     

    The adapter just connects you to a schema, but any instructions you send to it targeted at another schema will execute against the target. This is so you can access resources in other schemas which are available via a DB Link. 

     

    Change "SCOTT" in your schemas and in the send port action header to match your schema and that should fix it.

    Thursday, October 6, 2011 6:26 PM
  • Yay, well there is your problem.

     

    The adapter just connects you to a schema, but any instructions you send to it targeted at another schema will execute against the target. This is so you can access resources in other schemas which are available via a DB Link. 

     

    Change "SCOTT" in your schemas and in the send port action header to match your schema and that should fix it.

    I just did that, (I did a simple find and replace) now I a I am getting errors in the receive pipeline where it is looking for the old BT schema:

     

    A message received by adapter "FILE" on receive location "Receive Location1" with URI "C:\TestLocation\MessageIn\*.xml" is suspended. 

     Error details: There was a failure executing the receive pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Receive Port: "MessageIn" URI: "C:\TestLocation\MessageIn\*.xml" Reason: Finding the document specification by message type "http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG#GET_ACTIVITY" failed. Verify the schema deployed properly.   

    Thursday, October 6, 2011 6:53 PM
  • The orchestration you're using is subscribing to the schema before the change. Rebuild the orchestration with reference to your updated schema, redeploy, restart host instances, and the subscription should be updated.

     

    Or, if you're using no orchestration, just check the filter properties of the send port and update the MessageType to match.

    Thursday, October 6, 2011 6:55 PM
  • I think at this point the simpler solution, and one you would be doing in normal situations anyway, is to just generate the schema for the new target yourself.

     

    In Visual Studio Solution Explorer, right click the project and Add -> Generated Items. Then select Consume Adapter Service. If you do not have this you should install the LOB Adapter pack (comes with installation media or purchase separately) and the WCF LOB SDK ( http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22067 ) 

     

    Next select the oracleDBBinding and click the Configure button. Select Username for Client credential type and enter your schema and password. 

     

    Under the URI Properties tab set the server name to DataSourceName.

     

    Under Binding Properties tab set EnableBizTalkCompatibilityMode = true. Hit OK.

     

    Then navigate to the package and procedure and click OK. Use this schema as your message instead of the one you were trying to change, and update the namespaces in the places I already mentioned in prior responses. 

    Thursday, October 6, 2011 7:34 PM
  • I'm absolutely at that point. I will try that next.
    Thursday, October 6, 2011 7:37 PM
  • It really goes quick once you get the hang of it, I swear.
    Thursday, October 6, 2011 7:40 PM
  • I actually got this going with the original schemas, thanks for all of your help.

     

    I did try out  "Add Generated Items" and I had a couple of questions:

    I performed the "Add Generated Items" successfully for table ACCOUNTACTIVITY. Now it generated just have one schema (Select, ACCOUNTACTIVITYRECORDSELECT, ArrayOfACCOUNTACTIVITYRECORDSELECT, SelectResponse)  for the same table and in the example they had three schemas. What are each one of these schema nodes used for and how do i setup the ports?

     

    Also where does that SOAP header in the send port properties come from?


    • Edited by MSDEVTECH Friday, October 7, 2011 12:20 PM
    Friday, October 7, 2011 11:37 AM
  • I thought you were doing this for a package, not a table.

     

    Selecting a table lets you do any DML you want pretty much against a single or multiple tables.

    (single) http://msdn.microsoft.com/en-us/library/cc185527(v=bts.10).aspx

    (multiple/composite) http://msdn.microsoft.com/en-us/library/dd788415(v=bts.10).aspx

     

    If you're wanting to call a procedure here are those details (you can make composite calls to anything, including packages):

    http://msdn.microsoft.com/en-US/library/cc185188(v=BTS.10).aspx

     

    When you generate those schemas it creates the Xsd as well as an Xml instance. That Xml is a binding file which will create a send port which conforms to the definition of your database call. After you import the binding, all you have to do is re-set the password on the newly created send port (bindings don't save password, why SSO is handy).

     

    Let me know if you want to do a package or a table operation and I'll help you more from there.



    Friday, October 7, 2011 5:10 PM
  • This worked for me!

    The bottom line is that the error message about needing 2.111.7.0 is misleading (or just plain wrong). The BizTalk Adapter pack documentation is definitely wrong when it says that it will only work with "Oracle Data Access Components for Oracle Client 11.1.0.6 with Patch Set 11.1.0.7" (which is a good thing since it is difficult/impossible to download that version anymore).

    I followed Stefan's suggestion and did the following: Uninstall the Adapter Pack, uninstall LOB Adapter SDK, install ODAC1110720.zip, install LOB Adapter SDK (64-bit), install BizTalk Adapter Pack (32-bit).

    BTW, I tried editing the config file to do the version redirect and it didn't work for me.

    Kudos to Stefan: Merci beaucoup!


    Nelson Robin, MCTS:BizTalk2010/WF/BI/WPF MCPD:Win4/Web4 MCITP:SqlDev/DBA MCT
    .NET Architect/Developer: BizTalk, BI, Silverlight
    ELNEL Consulting, LLC

    • Edited by Mr. BizTalk Thursday, September 27, 2012 12:43 PM
    Thursday, September 27, 2012 12:40 PM
  • Thx Nelson,

    It has been a while since I posted the suggested installation procedure, I'm happy it still interests some people.

    So just to confirm what one said above, you need to install first the Oracle x64, then the Oraclex32, the question is why? the answer is quite simple, the BizTalk Administration Console uses the x32, so it needs to be installed last, otherwise the x64 would overwrite the x32 binaries and hence you'll get the error Version of the assembly not found.

    The x64 assemblies will be still there for runtime

    Regards,

    Stefan


    BizTalk Consultant in France

    Thursday, September 27, 2012 2:05 PM