ESB Error: I Hate Spaces

In this blog entry I am going to deal with a very common problem related to ESB which people experience.
Problem:
The error which I am discussing it here occurs when you click on the services already registred on ESB Console.

Create a simple ESB project, deploy it to the Integration Server. Login to the ESB Control. When you click on any of the services in ESB Control, you receive the following pop-up error:

"The metadata XML that was processed does not comply with the ESB Metadata XML Schema.
Cause: Invalid text 'esb:///ESB_Projects/ESB_TEST/PollDB.wsdl' in element: 'wsdlURL'.

If you are manually editing the Import Files, ensure that the metadata is valid and well formed. Otherwise, this is an Internal Error. Contact Oracle Support."

Solution:
The solution is pretty simple. This error occurs when the Application name contains spaces. The application name in JDeveloper must not contain spaces.

EXAMPLE1: ESB Test Appl (Wrong Application Name)
EXAMPLE2: ESB_Test_Appl (Right Application Name)

Bingo......

Keep Learning with me...

ORABPEL-13080

During working on MQ Adapter for one of the clients project, I faced this problem so thought of posting it in my blog.

Error Summary:
org.collaxa.thirdparty.apache.wsif.WSIFException: esb:///ESB_Projects/Jdev-Abhishek_ESP200011/ESP_PLF_JLE_JLN_IncomingMQAdapter.wsdl [
Enqueue_ptt::Enqueue(schedule) ] - WSIF JCA Execute of operation 'Enqueue' failed due to: Error.
"[MessageProducer] Error while putting message in PORTAL.PUBLISHER.ERRORS "
; nested exception is:
ORABPEL-13080
Error.
"[MessageProducer] Error while putting message in PORTAL.PUBLISHER.ERRORS "
Contact oracle support if error is not fixable.

Solution:
ORABPEL-13080 error is associated with the MQ Adapter. I have identified two possible cause for this error:

1) Queue is full.
2) Queue not accessible.

So to get rid of this error, either check your MQ Adapter connection details and priviledge or Clear the unwanted messages from the Queue.

Thats it...Keep Learning...

Using orcl:sequence-next-val

Recently this question was asked to me as a comment to one of my post. I have replied to the comment. I also felt to post this as a blog so that it can prove beneficial to others. Here it goes...

INTRODUCTION:
The function orcl:sequence-next-val takes 2 parameters. It return the next value of an Oracle sequence. It is similar to sequence generation what we see in Oracle SQL.

How to proceed:
First of all you need to drag that function to the tranformation swimlane.
Create a database sequence in Database, say,TEST. This will be the first parameter for the orcl:sequence-next-val function.

The second parameter will be your database connection details, i.e. the database in which your source table is defined.
Suppose your database is having details as:
Username: abhi
Password: abhishek
Host: 172.128.5.13
Port: 1521
SID: Oracle

so the second parameter will look like this:
'jdbc:oracle:thin:abhi/abhishek@172.128.5.13:1521:Oracle'

or for the second parameter you can add a datasource entry in data-sources.xml file under soa_home\j2ee\oc4j_soa\config.

Say the datasource name is BPEL_DBS. so the function will look like,
orcl:sequence-next-val('TEST','jdbc/BPEL_DBS')

After doing this, map it to the destination field.

Your are done...Happy Learnings...

File Adapter : Hidden Treasure-2

Getting File Size

I am assuming that people reading this blog know how to get the File name and the input directory of the file. In this blog entry I will demonstrate an another aspect of File Adapter i.e. getting the input file size.
Note:- If you don't know about getting file name & input directory, refer to my blog entry dated 14th June 2008.

Introduction:
If you see the fileAdapterInboundHeader.wsdl file in Jdeveloper, you will notice that this wsdl is containing only two elements i.e. fileName and directory. Now If you goto the BPEL Console and look closely at the variable part (which contains file name and directory information), you will notice that at runtime Assign activity is displaying 5 elements. So I felt that somehow I can get the file size too. Its pretty simple.

Solution:
To acheive it, just you have to modify the fileAdapterInboundHeader.wsdl file. Note that the fileAdapterInboundHeader.wsdl is a read-only file so to modify it, you have to remove the read-only permission. After removing the permission you can edit the code.

After change, the code will look like this:


