SOA 12c: Process Large Files Using Oracle MFT & File Adapter Chunked Read Option

SOA 12c adds a new ChunkedRead operation to the JCA File Adapter. Prior to this, users had to use a SynchRead operation and then edit the JCA file to achieve a "chunked read". In this blog, I will attempt to explain how to process a large file in chunks using the SOA File Adapter and some best practices around it. One of the major advantages of chunking large files is that it reduces the amount of data that is loaded in memory and makes efficient use of the translator resources.

"File Processing" means, reading, parsing and translating the file contents. If you just want to move/transfer a file consider using MFT for best performance, efficiency & scalability.

In this example, MFT gets a large customer records file from a remote SFTP location and sends it to SOA layer for further processing. MFT configuration is pretty straight-forward and is out of scope in this entry. For more info on Oracle MFT read here.

SOA 12c offers tight integration with Oracle MFT through the simple to use MFT adapter. If the MFT adapter is configured as a service, MFT can directly pass the file either inline or as a reference to the SOA process. If configured as a reference, it enables a SOA process to leverage MFT to transfer a file.

MFT also provides a bunch of useful file metadata info (target file name, directory, file size etc..) as part of the MFT header SOAP request.

Create a File Adapter:

Drag & drop a File adapter to the external references swimlane of our SOA composite. Follow instructions in the wizard to complete the configuration as shown below. Ensure that you choose the "Chunked Read" operation and define a chunk size - This will be the number of records in the file that will be read in each iteration. For eg., if you have 500 records with a chunk size of 100, the adapter would read the file in 5 chunks.

You will have to create an NXSD schema which can be generated with the sample flat file. The file adapter uses the NXSD to read the flat file and also convert it into XML format.

Implementing the BPEL Process:

Now, create a BPEL process using the BPEL 2.0 specification [This is the default option].

As a best practice, ensure the BPEL process is asynchronous - this will ensure that the "long running" BPEL process doesn't hog threads.

In this case, since we are receiving a file from MFT, we will choose "No Service" template to create a BPEL process with no interface. We will define this interface later with the MFT adapter.

Create MFT Adapter:

Drag and drop an MFT adapter to the "Exposed Services" swimlane of your SOA composite application, provide a name and choose "Service". Now, wire the MFT Adapter service and File Adapter reference to the BPEL process we created. Your SOA composite should look like below;

Processing large file in chunks:

In order to process the file in chunks, the BPEL process invoke that triggers the File Adapter must be placed within a while loop. During each iteration, the file adapter uses the property header values to determine where to start reading.

At a minimum, the following are the JCA adapter properties that must be set;

jca.file.FileName : Send/Receive file name. This property overrides the adapter configuration. Very handy property to set / get dynamic file names

jca.file.Directory : Send/Receive directory location. This property overrides the adapter configuration

jca.file.LineNumber : Set/Get line number from which the file adapter must start processing the native file

jca.file.ColumnNumber : Set/Get column number from which the file adapter must start processing the native file

jca.file.IsEOF : File adapter returns this property to indicate whether end-of-file has been reached or not

Apart from the above, there are 3 other properties that helps with error management & exception handling.

jca.file.IsMessageRejected : Returned by the file adapter if a message is rejected (non-conformance to the schema/not well formed)

jca.file.RejectionReason : Returned by the file adapter in conjunction with the above property. Reason for the message rejection

jca.file.NoDataFound : Returned by the file adapter if no data is found to be read

In the BPEL process "Invoke" activity, only jca.file.FileName and jca.file.Directory properies are available to choose from the properties tab. We will have to configure the other properties manually.

First, let's create a bunch of BPEL variables to hold these properties. For simplicity, just create all variables with a simple XSD string type.

Let's now configure the file adapter properties.

For input, we must first send filename, directory, line number and column number to the file adapter, so the first chunked read can happen. From the return properties (output), we will receive the new line number, column number, end-of-file properties which can be fed back to the adapter within a while loop.

Click on the "source" tab in the BPEL process and configure the following properties. Syntax shown below is for BPEL 2.0 spec, since we built the BPEL process based on BPEL 2.0.

Note: In BPEL 1.1 specification, the syntax was bpelx:inputProperties & bpelx:outputProperties.

Drag & drop an assign activity before the while loop to initialize the variables for the first time the file is read (first chunk) - since we know the first chunk of data will start at line 1 and column 1.

lineNumber -> 1

columnNumber -> 1

isEOF -> 'false'

For the while loop condition, the file adapter must be invoked until end-of-file is reached, enter the following loop condition;

Within the while loop, drag & drop another assign activity to re-assign file adapter properties.

returnIsEOF -> isEOF

returnLineNumber -> lineNumber

