A Walk Through the BizTalk 2006 Oracle Adapter

Written by

Two of the six BizTalk projects I’m currently working on require me to access Oracle databases. So, I set out to prove out four major uses cases for the out-of-the-box Oracle BizTalk adapter.

Scenario #1 – Receive messages into BizTalk by polling an entire table

In this case, I need to pull in all of records for a given table. To do this, I created a new Oracle receive location in BizTalk. Under Managing Events I chose the table I wanted to poll.

That “adapter wizard” form opens up when selecting Managing Events. Then, using a previously set up ODBC connection, the adapter interrogates the Oracle database for available tables. Here I’ve chosen the “Departments” table. Next I created a send port that had a subscription to the receive port (basic “wiretap” pattern). The output from the Oracle polling looked like this …

Notice that the message root is TableChangeEvent and that the message has well-defined fields.

Scenario #2 – Receive messages into BizTalk using custom SQL script

Now I also want to poll the Oracle table with more detail than the “select * from department” which is used by the direct table polling above. So in the Managing Events for the receive location, I now choose the NativeSQL option …

Now, because I’m using NativeSQL I can utilize the “Poll SQL Statement” field in the receive location.

As you can see, my fancy SQL code is simply returning a subset of fields, and only where the “department_id = 10”. After setting up another send port to grab this message directly from the receive location, I got the following output …

Notice now that my root node is SQLEvent and that I don’t get an easy-to-use response. I get a collection of parameters, and then I get the actual values which correspond to those parameters. I can understand why it’s returned this way, but, it’ll require me to use an intelligent map to normalize the data to a more usable structure.

Scenario #3 – Send message to Oracle using table-specific operator

So what happens if I want to use an orchestration to call into Oracle and retrieve a dataset? First I did an “Add Generated Items” and pointed to a pre-configured Oracle receive/send port, and then I browsed to the table I wanted …

Once again I’m using the “Departments” table. When I finish up the wizard, I end up with a schema like this …

This may look familiar to you if you’ve used some of the other line-of-business adapters. You’ve got what amounts to a multi-part schema where you choose the “operation” you wish to consume. So if you want to do an “Insert” into the Oracle table, you pass in a message of type “Insert” and expect back an “InsertResponse”. In our case, I want to use the “Query” message, which allows me to apply both a filter (e.g. “department_id = 10”) and a “maxRows” property. My orchestration now looks like this after I reference the auto-generated “port type” …

I can simply use the port operation that corresponds to my desired table function. The response returned from the Oracle adapter in this scenario looks like this …

Notice that it is of type “QueryResponse” and that it has a nicely typed schema.

Scenario #4 – Send message to Oracle based on custom SQL

The final case involves using the orchestration to make a more complicated call to the Oracle database. For instance, if you wanted to do a cross-table insert, or do any sort of operation where the “stock” operations were too restrictive. I started by using the “Add Generated Items” wizard again, but this time, I chose NativeSQL as my service to consume.

In my example, the “custom” SQL was still very simplistic. The orchestration’s Construct shape included the instruction:

OracleCustomQuery_Request.parameters.StatementText = “SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID = 30;”;

So, a very similar query to the one in “scenario #3”, but, here I chose only a subset of columns. The auto-generated port type causes my orchestration to look like this …

So what does this custom call output? As you might guess, something a lot like “scenario #2” …

So there you go, a quick lap around the adapter. I know some folks rave about the TopXML Oracle adapter, but for now, this is what I’ll be working with.

Technorati Tags: BizTalk

Comments

81 responses to “A Walk Through the BizTalk 2006 Oracle Adapter”

March 12, 2007

Howard Edidin

I have worked extensively with the BTS 2006 Oracle adapter. I normally add a soap fault handler on the send-receive ports. This will return descriptive exception information This is especially helpful when using procedures. You never know when a package will become uncompiled.

Reply
March 13, 2007

Richard Seroter

Good advice, Howard. I’ll have to incorporate that.

Reply
March 18, 2007

Arockia

I want to get just newly inserted records from an oracle table.
But i don’t want to add trigger into that table.
Is it possible?

If it is possible, pls give me advice.

I am struggling to do this, and i am trying to get a solution for this last one week.

