States meaning for Instances

This is an enhanced version of my own blog dated 08-Aug-2008, BPEL: Purging Instances-1, which suggests about various States stored in Dehydration store. Earlier information was for SOA 10g interface States stored in Cube_instances, This blog is about the same but for SOA 11g.

Problem: To know the meaning of States stored under CUBE_INSTANCE table of SOA_INFRA Schema

Explanation:

Value: 0

Meaning: Initiated

Description: for an instance that has just been newly created

Value: 1

Meaning: Open & Running

Description: for an instance that has been created and has active activities executing

Value: 2

Meaning: Open & Suspended

Description: for an instance that is unavailable & is in suspended state. No action can be taken for this instance until the instance has returned to the running state

Value: 3

Meaning: Open & Faulted

Description: for an instance that has an activity that has thrown an exception. When an activity throws an exception, the instance is flagged as being in an exception state until the exception is properly caught and handled

Value: 4

Meaning: Open & Pending

Description: for an instance that is in mid of cancellation. An instance is said to be pending cancellation state - it may happen that the process is quite big & complicated that the entire cancellation process may takes anywhere from seconds/minutes/days. An instance may not be acted upon during this time

Value: 5

Meaning: Closed & Completed

Description: for an instance that has been completed i.e. All activities belonging to this instance have also been completed

Value: 6

Meaning: Closed & Faulted

Description: for an instance that has an activity that has thrown an exception while the instance is being cancelled

Value: 7

Meaning: Closed & Cancelled

Description: for an instance that has been cancelled. All activities belonging to this instance have also been cancelled

Value: 8

Meaning: Closed & Aborted

Description: for an instance that has been aborted due to administrative control. All activities belonging to this instance are also moved to the aborted state

Value: 9

Meaning: Closed & Stale

Description: for an instance who's process has been changed since the process was last accessed. No actions may be performed on the instance. All activities that belong to this instance are also moved to the stale state

Value: 10

Meaning: Non Recoverable

Description: for an instance that has failed and is marked as non recoverable

And here I come with more exciting knowledge and learnings.

Apologies for not being there for quite a few months. During this period I have gathered lot of experiences on Oracle Fusion products and its implementation which I will sharing to you all, watch out the space.

And yes, although I am late, as always I wish all my blog readers a very happy and prosperous New Year 2013.

Connecting to SQL Server using SQL Developer

Problem:
Need to connect to SQL Server Database using SQL Developer

Solution:
This is an option available under SQL Developer which will allow us to connect to Third-Party Database systems. If we configure our SQL Developer, we can connect to SQL Server database.

Below mentioned are the steps which will ensure SQL server connectivity:
1) You need to have a Jar file in place for SQL server.
2) Download jTDS SQL Server driver - an open source JDBC driver from:-'http://sourceforge.net/projects/jtds/' to your local drive.
3) Unzip the folder
4) Open SQL Developer
5) Goto Tools -> Preferences -> Database -> Third Party JDBC Drivers
6) Click 'Add Entry', navigate to the unzipped folder
7) Select jtds-1.2.5.jar and click OK
8) Now goto the connections window and create a new connection
9) Give some connection name, enter proper username and password
10)Select 'SQLServer' Tab
11)Enter proper Hostname and leave Port as 1433 (Default). Change the port number only if it is running on running on some different port.
12)Click on Retrive Database button to fetch the Database name you want to connect
13) Test the connection
14) SUCCESS, Save the connection and press the connect button

It works!!!

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