Download MediatorExample.
Following Part 2 of implementation earlier, let us proceed to the final step.
Part 3: Database Adapter for Order Management
Create ORDERS and ITEMS tables in the database of your choice. Description of these tables shown below. SQL script can be downloaded from here.
Drag and drop a Database Adapter from Component Palette on to Services section of composite.xml. Enter OrderManagementDBAdapter for “Service Name”.
Select Database properties in the next screen. This presume you’ve successfully created Database connection pointing to the database containing ORDERS and ITEMS tables. If not, please click on the pencil icon next to “+” icon and create a new database connection.
Select “Insert of Update” operation from next screen.
Auto Query and select ORDERS table from the Available tables. Click on “>” arrow to move ORDERS table to “Selected” section. Select all attributes from next screen.
 |
 |
Enter ORDER_SEQ in Sequence field and hit on Search. This will populate ORDER_SEQ sequence we created earlier. Click on Finish.
We are now back to original composite.xml screen.
Right click on OrderABCS and connect it with OrderManagementDBAdapter. We need to convert OrderABCS’s canonical data into the format understood by Order Management system.
Double click on OrderABCS and go to “Static Routing” section.
Create new transformation mapping by clicking on icon next to “Transform Using” field. Select “Create New Mapper File” from next screen and accept default file name.
Create a mapping as shown below:
This completes the final step. We have now successfully created DB Adapter and linked it to composite Mediator component.
Finally, deploy it to Application Server and ensure that its listed in EM console.
To run this example, place Order.txt file in “D:\FUSIONTRAINING\SOA\SOATrainingSamples\MediatorExample\ftp\input” (or the directory you’ve specified for file polling). After 30 seconds, Order.txt will automatically be picked up, transformed and written to ORDERS table. Ensure ORDERS table contains data obtained from Order.txt file.
Follow the article on MDS Repository if you want to access XML artefacts of this project through MDS.
Thank You for such an elaborated example. I have two questions to ask if you don’t mind:
a) How do you set the purging from within SOA of completed instances?
b) We have the meditator using a staging or intermediate directory being used for archiving, is there a way to automatically purge these files on a regular basis?
c) Is there a way to automatically get notified when the Mediator goes into a suspended mode?
I would appreciate any insights…
Thank You.
a) How do you set the purging from within SOA of completed instances?
http://download.oracle.com/docs/cd/E14571_01/integration.1111/e10226/soacompapp_mang.htm#CJHDHHGB
b) We have the meditator using a staging or intermediate directory being used for archiving, is there a way to automatically purge these files on a regular basis?
This is beyond the score of SOA suite since we are directly dealing with file system. Yes, SOA Suite can help archive/delete files after they are successfully processed. However, once the file is archived, you’ll have to create your own purge scripts depending on the OS used.
c) Is there a way to automatically get notified when the Mediator goes into a suspended mode?
SNMP traps is one of the ways to achieve this functionality. I shall create a sample to demonstrate this feature soon.
Thank You for your replies. we have something working for part b) and just learnt that we also are using the purge scripts for a). would like to know if anyone has done anything c), I am thinking of writting custom alert in OEM/GC to be notified using the CUBE_INSTANCE table query.
Thank You.
Excellent article for a beginer. I followed every step outlined in your example. When I copied the Order.txt file I got he following error in OEM console. The tables did not get populated. Please see error below and advise what we need to be done to take care of this error. Looks like some sort of access or setup issue with ORDER_SEQ objects. I created the tables and sequence in database as per your script.
Thanks in advance.
Jun 7, 2012 12:56:36 PMMessageonCase “OrderABCS.OrderABCS.ReadOrder”
Jun 7, 2012 12:56:36 PMMessageEvaluation of xpath condition “No Filter” resulted true
Jun 7, 2012 12:56:36 PMMessageTransformed message part “payload” using “xsl/OrderCollection_To_orderDetailsRequest.xsl”
Jun 7, 2012 12:56:36 PMMessageError during invoking 2-way operation “ReadOrder” on target service “OrderABCS.OrderABCS”
Exception occured when binding was invoked. Exception occured during invocation of JCA binding: “JCA Binding execute of Reference operation ‘merge’ failed due to: DBWriteInteractionSpec Execute Failed Exception. merge failed. Descriptor name: [OrderManagementDBAdapter.Orders]. Caused by Exception [EclipseLink-7027] (Eclipse Persistence Services – 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.ValidationException Exception Description: The sequence named [ORDER_SEQ] is setup incorrectly. Its increment does not match its pre-allocation size.. Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake. “. The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.
The error you are getting is because of configuration mistake in setting up the JCA adapter. Take a look at http://www.orafmwschool.com/sequence-increment-and-pre-allocation-size/ for details on how you can rectify this.
Regards,
Chethan
Ｉfollowed your instructions in this example. and I got three issues with my development.
my SOA11g version is 11.1.1.6
1. Database Adapter wizard: step3 of 9
I create a db connection as:
connection name: ora11g
connection type: Oracle (JDBC)
username: sys
Role: SYSDBA
PAsswd: orcl
saved PAssword
driver: thin
hostname: dbserver.darren.com
JDBC Port: 1521
SID:ora11g
test connection: success!
in step 3 of 9, I select connection:
connection
ra11g
JNDI Name: eis/DB/ora11g
In step 8 0f 9, section: Native Sequencing(Oracle Only)
table: SOA.ORDERS
sequence: –NONE–
I click search, but could not find SOA.Order_seq, so I have to leave it as –NONE–
I created the soa table and sequence using the sql file from your website as following:
————————————————
drop user soa cascade;
create user soa identified by soa;
grant connect, resource to soa;
create table soa.items
(
id number primary key,
name varchar2(20 byte),
description varchar2(64 byte)
);
create table soa.orders
(
id number primary key,
address_line1 varchar2(64 byte),
address_line2 varchar2(64 byte),
address_city varchar2(64 byte),
address_state varchar2(64 byte),
address_zip varchar2(20 byte),
address_country varchar2(64 byte),
user_first_name varchar2(128 byte) not null,
user_last_name varchar2(128 byte),
item number,
order_date date
);
alter table soa.orders add (
constraint orders_items_fk1
foreign key (item)
references soa.items (id)
on delete cascade);
create sequence soa.order_seq
start with 1
maxvalue 99999999
minvalue 1
nocycle
cache 20
noorder;
————————————————
I wonder whether I should configure DBadapter to reflect the JNDI name I gave in the example.
because no where in the platform will know eis/DB/ora11g. so I create eis/DB/ora11g in outbound connection pool.
set xADataSourceName:jdbc/SOADataSource
I got a binding error:
<Got an exception: oracle.fabric.common.FabricInvocationException: BINDING.JCA-12563
"JCA Binding execute of Reference operation 'merge' failed due to: DBWriteInteractionSpec
[OrderManagementDBAdapater.Orders]。
caused by java.sql.SQLSyntaxErrorException: ORA-02289: sequence doesnot exist!
Please give me a hand on this!