Note the highlighted element "size" below the directory element. After doing the changes, apply the read-only permission back to the file(not necessary).

Now you will be able use the size attribute in your assign activity.

Pretty cool stuff...right..

Have a Happy Happy learning...

BPEL : PURGING INSTANCES

Here I am providing various flavors of Purging BPEL instances.

Following options will be covered:

1) Purges instance for a particular domain, for particular state
and for particular specified period from the BPEL dehydration store.
SUBJECT:PURGE INSTANCE-1

2) purges instance for a particular domain,version and between specified period for any state other than state 0 and 1 from the BPEL dehydration store.
SUBJECT:PURGE INSTANCE-2

3) Purges all the instance in one go from the dehydration store. (Handle with care)
SUBJECT:PURGE INSTANCE-3

All you need to do is, run the code under ORABPEL Schema. So scroll down and enjoy the show.

Happy Learnings.....

BPEL: Purging Instances-3

INTRODUCTION:

This script purges all the instance in one go from the dehydration store. This script is useful in a developement environement. It is the faster way by which you can clear the dehydration store.

Guideline for using this code:
1. Don't run this script until you are very much sure of deleting all the instances from the dehydration store.
2. First stop the BPEL instance via Enterprise manager then run this script.


truncate table cube_instance;
truncate table cube_scope;
truncate table work_item;
truncate table wi_exception;
truncate table document_ci_ref;
truncate table document_dlv_msg_ref;
truncate table scope_activation;
truncate table dlv_subscription;
truncate table audit_trail;
truncate table audit_details;
truncate table sync_trail;
truncate table sync_store;
truncate table dlv_message;
truncate table invoke_message;
truncate table ci_indexes;
alter table cube_instance deallocate unused;
alter table cube_scope deallocate unused;
alter table work_item deallocate unused;
alter table wi_exception deallocate unused;
alter table document_ci_ref deallocate unused;
alter table document_dlv_msg_ref deallocate unused;
alter table scope_activation deallocate unused;
alter table dlv_subscription deallocate unused;
alter table audit_trail deallocate unused;
alter table audit_details deallocate unused;
alter table sync_trail deallocate unused;
alter table sync_store deallocate unused;
alter table dlv_message deallocate unused;
alter table invoke_message deallocate unused;
alter table ci_indexes deallocate unused;
alter table cube_scope enable row movement;
alter table cube_scope shrink space compact;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;
alter table cube_instance enable row movement;
alter table cube_instance shrink space compact;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;
exec dbms_utility.analyze_schema('ORABPEL', 'Compute');


Happy Learnings....

BPEL: Purging Instances-2

INTRODUCTION:

This procedure purges instance for a particular domain,version and between specified period for any state other than state 0 and 1 from the dehydration store.
Note:This procedure is calling another procedure 'delete_ci'.Procedure 'delete_ci' deletes a cube instance and all rows in other Collaxa tables that reference the cube instance.

-- ** It is possible to purge the instances all at once, but by doing that it will remove all the instances. This is nice during development. But in a production environment you want to have a controlled way. This procedure will do the work.

Guidlines for using this code:
1. Don't call this procedure until you are too sure of deleting the instances.
2. First stop the BPEL instance via Enterprise manager then run this procedure.
3. For running the code, pass the date fields in oracle standard format i.e DD-MON-RR format.

CODE:
CREATE OR REPLACE PROCEDURE sp_purge_instances_per_process (
p_domain_name IN VARCHAR2,
p_process_name IN VARCHAR2,
p_version IN VARCHAR2,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2
)
/*

CODING BY: ABHISHEK SAURABH
CREATION DATE: 15-OCTOBER-2007
LAST MODIFIED: 15-OCTOBER-2007

*/
IS
CURSOR c (
v_domain_name IN VARCHAR2,
v_process_name IN VARCHAR2,
v_version IN VARCHAR2,
v_from_date IN VARCHAR2,
v_to_date IN VARCHAR2
)
IS
SELECT cie.cikey cikey, dmn.domain_id domain_id,
cie.process_id process_id, cie.revision_tag revision_tag,
cie.creation_date creation_date, cie.domain_ref domain_ref
FROM cube_instance cie, domain dmn
WHERE cie.domain_ref = dmn.domain_ref
---- the name of the domain
AND upper(dmn.domain_id)=upper(v_domain_name)
AND cie.creation_date between v_from_date and v_to_date
AND upper(cie.process_id)=upper(v_process_name)
AND cie.revision_tag LIKE v_version;
BEGIN
FOR r IN c (p_domain_name, p_process_name, p_version, p_from_date,p_to_date)
LOOP
DBMS_OUTPUT.put_line ( 'Purge '
|| r.process_id
|| '('
|| r.revision_tag
|| ')'
|| ' created on '
|| TO_CHAR (r.creation_date,
'YYYY-MM-DD HH24:MI:SS')
);
collaxa.delete_ci (r.cikey);

