In this post I like to show how to call a stored procedure in Oracle 11g XE performing an insert and an update. To create a stored procedure in Oracle 11g I look for guidance and found at Oracle® Database PL/SQL Packages and Types Reference, and PSOUG Insert and Update. I created the following two procedures:
create or replace procedure “INSERT_CUSTOMER”
(customer_name IN VARCHAR2,
customer_street IN VARCHAR2,
customer_city IN VARCHAR2,
customer_state IN VARCHAR2,
customer_photo IN BLOB)
is
begin
INSERT into customer (name, street, city, state, photo) values (customer_name, customer_street, customer_city, customer_state, customer_photo);
end;
create or replace procedure “UPDATE_CUSTOMER_ADDRESS”
(customer_name IN VARCHAR2,
customer_street IN VARCHAR2,
customer_city IN VARCHAR2,
customer_state IN VARCHAR2,
customer_photo IN BLOB)
is
begin
UPDATE customer SET street=customer_street, city=customer_city, state=customer_state WHERE name=customer_name;
end;
Both are very basic stored procedures. Next step in this little endeavor is creating a BizTalk project and create schemas for both procedures using the Consume Service Adapter Wizard.
After hitting OK a schema and binding file is created. Schema’s are depicted in screenshot below.
I build the solution after signing and providing it with application name. Next step is deploying the schema and configure the send port (by importing the binding file). Created Send Port contain two SOAP Actions and this needs to be one in my scenario (messaging). Two SOAP Action result in an error:
For calling stored-procedure to insert a new customer I have send port with following SOAPAction defined:
<BtsActionMapping xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<Operation Name=”INSERT_CUSTOMER” Action=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/INSERT_CUSTOMER” />
</BtsActionMapping>
With a filter for MessageType:
http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/INSERT_CUSTOMER#INSERT_CUSTOMER
And I created a new Send Port with SOAPAction:
<BtsActionMapping xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<Operation Name=”UPDATE_CUSTOMER_ADDRESS” Action=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/UPDATE_CUSTOMER_ADDRESS” />
</BtsActionMapping>
With a filter for MessageType:
http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure/UPDATE_CUSTOMER_ADDRESS#INSERT_CUSTOMER_ADDRESS
In both Send Port I added credentials and changed useAmbientTransaction setting to False (see also my previous post). Finally I created a Send Port to send both response to file with following filter:
BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure#INSERT_CUSTOMERResponse
Or
BTS.MessageType == http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure#UPDATE_CUSTOMER_ADDRESSResponse
and a receive port for incoming messages. When I test my application with following message:
<ns0:INSERT_CUSTOMER xmlns:ns0=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure”>
<ns0:CUSTOMER_NAME>Sarah Ralls</ns0:CUSTOMER_NAME>
<ns0:CUSTOMER_STREET>8th Street</ns0:CUSTOMER_STREET>
<ns0:CUSTOMER_CITY>Bellevue</ns0:CUSTOMER_CITY>
<ns0:CUSTOMER_STATE>WA</ns0:CUSTOMER_STATE>
<ns0:CUSTOMER_PHOTO></ns0:CUSTOMER_PHOTO>
</ns0:INSERT_CUSTOMER>
I receive this message in my out folder:
<INSERT_CUSTOMERResponse xmlns=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure” />
And I check the database to see if record with Sarah Ralls has been created:
This works out. I then offered the following message to update the address:
<ns0:UPDATE_CUSTOMER_ADDRESS xmlns:ns0=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure”>
<ns0:CUSTOMER_NAME>Sarah Ralls</ns0:CUSTOMER_NAME>
<ns0:CUSTOMER_STREET>9th Street</ns0:CUSTOMER_STREET>
<ns0:CUSTOMER_CITY>Bellevue</ns0:CUSTOMER_CITY>
<ns0:CUSTOMER_STATE>WA</ns0:CUSTOMER_STATE>
<ns0:CUSTOMER_PHOTO></ns0:CUSTOMER_PHOTO>
</ns0:UPDATE_CUSTOMER_ADDRESS>
And I received this in my out folder:
<UPDATE_CUSTOMER_ADDRESSResponse xmlns=”http://Microsoft.LobServices.OracleDB/2007/03/POLLING/Procedure” />
This works too and to be sure I checked database again:
I can tell you that calling a stored-procedure in Oracle 11g XE is straight forward, though I have to say I used pretty standard PL/SQL and did not use a cursor or anything. Something for another post. I hope this detail will show how to call stored procedure with BizTalk using the OracleDbBinding and Consume Adapter Service Wizard.
Technorati: 
biztalk server 2010
