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.
- FIX: One of the Microsoft BizTalk Adapters for Enterprise Applications stops processing incoming messages until you restart the BizTalk Host instance. I’ve gotten this one a couple of times in production under high load situations. I’ll probably reduce the number of “concurrent connections” to 1 for each individual send port.
- FIX: Error message when you use Microsoft BizTalk Adapter for Oracle Database and when you call a stored procedure on an Oracle server: “Error Code 6550”. This has to do with using the NUMBER data type in Oracle.
- FIX: An event may be logged when you use BizTalk Adapter for Oracle Database. This is a horrible article name (could it BE any more generic), but has to do with not passing in DATE values when doing inserts into Oracle tables.
- FIX: The Runtimeagent.exe process stops responding when you make concurrent calls by using BizTalk adapter. I really dislike that all I see in the Windows “Processes” pane is a bunch of “runtimeagent.exe” and there’s no way to see which .exe is doing what. This is another KB that has to do with concurrency.
- FIX: You receive an “Import Error” error message when you try to import an .msi file for a BizTalk application. I’m actually not sure why this is a hotfix. Anytime you have a binding with passwords, the actual password is replaced with “******”. I’ve gotten into the habit of keeping my binding files separate from my MSI when using the Oracle or Siebel adapters. That way, I never have an MSI with passwords embedded in clear text.
FIX: BizTalk Adapter for Oracle Database modifies the data in the DateTime field when you use the Insert method, the Update method, or the Query method. Read that again. The adapter assumes everything is in GMT time. So, when doing inserts or queries, the date is converted to a local time. Big ramifications if you don’t realize that (or apply the hotfix).
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: BizTalk
Richard Seroter's Architecture Musings 
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
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.
Thanks.
After some struggle the update option is working.
I’ve also heard that the adapter barfs if you try and use boolean values returning from procedures…..
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
Documentation for the adapter says …
“User-defined types (objects) are not supported for stored procedure in/inout/out/return types.”
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
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.
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.
Hi
Is there a way to create a dynamic Oracle Send Port?
Thanks
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)
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?
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
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?
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.
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.
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…
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.
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…