Download MediatorExample.
Following Part 1 of implementation earlier, let us proceed to the next step.
Part 2: File Adapter
Drag and drop a File Adapter from Component Palette on to Components section of composite.xml. Enter OrderFTPClientService for service name.
Enter values for next steps as shown in screenshots below.
Select “Read File” as Operation Type and enter operation name as ReadFTPFile as shown below.
Enter directory for incoming files. MyBay would FTP order files in to this directory. Select Archive processed files checkbox and specify directory for storing archived files. Once order file is success processed, we want to archive it and remove it from original directory for incoming files.
Select checkbox before “Delete files after successful retrieval”.
File Filtering can happen either through wildcards or regular expressions. For normal purposes, wildcard filter would suffice. Regular Expression can be used in advanced cases. Specify “Order*.txt” in the name pattern field.
Check “Files contain Multiple Messages” checkbox as we’ll have more than one order in the incoming file. We can post more than 1 record in a batch. However, this is useful only when the outbound service is capable of consuming records in batches. Database Adapter can process records in batches. For this example, we’ll keep batch size as 1.
In next screen, we can specify file polling frequency. Minimum File Age is an interesting option. File poller will process this file only “minimum file age” time after file is last updated. This is useful when we receive large files that take very long time to be transferred and prevent file pollers from processing the file while its still being FTPed.
In next screen, we need to create a schema to map to an incoming order file. Click on “Define Schema for Native Format” icon at the end of URL field as shown below.
Our order file will be a comma separated file. So, lets choose Delimited option.
Select Order.txt in File name section. This can be found in ./MediatorExample/ftp/input directory of the installation zip file.
For next steps, use the screenshots shown below.
Finally, specify file name for the XSD as FTPFileOrder and Finish. We will now come back to original File Adapter wizard with Schema details populated.
Finally, specify file name for the XSD as FTPFileOrder and Finish. We will now come back to original File Adapter wizard with Schema details populated. Click on Finish to complete File Adapter creation.
Drag and drop another Mediator component from Component Palette to create connector for File Adapter, to convert xml order data into canonical form. Name Mediator as OrderFTPABCS. Connect this component with OrderFTPClientService created earlier.
Drag and drop another Mediator component from Component Palette to create connector for File We need to transform XML data read from FTP File Adapter to canonical form. Double click on OrderFTPABCS.
Under Routing Rules, click on icon next to “Transform Using” field.
Create a new mapper file. field. File name will be automatically populated. We can change it to any name we like.
Right click, drag and drop OrderCollection element on to orderDetailsRequest.
Right click, drag and drop OrderCollection element on to orderDetailsRequest on the right. Uncheck “Match Elements Considering their Ancestor Names”.
Use the screenshot below to map elements.
Finally, connect OrderFTPABCS to OrderABCS. This completes the process of creating File Adapter, transforming data into canonical form and linking it to the Order Management connector to write details to Order Management.
In the next and final step, we shall examine how to create Database Adapter and link it to OrderABCS.
Hi Amjad,
I am trying to invoke a OracleApps stored proc from OSB using BPEL Db adapter.I am able to connect to the database through the DBAdapter in Jdev and my proc is available to me. I have also cteated a connection pool with proper JNDI name and deployed my project successfully in OSB .but i am gettig an error while running the project from sbconsole.
For the business service i am using jca transport and i have also provided the JNDI name in the URI i.e ‘jca://eis/DB/OraAppsDEV’ but i am getting this error.The proc is working properly according to the apps people but I am not able to access it from OSB.
Error that i got is as follows :
BEA-380001: Invoke JCA outbound service failed with application error, exception: com.bea.wli.sb.transports.jca.JCATransportException: oracle.tip.adapter.sa.api.JCABindingException: oracle.tip.adapter.sa.impl.fw.ext.org.collaxa.thirdparty.apache.wsif.WSIFException: servicebus:/WSDL/OraAppsServices_SB/WSDL/OraAppsDEV [ OraAppsDEV_ptt::OraAppsDEV(InputParameters,OutputParameters) ] – WSIF JCA Execute of operation ‘OraAppsDEV’ failed due to: Stored procedure invocation error.
Error while trying to prepare and execute the APPS.BPEL_ORAAPPSDEV.NMTC_INV_CUST_TXN_PKG$NMTC_IN API.
An error occurred while preparing and executing the APPS.BPEL_ORAAPPSDEV.NMTC_INV_CUST_TXN_PKG$NMTC_IN API. Cause: java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04063: package body “APPS.BPEL_ORAAPPSDEV” has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
pls help.
Your package seems to be in invalid state. Try recompiling it first.
Its compiling fine and I am able to see the output in the Jdeveloper itself but problem occurs when i am trying to access it from OSB.Its showing me the error as i have pasted in my previous comment.
Update:
Thanks for your reply.You are right some changes were made in the proc and the matter got resolved.
Hi Amjad,
We want to create an OSB process which connects to the database and executes an sql query automatically at regular intervals ,i.e.,like a polling process.Is it possible?Can you please help me if you have any ideas.
One option is to use DB Adapter in OSB. You need to import DB Adapter schema, WSDL and configure JCA Binding separately.
Can you tell me whether we can specify the time interval for the execution in OSB process so that it automatically gets executed after that interval?
You can specify polling frequency for DB Adapter. Please see following articles for more information:
1. Using JCA Adapter with OSB 11.1.1.3
2. Oracle JCA Adapter for Database
If your requirement is more of a scheduler, then I wouldn’t recommend OSB since it is stateless and is not really designed to handle such communications. You may want to consider BPEL for this purpose.
Have a look at this discussion to get some perspective on OSB’s scheduling/timer limitations: https://forums.oracle.com/forums/thread.jspa?threadID=1046609
Thank u very much.I’ll check out with these websites.
I am getting this particular error sometimes while trying to run any OSB process.This is not prevalent but frequntly comes up.The error description is:-
Activation of publish session failed for:OSB Configuration 1
C:\OSB_Middleware\user_projects\domains\base_domain\osb\config\sessions\ALSB_IDE_Internal_PublishOp_1311242-2049\_binary\SessionData\singleton (The system cannot find the file specified)
java.io.FileNotFoundException: C:\OSB_Middleware\user_projects\domains\base_domain\osb\config\sessions\ALSB_IDE_Internal_PublishOp_1311242-2049\_binary\SessionData\singleton (The system cannot find the file specified)
Can anyone help me out?
Ranjeeta
In case you still haven’t found the reason for this error, it seems to be because of invalid session. Removing or Renaming “ALSB_IDE_Internal_PublishOp_1311242-2049″ folder and restarting Managed Server seems to have fixed the issue.
See this discussion – http://forums.oracle.com/forums/thread.jspa?threadID=2180123
Hi Amjad,
I am trying to use DVM lookup table in OSB.For this i have cteated a dvm table in Jdeveloper and trying to use that in Xquery transformation in OSB.
but OSB is not recognising the ‘.dvm’ file that i have copied from Jdeveloper.Is there any way to convert the .dvm into its corresponding schema file or i have to create it manually???
pls help!!!!
You have two options for DVM. One, creating a XQuery lookup. Second, creating a Java lookup. The first option is simpler and more efficient. However, you need to manually copy & paste .DVM file content and create an XQuery expression in OSB.
See this article on how to do it:
http://blogs.oracle.com/christomkins/entry/domain_value_maps_in_oracle_se
Hi Amjad!!!
Again seeking help!!!
The DVM case I solved and now m able to use lookup tables from OSB.
The new problem is I am trying to add time zone to my datetime variablein Xquery transformation.I am using the function ‘fn-bea:add-timezone-to-dateTime()’ and passing the dayTimeDuration variable as ‘-PT3H’.
But my transformation is showing error evry time.when i omit the ‘-PT3H’ variable its adding 5:30 to the time which i pass. but I want to add 3Hr to the time variable i pass..
Pls help!!!!
You need to construct dataTimeDuration. String can’t be passed as an argument. Below expression works for me:
fn-bea:add-timezone-to-dateTime(xs:dateTime($inputstring), xdt:dayTimeDuration(“-PT3H”))
You can replace $inputstring with your own variable.
Hi Amjad,
I have got some BPEL processes deployed in EM.I trigger them through OSB.
But now I noticed that some of the instances which are in Rinning or Faulted state are getting retried from 12AM to 4 AM at an interval of every 5min everyday and it is eating up the server memory.
One of the processes is using also while the other is accessing an apps proc.In the process having no correlation only faulted instances are getting tried while running instances are not.
I am using fault binding and fault policies in my composites.
If you have any suggestions!!!!!!
Niva, tough to make a call. Did you find anything useful in Diagnostics log. You may want to enable detailed level logging (through EM console) and examine the log files.
Hi Amjad,
In the logs I can see only the BPEL fault messages and nothing related to the retrial.Anyway, I redeployed the processes and it is not retrying anymore.The problem is gone for now atleast.
Now I am facing one more problem.I want to convert microseconds(a string variable) to corresponding number of days in OSB .Is there any direct function that can be used in this context???
Don’t know of any extension function in particular. I presume you’ve already looked at http://docs.oracle.com/cd/E15523_01/integration.1111/e10224/bp_appx_functs.htm#CJAIABIC.
hi,
while archiving files in file or ftp, we are not able to get the files in the same format as read.ex: classmate.csv. system generates some extensions to the file. can i get the same filename