Friday, May 07, 2010

java.sql.SQLException: [POL-5205] column SEQ not found & ORA-00904.

You deployed an ESB process and the instance is not visible in the ESB console.

In the log files you are getting following errors

at oracle.tip.esb.monitor.manager.database.DBActivityMessageStore.persistMessage(DBActivityMessageStore.java:340)
at oracle.tip.esb.monitor.manager.database.DBActivityMessageStore.store(DBActivityMessageStore.java:128)
at oracle.tip.esb.monitor.manager.ActivityMessageReceiver.handleMessage(ActivityMessageReceiver.java:83) ... 7 more
Caused by: java.sql.SQLException: [POL-5205] column SEQ not found

Caused by: oracle.tip.esb.monitor.MonitorException: Due to the error "ORA-00904"

This issue mainly occurs if you have your ORAESB schema gets corrupted some how.

In that case from ESB_ACTIVITY these columns SEQ and SUB_FLOW_SEQ gets deleted or gets corrupted.

The ESB_ACTIVITY should contain the following

desc ESB_ACTIVITY
Name Null? Type
----------------------------------------- -------- ---------------------------
ID NOT NULL NUMBER
FLOW_ID NOT NULL VARCHAR2(256)
SUB_FLOW_ID VARCHAR2(48)
SEQ NUMBER
SUB_FLOW_SEQ NUMBER(3)
BATCH_ID VARCHAR2(48)
SOURCE VARCHAR2(48)
OPERATION_GUID VARCHAR2(48)
TIMESTAMP NOT NULL NUMBER
TYPE NOT NULL NUMBER(2)
RR_OUTPUT_STATUS NUMBER(2)
ADDI_INFO VARCHAR2(500)
IS_STALE VARCHAR2(1)


So in order to recreate the columns again we need to run some scripts

The script can be found in SOA_HOME\integration\esb\sql\oracle\wfeventc.sql.

Run this script to recreate the columns.

Now restart the server and check for the issue.

No comments: