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:
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
RSS Feed
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.
Good advice, Howard. I’ll have to incorporate that.
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
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”.
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é
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?
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
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?
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.
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
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.
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.
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
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.
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’.
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.
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
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.
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?
OracleCustomQuery_Request.parameters.StatementText you cannot fire this statement as biztalk messages are immutable.
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.
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.
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.
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
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.
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
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!
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.
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!
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.
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?
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.
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.
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.
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.
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.
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
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.
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!
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
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.
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.
Hi,
Richard thank for your help, now it is work.
Regards,
Marco Almeida
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
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.
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.
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;
Hey saurabh,
Please don’t post the same question to two different posts (http://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.
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
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.
Thank you.
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
Siva,
Guess it depends on your query. The “table” query does a “select *” operation. Are you confident that your nativeSQL query is returning results?
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.
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
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
***********
It seems positively Dickensian now. ,