Please reply someone as soon as.

Thanks in Advance

Regards
Arockia

Reply
March 19, 2007

Richard Seroter

Hey Arockia,

I’m dealing with a similar situation, but my Oracle table has a “last updated” stamp, so we’re using a new table (with nothing but a “last poll time” field) and only getting records when “last updated” > “last poll time”.

Reply
March 28, 2007

René

Hi,

I got the situation where I got the recieve any rows (once a day) which havn´t been exported. In the table there is a timestamp, telling me if and when the row got exported. I just created an select statement (also including some joins) and use the SQLNative event.
As you wrote in your article, I get a nasty construct. My problem now is: How am I able to map this message, but only if the correct columns have been returned (so the select statement can be changed and then the message won´t fit to that target schema anymore…)

Thought about creating a new message from the existing one, but want to safe the rest of the team from too much custom code.

Would be cool if you would have some kind of mind-bridge for me 😉

Greetings René

Reply
March 29, 2007

Richard Seroter

You know, I’ve got Oracle tables that couldn’t be changed, so instead we created Views that returned only the columns we wanted. And, that view used the “last poll time” flag in a new table to only return rows that changed since the last poll time.

In your case, do you need to support the changing of columns returned? Is it that dynamic?

Reply
April 9, 2007

Ryan McIntyre

Richard –

Would you happen to have an example of executing a stored procedure using a receive location? I’m looking at either selecting the procedure through the location configuration (Oracle adapter wizard), or using an exec command using NativeSQL and haven’t had much luck yet.

Thanks.

Ryan

Reply
April 10, 2007

Richard Seroter

Hey Ryan,

