Tuesday, June 12, 2012
1. Issue with a structure changes of DB
The JCA DB Adapter is tightly coupled to the DB structure. A rework is always needed to reflect any DB table structure changes (such as adding new fields or removing some fields, modifying data types). We have to regenerate the WSDL using JDeveloper & and make impacting changes in OSB services.
The fn-bea:execute-sql() function is much flexable and just change the select query used in the function, no additional changes.
The JCA DB Adapter is a reusable across projects/products. All Oracle SOA products (BPEL, ESB, OWSM, Oracle BPM) use the same adapter. In case you want to migrate to a different product from OSB then there is less migration effort for the JCA DBAdapter.
For simple select queries, both of them seem to give a similar performance. But the bea function was more expensive than the DBadapter as the number of records in the DB tables increase.
Thread: fn-bea:execute-sql() function vs Oracle JCA Adapter(https://forums.oracle.com/forums/thread.jspa?threadID=2358998&tstart=1275)