-- -- also workflow tasks --
DELETE FROM wftask wfn
WHERE wfn.instanceid = r.cikey;
END LOOP;
-- invoked messages

DELETE FROM invoke_message ime
WHERE ime.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND ime.state > 1
AND upper(ime.process_id)=upper(p_process_name)
AND ime.revision_tag LIKE p_version
AND ime.receive_date between p_from_date and p_to_date;

DBMS_OUTPUT.put_line ('-> #invoke msg ' || SQL%ROWCOUNT);

-- callback messages
DELETE FROM dlv_message dme
WHERE dme.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND dme.state > 1
AND upper(dme.process_id)=upper(p_process_name)
AND dme.revision_tag LIKE p_version
AND dme.receive_date between p_from_date and p_to_date;

DBMS_OUTPUT.put_line ('-> #callback msg ' || SQL%ROWCOUNT);
END sp_purge_instances_per_process;


It seems...coding has become my habit. :))

BPEL: Purging Instances-1

INTRODUCTION:
This procedure purges instance for a particular domain,for a particular state and for particular specified period from the BPEL dehydration store.

Note: This procedure is calling another procedure 'delete_ci'.
Procedure 'delete_ci' deletes a cube instance and all rows
in other Collaxa tables that reference the cube instance.
***********************************************************************
Guideline for running the code:
1. Pass the date fields in oracle standard format i.e DD-MON-RR format
2. Pass the state code depending upon the requirement.

STATE CODE----------STATE NAME
0----------------------Initiated state
1----------------------Open,Running state
2----------------------Open, Suspended state
3----------------------Open,Faulted state
4----------------------Closed,Pending-Cancel state
5----------------------Closed,Completed state
6----------------------Closed,faulted state
7----------------------Closed,Cancelled state
8----------------------Closed,aborted state
9----------------------Closed,Stale state
***********************************************************************
CODE FOR PURGING:
CREATE OR REPLACE PROCEDURE sp_purge_instances_per_state (
p_domain_name IN VARCHAR2,
p_state IN NUMBER,
p_from_date IN VARCHAR2,
p_to_date IN VARCHAR2
)
/*
CODING DONE BY: ABHISHEK SAURABH
CREATION DATE: 12-OCTOBER-2007
LAST MODIFIED: 18-OCTOBER-2007

Note:
1. Don't call this procedure until you are too sure of deleting the instances.
2. First stop the BPEL instance via Enterprise manager then run this procedure.
*/

IS
CURSOR c (
v_domain_name IN VARCHAR2,
v_state IN NUMBER,
v_from_date IN VARCHAR2,
v_to_date IN VARCHAR2
)
IS
SELECT cie.cikey cikey, dmn.domain_id domain_id,
cie.state state,cie.creation_date creation_date, cie.domain_ref domain_ref
FROM cube_instance cie, domain dmn
WHERE cie.domain_ref = dmn.domain_ref
---- the name of the domain
AND upper(dmn.domain_id)=upper(v_domain_name)
AND cie.creation_date between v_from_date and v_to_date
AND cie.state=v_state;
BEGIN
FOR r IN c (p_domain_name, p_state, p_from_date,p_to_date)
LOOP
DBMS_OUTPUT.put_line ( 'State code '
|| r.state
|| ' created on '
|| TO_CHAR (r.creation_date,
'YYYY-MM-DD HH24:MI:SS')
|| ' is purged.'
);
collaxa.delete_ci (r.cikey);
-- also workflow tasks --
DELETE FROM wftask wfn
WHERE wfn.instanceid = r.cikey;
END LOOP;
-- invoked messages
DELETE FROM invoke_message ime
WHERE ime.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND ime.state > 1
AND ime.receive_date between p_from_date and p_to_date;
DBMS_OUTPUT.put_line ('-> #invoke msg ' || SQL%ROWCOUNT);
-- callback messages
DELETE FROM dlv_message dme
WHERE dme.domain_ref IN (SELECT dmn.domain_ref
FROM domain dmn
WHERE upper(dmn.domain_id)=upper(p_domain_name))
AND dme.state > 1
AND dme.receive_date between p_from_date and p_to_date;
DBMS_OUTPUT.put_line ('-> #callback msg ' || SQL%ROWCOUNT);
commit;
END sp_purge_instances_per_state;