I actually haven’t done it with a stored proc yet. Everyone keeps handing me Views or Tables to query. The “Oracle Adapter Walkthroughs” here (http://www.microsoft.com/technet/prodtechnol/biztalk/2006/library/adapters.mspx) don’t seem to cover it either. Does the procedure not show up when you browse for it?

Reply
April 10, 2007

Ryan McIntyre

Richard –

The procedure does show up, and I think if I were calling the sproc from within an orchestration it may be more straight forward. I’m trying to use a receive port to execute a sproc on a schedule. There’s more to the process, but that’s the piece I’m referring to.

I can select the package (not the procedure) in the port. Then when I enable the port I get errors such as:

I-OAP0022: Unable to find object describing this table: OracleDb://dsnnamehere/oracleuserhere/Procedures/PackageNameHere

The procedure doesn’t have any parameters. I’ve also tried a procedure with one out param, but same result.

Reply
May 27, 2007

Andy

Richard,
I’d appreciate it if you could elaborate on the “intelligent map” you referred to in your post…because I am trying to figure out how I can map the result columns returned as part of approach #3 into a more usable schema structure.

For eg, the source oracle SQLExecuteResponse section looks like this:
–
–

and the destination schema looks like this:
–
–
–

I am a BTS maps newbie and am trying this scenario out as practice. Would appreciate any pointers here.

Thanks
Andy

Reply
May 27, 2007

Andy

oops, i guess xml isnt allowed. But the basic idea is that I have a single repeating element in the source schema that needs to map to different elements on the destination schema. Thanks.

Reply
May 29, 2007

Richard Seroter

Hey Andy,

You may be able to pull off the “intelligent map” via functoids, but for me personally, I’d use a scripting functoid with inline xslt. In the xslt I’d loop through the columnMetadata and grab the columnData field at the same index. This would be in a map that took that format and converted it to a easy-to-use standard schema. You could even apply it at the receive port level so that your internal processes never know about the ugly original format.

Reply
June 16, 2007

saravana

Hello Richard
I have situation where i need to call store procedure only if there is any change in data from sql server.I dont have last updated time field also .Can you suggest me any ideas how to achieve this .Trigger is not an option like if there is any change then drop trigger xml file and biztalk will pick the trigger file and poll the sql adapter.Currently i have implemented a solution in biztalk.Sql adapter will poll for every 5 minutes and keep a local copy in machine.Later compare the data in the map using multiple inputs one from current message and the local data which got refreshed from last poll (only if there is change).The solution works fine for small amount of xml 500 kb data but more than that i dont get the data compared.Please share your ideas if you have come across situation like this.
Thanks
Saravana Ramkumar

Reply
June 20, 2007

Richard Seroter

Hmm, if you can’t do triggers/view/database modification, it’s going to be tough to implement this. You need some way to only pull fresh data, without forcing BizTalk to pull in ALL data each time.

Reply
August 8, 2007

Hari

Hi

Iam have requirement to read from Oracle table from biztalk.
I have mapped the table in the managedevents.
I find the Inserd,update,tablechangeevents for all the tables in the treeview are marked red.
What does this indicate?

Can you give me how this will be done using simple orchestration?
Iam getting following errors:

TableChangeEvent@SOAP://xmlns/schemas.microsoft.com/C_LIMITS_OUT_CTRL/C_LIMITS_OUT_CTRL:Binding
FaultCode = Time out waiting for poll
Time out waiting for poll
User data was returned in the ‘detail’ field but the fault type
called ‘detail’ could not be found.
This occurred in response to method ‘TableChangeEvent’.

Reply
August 8, 2007

Richard Seroter

If you are reading from the Oracle table, then you use the “Query” message type. You’d create a map to make an instance of the Query message, send THAT to the adapter, and expect a message of type QueryResponse back.

Reply
September 7, 2007

FREDY COREA

Richard

So far your article is the best one I have found in relation to BizTalk and Oracle adapter.

I am new into BizTalk, I am having some difficulties:
Where in the orchestration do you type the SQL Command?
How do you do the transformation inside the Construct shape?

Is possible for you to share the project used in your article, that will be of great healp and will be mostly appreciated.

Regards
Fredy

Reply
September 10, 2007

Richard Seroter

Hey Fredy,

Glad you found the post useful.

The query is executed (if an orchestration is calling into the adapter) by setting the “filter” value in the orchestration. You can do this in a transform by building up the filter statement using functoids, or, you could use a map to default the field value (to say, “”), and then use a “message assignment” shape immediately afterwards to set the filter via distinguished field. Just depends on where you want to maintain the logic.

Reply
September 13, 2007

Satya

OracleCustomQuery_Request.parameters.StatementText = “SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID = 30;”;

May I know OracleCustomQuery_Request what object is this?

Reply
September 13, 2007

Satya

OracleCustomQuery_Request.parameters.StatementText you cannot fire this statement as biztalk messages are immutable.

Reply
September 13, 2007

Richard Seroter

Satya,
I sure can, since my map sets the value to , and then I set that distinguished value in a “Construct” block immediately following the Transform shape.

Reply
October 11, 2007

Bembeng Arifin

Hi Richard,

I need to ask your advice regarding to the BizTalk 2006 Oracle Adapter.
It seems that I’m in the same situation as Ryan’s where he’s trying to call a procedure in the receive location. I have some logic that I need to execute when retrieving the data, and the way to do this is using oracle procedure.

I have created the package for the procedure with a variable in the package so the variable value will be set in the procedure. this will leave the procedure with no input / output parameters.

This is working fine as expected in the sqlplus, but when i tried this in the Biztalk, I’m stucked in the same error messsage :
I-OAP0022: Unable to find object describing this table:
OracleDb://dsnnamehere/oracleuserhere/Procedures/PackageNameHere

Do you have any solution that you can share for this?
Thanks.

Reply
October 15, 2007

Richard Seroter

Hey there,
I actually haven’t had the excuse yet to call a stored proc from the adapter. A co-worker and I have pledged to do so soon. I’ll try and include the scenario you’ve called out.

Reply
October 19, 2007

Bembeng Arifin

Hi Richard,

I just posted a question in here several hours ago, it’s probably my bad that it was not posted coz it is still not being added now.

I was posting a question which Satya was previously asking the about this as well, regarding to your scenario #4 :

OracleCustomQuery_Request.parameters.StatementText = “SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID = 30;”;

Please correct me if i’m wrong, OracleCustomQuery_Request is actually a biztalk orchestration message with NativeSQL.SQLExecute as its message type.

When I was trying to do the same statement, I got error compilation problem, because it was not able to recognize the parameter property of the message. May I know how you did it?

Your code statement will be a very nice solution for me since I find it more elegant in the code. Currently I can only assign the message using xml document :

strXml = “”;
xmlDoc.LoadXml(strXml);
msgNativeSQL = xmlDoc;

Thanks in advance Richard 😉

Reply
October 19, 2007

Richard Seroter

You are correct as to the “message type” used there. I actually distinguished the “StatementText” field, and used the map to simply set that value to “”. That’s why I could access that value in the Message Assignment.

Reply
October 21, 2007

Bembeng Arifin

Wow, that’s so simple, turned out that I didn’t notice the word “distinguish” in your earlier reply for Satya. I just knew that that is actually a distinguished property now 😉
I really like the result, with this, I can avoid hard coding the namespace in the orchestration.
Thanks again Richard 😀

Reply
November 22, 2007

malmeida

Hi,

Iam trying to do the scenario 2, but i not having success, please can some one explain in more detail the creation of the orchestration (Message type, etc) and the receive and send port on biztalk server 2006 admnistration console?

Thanks!

Reply
November 25, 2007

Richard Seroter

The “message type” will be chosen from the auto-generated Oracle schema. For instance, if you were doing an Oracle insert, you’d have one message of type “Insert” and another as “Insert Response”. Both would be used as part of the request/response port. In the Admin console, for calling Oracle, you create a (request/response) Send port with the appropriate security settings, and bind it to the orchestration.

Reply
November 26, 2007

malmeida

Hi,

Thank you for your response, now it is working.
But now i have a problem, i use sqlnative, and now i don’t know how to use a map. You have explain that you use a scripting functoid with inline xslt. Can you tell with more detail how can i do this?

Tnanks,
Regards!

Reply
November 27, 2007

Richard Seroter

If you’ve got a SQLNative message, you’ll have to pull out specific indexes of the XML. So your inline xslt might pull out the second columnData child node because you know it has the value you want in it.

Reply
December 6, 2007

John

Hi Richard,

Are there any problems in using this method on a BizTalk Server Group with multiple instances of the receive location polling concurrently on seperate application servers?

Will the adapter ever return the same records to multiple instances if the polling happens at the same time? Is this more likely to happen with a custom SQL select and update/delete for POLL and POST, rather than setting up a Managed Event on the table in question?

Reply
December 8, 2007

Richard Seroter

Hi John,

There’s a “use transaction” property of the adapter which is explained as:
“Select Yes or No from the list. If you select Yes, the transactions are enabled for the port being created. All orchestrations using this port must be transactional. Otherwise, calls are rolled back or are not committed.” But that’s probably on the “send”, not “receive” side. However for polling, the documentation states:
“Note that the execution of the poll statement and post poll statement is performed in a transaction of serializable isolation level. Records or changes that were not visible when executing the poll statement are also not visible when executing the post poll statement.” That all said, I personally played it safe and have the Oracle-polling receive location in a single instance host.

Reply
December 11, 2007

Siva

Hi,
I am new to Oracle + BizTalk. I want to insert multiple records into a table using oracle adapter. I wrote a oracle procedure and generated schema from it. when I pass xml with multiple records the output, schema that generated by oracle schema, has only one record, the first record.
I think we need to add “XMLDATA” syntex for SQL procedure..do we have to do same think for oracle procedure?
When I use SQL Updategram, the sql schema has “after, before, sync…” records, Do have to add those records to my oracle schema manually?
Overall, I am not sure how to use oracle procedure for multiple record insert in biztalk.

Reply
December 11, 2007

John

Thanks Richard,

From the Documentation;

“Note that the execution of the poll statement and post poll statement is performed in a transaction of serializable isolation level. Records or changes that were not visible when executing the poll statement are also not visible when executing the post poll statement.”

This implies data won’t be lost. But what about duplication? If instance A has polled will instance B wait for instance A to perform its post poll operations before it polls?

If they both poll at the same time data will be duplicated.

Reply
December 12, 2007

Richard Seroter

Siva,

Oracle adapter and SQL adapter are quite different. There’s no “for XML” concept as the Oracle adapter itself does the XML conversion. There’s also no “updategram” concept, so you need to either use a stored proc or table. I’m not aware of any way to “bulk insert” without simply calling the endpoint once for each individual message.

Reply
December 12, 2007

Richard Seroter

Hi John,

You’re probably right on duplication. If a second instance of the adapter polls BEFORE the first can run the post-poll statement, then sure, I could forsee duplication happening.

Reply
December 12, 2007

malmeida

Hi,

Richard, i am trying to execute a query, like scenario 3, and i have read your post and the post of Bembeng Arifin and i can’t put this to work. Can you explain me how to access to “filter” in the message assignment?
At this moment i have a construct message shape, inside i have a transform shape and in the map i only put the field “filter” to empty. I have a stored procedure that returns a value and i have distinguish this value. Next i have a message assignment, but i don’t know how to do this…
Can o please put an example!

Thanks

Reply
December 12, 2007

Richard Seroter

To access it in the message assignment shape, you can distinguish the field. Or, you could use the “concatenate” shape in the map to build the filter value there as well.

Reply
December 12, 2007

malmeida

I need to distinguish the fielter field and the value of my sotred procedure?
In the message assignment i have put this:
MsgIDE_TMP_REMOTA(SONHO_.PropertySchema.Filter) = MsgPESQUISA_REMOTAResponse(SONHO_.PropertySchema.Return);

MsgIDE_TMP_REMOTA(SONHO_.PropertySchema.Filter) –> Field Filter (Query)

MsgPESQUISA_REMOTAResponse(SONHO_.PropertySchema.Return) –> value from my stored procedure that i want to filter.

I have tried this, but when i start the application this stop after the send shape (I have the construct message and send shape blue but the receive shape (QueryResponse) is green, in the orchestration debugger).

thanks for so fast reply in the other post.

Regards!

Reply
December 12, 2007

siva

Thank you Richard.
If i want to use the package/store prcedure, could you give me some idea? i am new to Oracle.
I just wrote a single procedure with paraters. Generated Adapter schema’s elements are whatever i had as parameter for procedure. The problem is that adapter schema could not create multiple record level.
To insert the records one by one, what steps i have to follow?
Thank you in advance.
Siva

Reply
December 12, 2007

Richard Seroter

Malmeida,

Your example there confuses me. The idea is that your Oracle message has a filter that acts as the WHERE clause of the query. So you’d have something like …

MyOracleUpdateMsg.Filter = “departmentcode = 1234”

The “filter” value is distinguished on the auto-generated Oracle schema so that you can set that from a Message Assignment shape. Not sure what you’re doing with property schemas there.

Reply
December 12, 2007

Richard Seroter

Siva,

To process one by one, you’ll have to debatch your inbound message (assuming you are taking in a message with multiple records and wanting to add each to your Oracle table) via either a pipeline or orchestration. There’s lots of information online about debatching messages in BizTalk. You’d then have the option of mapping each individual message to the Oracle “insert” schema and using that map on an Oracle send port. So, if you passed in a message with 10 records, debatching it would produce 10 individual messages, where each one has the map applied, and each one goes through a send port to Oracle.

Reply
December 13, 2007

malmeida

Hi,

Richard thank for your help, now it is work.

Regards,
Marco Almeida

Reply
December 14, 2007

siva

Thank you Richard.
I used the orchestration method. I am having issue in inserting in to oracle table.
If i have 4 records in the input file, only one record will be insert into table.
I tested with 124 records, only 106 records has been inserted. Same time if i send the out put to some folder, i am getting 124 xml out puts. It looks like debatching and mapping works fine however I it is not inserting into table correctly.
I am using a package something like:

CREATE OR REPLACE PACKAGE Order_Pac AS
PROCEDURE order_Proc(nID IN Varchar2, nName IN Varchar2);
END Order_Pac

CREATE OR REPLACE PACKAGE BODY Order_Pac AS
PROCEDURE order_Proc(nID IN Varchar2, nName IN Varchar2)
IS
dDate Date;
BEGIN
insert into Order_Tab (nID,nName,dDate) values(nID,nName,dDate)
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
WHEN OTHERS THEN
— Consider logging the error and then re-raise
ROLLBACK;
END Order_Proc;
END Order_Pac;
/

I am new to BizTalk and Oracle. So I don’t have much knowledge of it.

Thank you in adavance,
Siva

Reply
December 21, 2007

Oracle ODBC Driver Problems After Database Upgrade « Richard Seroter’s Architecture Musings

[…] After Database Upgrade Published December 21, 2007 BizTalk Given that my posts on BizTalk + Oracle integration are consistently my most popular, I thought I’d add another one to the […]

Reply
December 26, 2007

malmeida

Hi!

I need again your help. 🙂 In a previous post i have told that i have a scenario very simmilar to scenario 3.

This is the scenario:

I have an oracle database and i need to execute a stored procedure.

Example:

I have a table and i do a delete after poll with the table change event, this works fine when i only have one record. But if i have multiple records this don’t work how i expected. I have resolved this, on the receive port; i configured the property Event Max Row Count -> 1. Now if i have multiple records, create one “message” for each record. Now i have to call a stored procedure, the first records that is polled from the table is process immediately, but the other ones i need to wait 5 minutes for each one. Where can i change this property? It is possible to change?
I have tried to change some properties from the oracle port, but i don’t know what is the correct property. I tried refresh agent but if i open again i put always to “no”.

Thanks!
Regards.

Reply
December 27, 2007

Richard Seroter

Hey malmeida,

I think the “delete after poll” works for SQL statements, not stored proc calls. If you pull 10 records, debatch them, then you can’t wait 5 minutes between processing each … they all happen at the same time. If you need to introduce flow control, consider an orchestration to manage the process.

Reply
January 3, 2008

saurabh

hey richard i have to insert data from one A table to another B table in bulk mode both table are on diffrent server ;The problem is some data is already presenet in B table ;
when i am inserting then batch operation fails because of some already inserted data are present in B Table;
how will this problem solved i want isertion in batch mode and the record which already exist that will skip;

Reply
January 6, 2008

Richard Seroter

Hey saurabh,

Please don’t post the same question to two different posts (https://seroter.wordpress.com/2007/01/03/debatching-inbound-messages-from-biztalk-sql-adapter/).

In your case, consider writing your stored procedure to do this work for you. Your procedure queries the table before inserting and if the data exists, an update is done instead.

Reply
January 24, 2008

siva

Richard,
Would it be possible to retrieve multiple rows through package in oracle adapter? I could not use ref cursor since Biztalk 2006 R2’s Oracle adatper doesnt not support to it.
I dont want to retrieve one by one..I need to use the procedure to do it.
I wrote a simple procedure with 2 output paramter. Which is just returning 2 clom records from a talbe. I use the talbe for manage event. When i engable the send port and receive port, I didnt get any out. not error message too.
What could be the reason for it?

Thank you in advance,
Siva

Reply
January 28, 2008

Richard Seroter

Siva,

You can retrieve multiple rows via table/view polling. I haven’t used the Oracle adapter with stored procedures, but I would assume it is possible, depending on how you return the recordset.

Reply
January 29, 2008

siva

Thank you.

Reply
February 29, 2008

siva

Hi Richard,
I am using your 2nd method,Managing Events = Native SQL, to retreive data from table. How come i dont get any out put? It looks like receive port not polling on given intervel.
When I use ur first method, Managing Events = table, I am getting out put.

Thank you in advance,
Siva

Reply
March 6, 2008

Richard Seroter

Siva,

Guess it depends on your query. The “table” query does a “select *” operation. Are you confident that your nativeSQL query is returning results?

Reply
March 27, 2008

The Adapter Diaries – Oracle Adapter – 2 « Santosh Benjamin’s Weblog

[…] external “poll tables” as Richard Seroter pointed out in the feedback section to his Walkthrough article. (To summarise that : He had another table with just one entry in it – the last poll time and […]

Reply
May 2, 2008

Bembeng Arifin

Hi Richard & John,

I found a work around to avoid duplication by using oracle function. It’s a bit hack but it solved the duplication polling problem without depending on the post poll SQL Statement.

It seems that I can’t post a link in the comment body, but you can find the post by clicking on my link.

Hope this helps anyone with the same issue.

Thanks.

Reply
October 1, 2008

shashikant raina

Richard,

I heard that Biztalk WCF based adapters are not compatible with Oracle 8 i REF CURSORS. I do not have Oracle 8 i version to validate this. I know they work with Oracle 10 i. Any thoughts?

Shashi

Reply
October 6, 2008

Richard Seroter

Hi Shashi,

According to the Help docs for the adapter, the WCF Oracle adapter doesn’t support anything below Oracle 9. It says …
**********
The Oracle Database adapter can be used to connect to the following versions of the Oracle database:

Oracle 9.2.0.2

Oracle 10.1.0.2.0

Oracle 10.2.0.1.0

***********

Reply
January 18, 2009

Oracle adapter issue in BizTalk | keyongtech

[…] Packages before on the Oracle adapter, however take a look a the the following two blog posts: https://seroter.wordpress.com/2007/03…racle-adapter/ and http://www.cubido.at/Blog/tabid/176/…5/Default.aspx Hope these help. Regrds, Nick. […]

Reply
October 22, 2009

Maxx42

It seems positively Dickensian now. ,

Reply
April 12, 2010

Dave

I don’t see a managing events item in my Oracle adaptor transport properties. Do I have a different version or BizTalk 2006 or a different version of the Oracle adaptor, or did I install BizTalk incorrectly? I’m using BizTalk 2006 r2 with the business adapters for Biztalk including oracle installed.

Reply
1. April 12, 2010
  
  Richard Seroter
  
  Hi there,
  
  This was written against the “classic” Oracle adapter, not the new WCF ones. Do you know which one you have installed?
  
  Reply
  1. April 13, 2010
    
    Dave
    
    Unfortunately no. Where can I look for a version number? I know we installed the adapter at the same time we installed BizTalk 2006 r1 from CD; there was a separate CD labeled Adapters for Enterprise Applications, and we installed the Adapter for Oracle Database from that.
    
    Reply
  2. April 13, 2010
    
    Richard Seroter
    
    Dave, that sounds like the classic adapter that’s being deprecated in the near future. If you’re using 2006 R2, then I think you can leverage the v1 of the BizTalk Adapter Pack (http://www.microsoft.com/downloads/details.aspx?FamilyID=58bd17c8-8efa-4b69-ab51-9f5b11a962dc&DisplayLang=en). As for not seeing the managed events, are you seeing the other adapter specific properties? And you’re looking at a receive location or send?
    
    Reply
April 13, 2010

Dave

I’ll check that link, thanks. As for what I’m seeing, it’s a send port and the other adapter specific properties are there. Clicking on Configure brings up the transport properties pop-up window with Oracle connection info first (pw, path,service name,username) and then concurrency control, connection, event log, refresh agent, use single sign-on and use transaction.

Reply
1. April 16, 2010
  
  Dave
  
  I was looking only at Send ports, not Receive. The Managing Events options are present in my install. Thanks for your help.
  
  Reply
April 16, 2010

Richard Seroter

Dave, do you see it in the Receive location? I haven’t used this version in a while, and trying to remember if this attribute is on both receive and send ports. Otherwise, I’d shoot for posting this in the BizTalk Adapters forum (http://social.msdn.microsoft.com/Forums/en/biztalkr2adapters/threads)

Reply
1. April 16, 2010
  
  Dave
  
  Yeah, it’s in the Receive Port only. I never thought to look there because up until now I’d been doing stored procedure calls only through Send Ports. Thanks again for your help.
  
  Reply
August 12, 2010

Hari

I have a requirement to read from Oracle DB at a very high rate.
The polling will restrict the number of records.
Iam using the classic Oracle adapter.
What will be best way to read max number of records in minimum time frame.

Thanks

Reply
1. August 26, 2010
  
  Richard Seroter
  
  Hi, don’t have a specific suggestion. Sounds like you want a short polling cycle but may want to call a procedure that restricts records.
  
  Reply
September 13, 2010

sneha

Hi Richard,

i have a requirement where i need to load data into oracledb.i need to use oracledb adapter.i would like to know which operation i should use.inbound operation or outbound operation.and also i need to check the load status on other table when it completes.let me know how can i accomplish this.
thanks
Sneha.

Reply
November 7, 2010

Sgreeny

Hi Richard

I have tried using Scenario #2. It works only when the return recordset is not huge. If I did not put any condition (where clause), the result always failed. (I am expecting more than 100,000 records in the poll).

Error Message as below:
Failed to send notification : System.Runtime.Remoting.RemotingException: Underlying connection was closed: Either there was a fatal error on the server or client authentication failed

Server stack trace:
at System.Runtime.Remoting.Channels.SocketHandler.ReadFromSocket(Byte[] buffer, Int32 offset, Int32 count)
at System.Runtime.Remoting.Channels.SocketHandler.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.Runtime.Remoting.Channels.SocketHandler.ReadAndMatchFourBytes(Byte[] buffer)
at System.Runtime.Remoting.Channels.Tcp.TcpSocketHandler.ReadAndMatchPreamble()
at System.Runtime.Remoting.Channels.Tcp.TcpSocketHandler.ReadVersionAndOperation(UInt16& operation)
at System.Runtime.Remoting.Channels.Tcp.TcpClientSocketHandler.ReadHeaders()
at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.ProcessMessage(IMessage msg, ITransportHeaders requestHeaders, Stream requestStream, ITransportHeaders& responseHeaders, Stream& responseStream)
at System.Runtime.Remoting.Channels.BinaryClientFormatterSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.BizTalk.Adapters.RuntimeAgentRemote.Poll(Int32 timeOut, String& data, String& transactionId)
at Microsoft.BizTalk.Adapters.CBRuntimeAgent.Poll(Int32 timeOut, PollStatus& status)
at Microsoft.BizTalk.Adapters.ReceiverEndpoint.ProcessWorkItem()

Reply
1. November 9, 2010
  
  Richard Seroter
  
  I had very little success with data sets greater than 10,000 records.
  
  Reply
January 5, 2011

Harold

Well, I tried to follow along in BizTalk 2010 but the differences are too great. The only question I had was did you have an application previously deployed or were you just going into the BizTalk server administrator and creating a new receive port – I started with BT 2009, so I am used to the new Receive Port property sheet which has a Receive Location node in the navigation pane. As far as transport, there is no Oracle Adapter (I am using ODAC 11.2). The WCF shows a binding type of OracleDBBinding in the WCF Transport Properties. There is no way in that particular property sheet of specifying a TNS service name or DSN. Perhaps you could update your article. Thanks

Reply
1. January 12, 2011
  
  Richard Seroter
  
  Harold, the changes are indeed pretty different between those. I wrote an article on polling with the new Oracle adapter that you can find here.
  
  Reply
May 4, 2011

NewBiztalkBee

Im new bee to biztalk server.I got a situation here,i tried to connect oracle database from biztalk server 2009.I have installed classic oracle adapter(BizTalk_LOB_Adapters.exe) and Oracledatabase (win32_11gR1_database_1013.zip) in that machine. When i tried to connect after adding the oracle adapter in administration console,its not connecting and found the following error in Event viewer.

The description for Event ID ( 0 ) in Source ( Microsoft BizTalk Adapters for Enterprise Applications ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Exception occurred:
Error Code: 1017 (0x3f9)
28000 : [Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
.

Please help me out of this.Thanks in Advance.

Reply
May 24, 2011

SMH

Dear Richard,
I need to get multiple records from an ORacle table based on some WHERE clause, need to do some processing on these records one by one and then create individual messages. This is not possible with TableChangeEvnt because it polls all the records from the table
Please help

Reply
1. May 26, 2011
  
  Richard Seroter
  
  Can you use the 2nd scenario above and apply custom script?
  
  Reply
September 7, 2011

dinesh

My requirement scenario:

I need to fetch multiple records from an Oracle base table based on the timestamp create/modify, which are new or modified records and its dump [insert/updated] on destination table.

Can you suggest me the proper scenario on the same.[but I want to avoid the store proc]

Thanks,
Dinesh

Reply
January 9, 2012

dddas

images are out of date

Reply
1. January 9, 2012
  
  Richard Seroter
  
  Hi there. What’s out of date? This pertains to old LOB adapter, not new WCF one. You can see an example of the new one here … https://seroter.wordpress.com/biztalk-and-wcf-part-vii-about-the-biztalk-adapter-pack/
  
  Reply