returnColumnNumber -> columnNumber

This will ensure that the in the next loop, file adapter would start fetching records from the previous end. For eg., If you have a file with 500 records with a chunk value of 100, returnLineNumber will have a value of 101 after the first loop is completed. This will ensure the file adapter starts reading the file from line number 101 instead of starting over.

Your BPEL process must look like this;

We now have the BPEL process that receives file reference from MFT, reads the large file in chunks.

Further processing like data shaping, transformation can be done from within the while loop.

SOA 12c RCU: Oracle XE 11g TNS listener does not currently know of SID

Recently, I installed Oracle XE 11g database on my windows machine to host my SOA 12c RCU.
Note: Although XE is not a certified database for SOA 12c, it works just fine for development purposes.

Strangely enough, my RCU utility was unable to connect to the database instance. I kept getting the error that "Unable to connect to the DB. Service not available".
I was pretty sure that all my connect parameters were correct.

Also, worth noting is that, I couldn't connect to the DB apex application running @ http://127.0.0.1:8080/apex/f?p=4950

First suspicion was to check the service name, as sometimes during installation, the domain name gets appended to the service name. eg., instead of orcl, it might be registered as orcl.localdomain

A quick look at the listener.ora file revealed that the default service name was indeed XE.

However, when I ran the lsnrctl status command, I could see that the XE service was not listed.

Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\SATANNAM-US\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully.

This is due to the fact that the listener hasn't registered the XE service properly. In my case, restarts of database and listener services didn't help. Remember, as a best practice the listener must always be started ahead of starting the database for it to register the services.

The fix is to manually instruct the database to register the XE service. To do this, login to sqlplus as sysdba and issue the following commands.

> sqlplus / as sysdba
> Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;

Exit sqlplus and restart your OracleServiceXE and listener services.

Now, lsnrctl status command gives the following output;

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\SATANNAM-US\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080)) Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully.

You can see that XE service is now registered and ready. Also note that the http port 8080 is up and running - meaning you can now successfully access the APEX url.

Learner Series: Dynamically load XSL templates

I received a few requests from readers on how to dynamically load XSLT stylesheets in SOA 11g/12c.

This usecase finds importance when you have the following;

• You have one input source (master data) and multiple destinations each of which receives the same data in different formats
• Your destination requires minor changes on the data being sent and you don't want to redeploy the whole process causing downtime to other systems
• You have dynamic partnerlinks to connect to multiple targets each of those targets expect data in a certain format

We will leverage SOA-MDS to store and retrieve our XSL stylesheets dynamically at runtime.

Step 1: Develop your XSLT mappings and test them before you persist them on MDS
Ref: learn how to use MDS in 11g/12c

Once you have your stylesheets in MDS, you can reference them from within your BPEL process using the oramds:/ protocol

Step 2: You don't need to use the "Transform" activity in your BPEL. Use either of the following BPEL XPath Extension Functions to load and process your XSL stylesheets within your "Assign" activity.

ora:processXSLT()
ora:doXSLTransformForDoc()

For example, ora:processXSLT('oramds:/apps/stylesheets/xformOrder.xsl', $inputVariable.payload)

You can further parameterize this expression by using a DVM to store the XSL references and use dvm:lookup to get the first parameter - XML template location.

Learner Series: SOA 12c Share Resources easily using MDS

SOA 12c has simplified the way developers leverage the MDS capabilities.

Developers still have the 11g way of deploying "SOA Bundle" archives to MDS described here.
But if you are on SOA 12c, you get a simpler option.

Once you install SOA 12c quickstart, you get a file-based SOA DesignTime MDS Repository by default. You can choose where to host your MDS root.

Under the Resources window in JDeveloper, expand SOA-MDS IDE connection. You must have a default SOA_DesignTimeRepository. Go to properties and set your MDS root folder.

You can copy your resources that you want to reuse/share to your MDS root folder and reference them within your SOA composite by using the oramds:/ protocol. eg., oramds:/apps/xsd/employee.xsd

Now to create a runtime MDS repository, first create a DB MDS connection to your SOA instance using your prefix_MDS schema [prefix is what you specified during RCU config].

From the Resources window -> IDE connection panel, right click SOA-MDS and create a new SOA-MDS connection. Provide a unique connection name, choose Connection type as "DB Based MDS", select the MDS connection created above and choose the "soa-infra" partition.

Note: SOA quickstart by default uses Java derby database (no DB MDS capabilities). You must either have a compact domain installation or a full-fledged SOA installation (your staging / test / production server).

To deploy the design time MDS (file-based) resources to DB based runtime MDS repository, right click on the design time repository and choose Transfer. The wizard will prompt you for the runtime MDS to which the resources must be transferred to. Choose the resources you need to transfer and your DB MDS connection and click on Transfer.

Learner Series: Handling Dynamic Arrays in SOA 12c (BPEL 2.0)

Dynamic arrays are always tricky to handle within a BPEL process. Every now and then we encounter XML schemas that have generic name-value pair arrays that are unbounded. The challenge is assigning data to and from these dynamic arrays as there are no concrete XML target elements at runtime. Hence, the XML elements must be generated first before assigning values to them.

There are multiple ways this problem can be solved.

For one, you can choose XSLT over a simple assign. XSLT wields more granular control over how the XML elements are handled. You can instruct the XSL processor to loop over dynamic array list and assign values. This of course requires some level of XSLT skill (Although JDeveloper presents an easy XSL mapper, I prefer fiddling with the source) and in certain cases, you may have to pass some BPEL variables (properties) to XSLT in additions to the source and target variables.

Second option would be to use an XML literal (called XML fragment in BPEL 1.1) within your assign activity. Here you can pre-form the XML literal, assign it to the target and then map the values. Not so sophisticated and if you are not careful with namespaces, this could cause a lot of mapping troubles.

Thirdly, with BPEL 2.0 you can simply add an attribute to your copy action in your assign activity to achieve the same. Most simplest of all.

Since this is a learner series, let's get into some details on how to go about this;

Let's first understand the root-cause of the error;

The problem with assigning dynamic arrays within BPEL is that, for the first element in the array, since the XML element is always "available", the copy happens successfully.
However, starting with the second element, all copy rules within assign would fail due to a "selection failure" with the following error because the dynamic array XML elements are still not formed or is empty.

"Exception is thrown because the to-spec at line 140 is evaluated to be empty"
or
"Exception is thrown because the from-spec at line 160 is evaluated to be empty"

depending on whether you are trying to copy to a target or copy from a source.

Your XML schema containing dynamic array may look something like this;



Your typical assign would like the following - you will have to manually let BPEL know which element the values must be mapped - [1], [2], [3] .... [n]. Just append this to your root XML element which contains the dynamic array. In this case;



Now, starting with the second copy rule within your assign, right-click on the rule item and select "ignoreMissingFromData" or "insertMissingToData" depending on whether your dynamic array is being read or written to respectively.



This action would add a flag (attribute) to the copy rule instructing BPEL to handle the dynamic XML element - either ignore or insert.

<copy bpelx:insertMissingToData="yes">
or
<copy bpelx:ignoreMissingFromData="yes">

Happy BPELing...

SOA 12c Compact Domain DB Based MDS

With the release of SOA 12c, developers can now have full-fledged SOA 12c running on their desktops/laptops on the integrated WebLogic server. This is a great news for development community - as one can download the SOA/BPM 12c quickstart which comes pre-configured with JDeveloper and integrated WLS running SOA/BPM.

Some developer productivity benefits include;
1) You don't need a license for quickstart installs (for development & evaluation purposes)
2) Single jar that will install & configure WebLogic domain, SOA, JDev and everything you need to get going

However, there are some limitations with the default quickstart install. For one, the quickstart installation runs on top of Java Derby DB; and the SOA MDS is a file-based repository by default. Although for normal development purposes this may not be a big challenge, it could be an issue if you want to leverage some features such as;

1) Run-time modification of business rules
2) B2B, MFT & ESS
3) SOA Composer
4) BAM
5) BPM Composer etc.. which require a Oracle database to run.

However, there are other ways to configure your development environment to run on top of Oracle database - therefore you can have a DB based SOA MDS.

In this post, I will illustrate how to install and configure a SOA 12c compact domain for development purposes.

Pre-Requisites:

1) Download Java 7. According to the certification matrix jdk 1.7.0u55+ is supported - here
2) Download Oracle XE 11g Database - here
3) Download SOA 12.1.3 quickstart distribution - here
4) Optional components such as B2B, MFT, can be downloaded if required
Download MFT - here
Download B2B - here

Install java 7 on your environment. Note: By default on windows jdk will choose a location under "Program Files". In certain cases, I have seen the space in folder location cause unknown issues. Ensure java is installed on a location without space in the folder name eg., C:\Java

SOA 12c Quickstart Installation:

Unzip the fmw_12.1.3.0.0_soaqs_Disk1_1of1.zip

Open terminal (command prompt) as administrator
Ensure JAVA_HOME and PATH environment variables are set and points to the jdk installed
set JAVA_HOME=C:\Java\jdk1.7.0_71
set PATH=%PATH%;C:\Java\jdk1.7.0_71\bin

Step 1:
Execute the following command to install the SOA 12c quickstart
java -D64 -jar fmw_12.1.3.0.0_soa_quickstart.jar