Happy Learnings....

Using "orcl:query-database" in transformation

In this blog, I am going to show the way to tackle dynamic 'where' condition in the function orcl:query-database. It returns a node-set by executing the SQL query against the specified database.

Just a brief introduction about how this function works:-
Signature:
orcl:query-database(sqlquery as string, rowset as boolean, row as boolean, datasource as string)

Arguments:
sqlquery – The SQL query to perform.
rowset – Indicates if the rows should be enclosed in a <rowset> element.
row – Indicates if each row should be enclosed in a <row> element.
datasource – Either a JDBC connect string (jdbc:oracle:thin:username/password@host:port:sid) or a JNDI name for the database.

Example1:
orcl:query-database("select empno from emp
where empno='50'",false(),false(),"jdbc/DbConnection")

Now coming back to the main agenda about giving dynamic value to the where clause. Its pretty simple. Just a little bit trick.

Use the function with the concat function, as:
Example2:
orcl:query-database(concat("select empno from emp where empno=",/tns:Root-Element/tns:Data/:tns:Empno),false(),false(),"jdbc/DbConnection")

Bingooooooooo....

Happppy Learning....

BPEL : ORABPEL-09903

I am back with ORABPEL error topic again. This time I will show you how to get rid of the error ORABPEL-09903.

Problem:
Error shown below is reported sometimes when you deploy a BPEL project:

C:\jdevstudio10133\jdev\test\testApps\build.xml:79: A problem occured while connecting to server using port "7777":
bpel_testApps_1.0.jar failed to deploy.
Exception message is: ORABPEL-09903
Could not initialize activation agent.

Solution:
This type of error happens when the JNDI entry is not proper or you have insufficient priviledges.
Check for the following things to get rid of this error:

1) Check the JNDI names, as they may be pointing to a different location. Test it.

2) Test that the user connecting to the queue has enough priviledges.

In my case, I was not having proper priviledges for accessing the queue.


Now compile and try to deploy your process.

Happy Learning...

Database Adapter: Handling Twins

Problem: I had faced this problem in past so thought of sharing this to all. You will encounter a peculiar problem while handling Database schema with the same name containing same table name, for the different systems.

Lets say,
System1:
System name: sys1
Schema name: schema1
Table name: Table1

System2:
System name: sys2
Schema name: schema1
Table name: Table1

Everything will work fine. But problem starts after making the project, i.e. if you want to see the wizard for Database adapter by opening the .esbsvc file, you will become mad to see that the table information is not visible in the window. Actually Jdeveloper automatically deletes descriptor file and some contents from Toplink mappings.

Solutions: After doing some research over this I found out some solutions for handling such types of problem.

Solution1:
1) Create one project against system1 and do the necessary adapter configuration for that database.

2) Now Create again a second project against System2 and do the necessary adapter configuration for that database. (Because the
databases are on different hosts, you use different database connections.)

3) Then create a third project, but do not use the Adapter Configuration Wizard. Instead, copy the BPEL or ESB artifacts (WDSL, XSD, and toplink_mapings.xml) from projects one and two.

4) Only Deploy the third project.

Bingo...It works fine.

Solution2:
This is very lengthy solution. You have to goto the project folder and you have to manually update the .mwp files present under Toplink folder. Also, you need to create descriptor folder containing class descriptor.xml file, under Toplink folder. To do this job, it involves lot of time and manual coding headache.

Actually to crack the problem, solution2 was my first approach. Later on, I found out much easier and better approach i.e. solution1. :)
So, I will personally recommend everybody to use the solution1 technique to get rid of such problem.

Happy learning...