Handling Large Payload Files

Problem:
Want to create a BPEL process to read, transform and translate a large payload file (e.g. size 1GB)?

Thoughts:
Reading and traversing large payload file was always a problem in SOA 10g. Supposedly Maximum limit for such operations are for less than 7MB files in 10g.
Solution to such problems are answered in 11g where you can read and transform huge payload files.

Solution:
Suppose you have a CSV file incoming from source directory which you want to transform to a Fixed length file. Keep the source and the destination schema (XSD) ready. Also prepare the Transformation file (XSL) and keep it handy.
For accomplishing this, you will only be using one File Adapter which will take care of your read, transform and write - so all the three operations takes place under single I/O interaction.

Follow the steps below for making it possible:
1) Drag and drop a file adapter in the external references swim lane. (For e.g. Give name as FileMove.)
2) Select Synchronous File Read
3) Give any dummy value for File physical path and File Name. It will be changed manually later.
4) Select Native format translation is not required (Opaque Schema)
5) Outbound File Adapter is now configured, click on Finish
6) Open the relevant .jca file (in this case FileMove_file.jca)
7) Ensure that className should be
"oracle.tip.adapter.file.outbound.FileIoInteractionSpec"
8) Add extra parameters as shown in below file:
<adapter-config name="FileMove" adapter="File Adapter"
xmlns="http://platform.integration.oracle/blocks/adapter/fw/metadata">
<connection-factory location="eis/FileAdapter" adapterRef=""/>
<endpoint-interaction portType="FileMove_ptt" operation="FileMove">
<interaction-spec
className="oracle.tip.adapter.file.outbound.FileIoInteractionSpec">
<property name="SourcePhysicalDirectory" value="testDir1"/>
<property name="SourceFileName" value="test1"/>
<property name="SourceSchema" value="xsd/source-csv.xsd"/>
<property name="SourceSchemaRoot value="Root-Element"/>
<property name="SourceType" value="native"/>
<property name="TargetPhysicalDirectory" value="testDir2"/>
<property name="TargetFileName" value="test2"/>
<property name="TargetSchema" value="xsd/destination-fixedLength.xsd"/>
<property name="TargetSchemaRoot value="Root-Element"/>
<property name="TargetType" value="native"/>
<property name="Xsl value="xsl/SourceToDestination.xsl"/>
<property name="Type" value="MOVE"/>
</interaction-spec>
</endpoint-interaction>
</adapter-config>
9) Save the file and deploy the process.

Note:
This will works only if all the records in the data file are of the same type.

Exporting ESB Metadata

Requirement:
Exporting entire ESB metadata

Solution:
Follow the steps below to achieve the result:
1) Login to SOA Server (10g) from backend viz. using Putty, and set your environment.
2) Navigate to <SOA_HOME>/integration/esb/bin
3) Run the export.sh command to export entire ESB metadata like:
./export.sh ESBmetadata.zip
4) You will see a output message as:

Setting Protocol = http
Dec 29, 2010 11:14:16 AM oracle.tip.esb.lifecycle.HttpHandler logInfo
INFO: Exporting metadata from the server [host=oradev,port=7777,user=oc4jadmin] to
file "/u01/app/oracle/product/10g/OracleAS_3/integration/esb/bin/ESBmetadata.zip"
Dec 29, 2010 11:14:16 AM oracle.tip.esb.lifecycle.HttpHandler main
INFO: Export completed successfully!!!

Note:
Steps shown above are done on SOA Server 10g running on Linux environment.

ORABPEL Spying-Part2

Requirement:
To get all BPEL instances and their duration that run longer than 'n' seconds

Solution:
SELECT
process_id, creation_date,
SUBSTR(modify_date-creation_date,12) Duration,
SUBSTR(REGEXP_SUBSTR(title, '[^ ]+', 1, 2), 2) InstanceId
FROM
cube_instance
WHERE
TO_CHAR(creation_date, 'YYYY-MM-DD HH24') >= '<Date_value>'
AND
TO_CHAR(creation_date, 'YYYY-MM-DD HH24') <= '<Date_value>'
AND
(modify_date-creation_date) > '0 0:0:n.0'
AND
process_id IN ('<Name of Process>')
ORDER BY
modify_date DESC

Example:
Here n=45 seconds

SELECT
process_id, creation_date,
SUBSTR(modify_date-creation_date,12) Duration,
SUBSTR(REGEXP_SUBSTR(title, '[^ ]+', 1, 2), 2) InstanceId
FROM
cube_instance
WHERE
TO_CHAR(creation_date, 'YYYY-MM-DD HH24') >= '2010-10-12 15'
AND
TO_CHAR(creation_date, 'YYYY-MM-DD HH24') <= '2011-01-01 21'
AND
(modify_date-creation_date) > '0 0:0:45.0'
AND
process_id IN ('TestProjectBPEL')
ORDER BY
modify_date DESC

ORABPEL Spying-Part1

Requirement:
Want to get the information on currently running processes - Shortest and Longest running instances on server?

Solution:
Below query will give you the desired result:

SELECT * FROM (
SELECT
bpel_process_name AS "ProcessName",
TO_CHAR(MIN(creation_date),'YYYY-MM-DD HH:MI') AS "EarliestDate",
COUNT(*) AS "TotalRunningProcesses",
TO_NUMBER(SUBSTR(MIN(sysdate-creation_date), 1,
INSTR(MIN(sysdate-creation_date), ' '))) AS "ShortestRunning (Days)",
SUBSTR(MIN(sysdate-creation_date),
INSTR(min(sysdate-creation_date),' ')+1,8) AS "ShortestRunning (Hours)",
TO_NUMBER(SUBSTR(MAX(sysdate-creation_date), 1,
INSTR(MAX(sysdate-creation_date), ' '))) AS "LongestRunning (Days)",
SUBSTR(max(sysdate-creation_date),
INSTR(MAX(sysdate-creation_date),' ')+1,8) AS "LongestRunning (Hours)"
FROM ORABPEL.bpel_process_instances
WHERE state = 1
GROUP BY bpel_process_name
ORDER BY "EarliestDate" DESC
)

NOTE: Query for Oracle BPEL Process Manager 10g (10.1.3.x)

HAPPY NEW YEAR

I wish all the readers a very and prosperous Happy New Year 2011.

Hope to see new things coming this year from Oracle.

JDeveloper: TODO

Problem:
Adding Tasks to JDeveloper to keep track of development code

Thoughts:
We always need some kind of Task handler/reminder for our tasks during development so that if that is present in the source code, we can refer that in future (just like Reminder) viz. Need to add a Variable, Delete this variable, Temporary variable, Handle the error etc.

Solution:
Jdeveloper comes with a very good utility - "TODO".
You can write simply TODO as comment (Note: this should be in Caps always) in the source code.
Below picture depicts the same:



Just you need to write //TODO followed by your comments in the source code.

Later through View -> Tasks, you can view all the TODO's in your project. Below picture depicts the same.



So at later point if you want to revisit the code, just double click on the Task line of your interest, it will navigate you to the desired line of code.


Happy Learning.