Step 2: Provide a oracle home where SOA 12c must be installed

Step 3: Ensure the validations are okay

Step 4: Review the installation summary - provides the list of features/components installed as part of the quickstart distribution

Step 5: Review the installation progress

Step 6: Once the installation succeeds, click Finish.

At this stage, quickstart can be used if you are not looking for features such as mentioned above. Just open the JDeveloper, right click on the IntegratedWeblogicServer to create and start domain. JDeveloper takes care of creating a WLS domain and configuring it automatically.

Follow through for a compact domain installation.

If you need additional components such as B2B, BPM or MFT now is the time to install them. Let's install B2B.

Installing Oracle B2B:

Step 1:
Unzip fmw_12.1.3.0.0_b2b_Disk1_1of1.zip and execute the following command to install B2B
java -D64 -jar fmw_12.1.3.0.0_b2bhealthcare.jar

Step 2: Choose the same Oracle home that was used to install the SOA 12c quickstart

Step 3: Choose B2B or Healthcare profiles depending on requirement. Let's choose B2B

Step 4: Follow the instructions and click Finish to close the B2B installation wizard

 

I haven't detailed the MFT installation. However, should you need, it is a very straightforward installation.

Installing XE 11g Database:

Unzip the OracleXE112_Win64.zip and follow the instructions to install XE database.

Step 1: Execute setup.exe

 Step 2: Accept the license agreement

Step 3: Choose the destination folder where XE must be installed

Step 4: Enter the SYS password

Step 5: Review and click Install

Step 6: Click Finish to finish the database installation

Creating RCU schemas for compact domain:

Step 1: Execute rcu.bat from the following location - $ORACLE_HOME\oracle_common\bin

Step 2: Leave the default selection on Create Repository and click Next

Step 3: Provide the database connect information and click next

Step 4: Ignore the warning. XE is not a "certified" database but will work fine in development environments.

Step 5: Default prefix is DEV. You can change this if you need. Select the SOA Infrastructure schema from the list which will select the required schemas

Step 6: Provide a password that will be used for all the RCU schemas

Step 7: Here you can select the size for the database profile. Default is small and should be fine for development environments. Optionally you can choose to enable/disable healthcare integration

Step 8: Follow the instructions and proceed with creation of database schemas for our compact FMW domain. Click close in the final summary screen.

Create and Configure Compact SOA Domain:

In this section let's create and configure a compact SOA domain.

To enable compact domain option, set the following environment variable before executing the config.cmd script

> set CONFIG_JVM_ARGS=-Dcom.oracle.cie.config.showProfile=true

> cd $ORACLE_HOME\oracle_common\common\bin

> config.cmd

Step 1: Choose the "Create a new compact domain" option and provide a domain name/location

Step 2: This is an important step where you must choose the templates with which the compact domain will be created. Choose all the required components. Note that the templates would show up based on the installations in the current domain.

Step 3: Choose a folder location where applications would reside

Step 4: Provide a administrator username and password.

Step 5: Choose Development mode / Production Mode (basically if you choose production mode, you will be prompted for the administrator username and password every time you start the weblogic server). Choose the JDK that we installed earlier which will be picked up by default.

Step 6:Provide the database details and click Get RCU Configuration. Once you get a success message proceed to next step.

Step 7: In the JDBC component schema page, select all the schema components and click next

Step 8: A test would run against the database for the schema connectivity. Once you get a success for all the components proceed further.

Step 9: For a compact domain with a "Admin" only server which will host all the middleware services leave the defaults on step 9 and click next. If you have a need to update the configuration such as adding managed servers, update default ports you can check the appropriate configuration and update.

Step 10: Click Create to create a compact SOA domain

Step 11: Ensure the domain creation succeeds and click Finish to close the config wizard.

That completes the creation of a SOA compact domain.

Now, if you start the weblogic server by issuing startWebLogic.cmd command, you will notice that the Java DB instance would start automatically. Since this is a compact domain and it will run off the Oracle XE database instance, we don't need the Java DB.

Start the weblogic with the noderby flag to prevent the Java DB from starting up.

> cd $ORACLE_HOME\user_projects\domains\compact_domain\bin

> startWebLogic.cmd noderby

Once the server starts up, establish connectivity to all the services to test the installation;

WLS Console: http://localhost:7001/console

EM Console: http://localhost:7001/em

SOA Composer: http://localhost:7001/soa/composer

B2B Console (if installed): http://localhost:7001/b2bconsole

BAM Console: http://localhost:7001/bam/console

One advantage with compact domain is that you can also create a integrated weblogic domain from JDeveloper which could be handy for a lot of development activities.