Important Hotfixes For the BizTalk Oracle Adapter

We’ve encountered a few quirky things with the Oracle database adapter for BizTalk, so I thought I’d point out a few Microsoft KB articles and hotfixes that you should be aware of if you’re using this adapter.

For some reason you need a compass and secret handshake to find these freakin’ things on the Microsoft website, so to grab the full list of Oracle adapter KB articles, visit here.

Technorati Tags:



Categories: BizTalk

21 replies

  1. Iam reading from Oracle database successfully.
    I want to update the status column to “Y” on reading from table.Pls let me know how to go aboout this.

    Thanks

  2. Wouldn’t you just use the “Update” object in your auto-generated Oracle schema? Create a message of type “update” where you set the “status” column value, set the “Filter” node to make sure you only update the single record, and you’re good to go.

  3. Thanks.
    After some struggle the update option is working.

  4. I’ve also heard that the adapter barfs if you try and use boolean values returning from procedures…..

  5. Hi Richard,
    I want to use user defined types in Oracle. but when i am generating schema for the User defined types in oracle it is generating xsd:base64Binary for that type . at runtime it is throwing a DB Exception with the following trace.

    Event Type: Error
    Event Source: Microsoft BizTalk Adapters for Enterprise Applications
    Event Category: None
    Event ID: 0
    Date: 10/4/2007
    Time: 9:40:11 AM
    User: N/A
    Computer: SALLTP070345
    Description:
    System defined exception
    Call to Insert@OracleApps://CPTEST/APPS/Tables/RAVITEST
    Implementation = OracleApps://CPTEST/APPS/Tables/RAVITEST

    Source: OracleApps
    Error Code: 932 (0x3a4)
    Cause: OracleApps://exception=DBException (Unique ID )
    HY000 : [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected APPS.RAVITEST_TYPE got BINARY

    Exception data:
    struct DBException =
    WideString StatusCode = “HY000”
    WideString Description = “[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected APPS.RAVITEST_TYPE got BINARY

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

    let me know whether the adapter has support for user defined datatypes or not

  6. Documentation for the adapter says …

    “User-defined types (objects) are not supported for stored procedure in/inout/out/return types.”

  7. Iam trying to export the bindings and do a import.
    It fails with following error:

    TITLE: Import Bindings
    ——————————

    Failed to update binding information. (mscorlib)

    ——————————
    ADDITIONAL INFORMATION:

    Could not validate configuration of Primary Transport of Send Port ‘sndOracleLimitsOutStatusSIM2’ with SSO server.
    Specify user name and password (Microsoft.BizTalk.ExplorerOM)

    There is a hotfix available for this.
    http://support.microsoft.com/kb/923733

    Meanwhile how to use the workaround give in the above link?

    Thanks

  8. Hey Hari,
    That’s a completely different issue. The MSI package that you’re deploying technically has an invalid binding inside. You should correct your binding (by replacing ****** with the actual Oracle database password) before including in the MSI to import.

  9. Thanks for your response.By hard coding the password I was able to import bindings.

    I have a scenario like this:

    Read from Different Oracle Instances convert it to flat file and FTP to different servers.
    For this purpose I have created multiple applications pointing to different oracle server and ftp servers.
    What is the best way for addressing this scenario?

    To avoid the creation for send port and receive ports for every application I am using bindings file to import.

  10. Hi

    Is there a way to create a dynamic Oracle Send Port?

    Thanks

  11. Hi,

    I have a problem with Oracle Adapter. Since I developed a biztalk project on testing environment, and using auto-genenate meta schema to call store procedure. All test in testing enviornment is okay. But when I deploy it to production environment, the oracle send port config with different username, then the biztalk project log an error in windows event log with ‘Invalid target: SP_BEFORE_RECEIVE@OracleDb://orcl/CIFADMIN/Procedures/TopLevel’ , which ‘CIFADMIN’ is username in testing environment. How to solve this problem?

    (ps: I tried hotfix 922810 but doesn’t work)

  12. The CIFADMIN in your namespace above is the schema owner of the database. Are you sure that the schema owner remained the same in the test environment?

  13. Hi I have a problem with oracle adapter for biztalk 2006 .
    when i try to do an update with autogenerated schema, i don’t receive any answer, if i debug orchestration, message response is always null….
    null if update is right (1 row inserted)
    null if filter clausole (where) does not match any records….

    Do you know if i can solve this problem ( i would not do a query to test if the record is inserted….)
    Thank you

  14. Hello Richard,

    Thank you for your reply.

    About the schema owner issue, actually the schema name(username) is different between production and test environment. Because of the security issue, I can’t touch the production schema. The schema name on production is config by DBA, and he will setup different schema name on Oracle Send Port to separate production and test environment. Thus, the issue raised.

    My question is, is there any way to solve this problem without change schema name in biztalk schema file(.xsd)? If I change schema name in biztalk schema file, then I can’t test my biztalk AP!

    Any idea?

  15. Hi Eliot,

    That’s a tough problem. Don’t know how you could avoid maintaining two sets of schemas, one with the “dev” schema namespace, and one with the “prod” namespace.

  16. Hi Richard,

    I am encountering the same issue that Elliot has raised.

    Invalid target: SP_toExecute@OracleDb://myDSN/mySchemaOwner/Procedures/myPackage’.

    I am sure that and are the same both in the Test and Prod environments. I validated this by autogenerating the schemas from the two environments. The generated schemas are the same.

    From Test environment, we only switch the TNS Service Name in the ODBC to connect to the Live Oracle db. UserID/Password is also the same.

    Is there anythings else that I missed, or need to look into? This is the only issue that needs resolving now so we can push our system to Live:(

    Thank you.

  17. Hi Richard,

    I am currently “upgrading” the old Oracle adapter to the new “Microsoft BizTalk Adapter 3.0 for Oracle” in our production environment.

    The old adapter had many shortcomings, but one of the issues which I was hoping would be resolved in the new adapter is the way it handles SP calls. One of the undocumented features of the SQL adapter is the ability to call a SP (which accepts parameters for inserts and updates) multiple times with a single document. As you will know, this is achieved by naming the repeating record with a name identical to the SP name. The SP is then called for every record in your document (all within a single transaction).

    I cannot get this working with both Oracle adapters! Do you have any idea whether this is possible with the Oracle adapter? All the tutorials and samples only show you how to call a single SP which returns data. I am looking for an example which allows me to call a SP repeatedly within a single transaction.

    Any ideas?

    Thanks…

  18. Hi there Wayne,

    So you tried this with the existing LOB adapter for Oracle and no luck? I’ll have to try that with the new WCF adapter for Oracle. If the adapter itself doesn’t support it, we may need to create a service wrapper which takes in an array of messages and calls all procedures within a single transaction.

  19. Hi,
    I was facing similar issue, due to different environments.
    The namesoace varies from env to env.
    I just created a custom pipeline component – which fetches the namespace from an external config file and replaces the namespace before the message hits the oracle DB.
    The values are fed into the config file based on the target environment.
    This is not a solution just a work around.

    Hope we get some breakthru using WCF – yet to try out…

Trackbacks

  1. Richard Seroter Saves the day (well, 2 days…) - Jason Hyland
  2. Adapter oracle ... ( la resa dei conti?) - Claudio Santorum Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: