Advertisements
RSS

Error with Oracle BPM process metric view during deployment

13 Jan

Since we use BI metrics in our Oracle BPM processes we configure these in JDeveloper. However after adding/renaming/deleting a metric we sometimes discover that we are still able to deploy the BPM composite. However the process specific database view (Analytic View Identifier) is gone.

When we check the Enterprise Manager we discover the following error:

EM Error Message


Component soa_server1
Module oracle.bpm.analytics.cube.persistence
User jzoggel
Thread ID [ACTIVE].ExecuteThread: '13' for queue: 'weblogic.kernel.Default (self-tuning)'
ECID 0000K838zKS8xk35vnT4iX1IRqKb000005
Message SQLException from: CREATE VIEW BPM_ACTV_PERF_MYPROCESS_V AS
Supplemental Detail SELECT
PERF.TASKPERFID AS SEQUENCE_ID,
PERF.PROCESSID AS PROCESS_ID,
PRC.PROCESSNAME AS PROCESS_NAME,
PRC.REVISION AS REVISION,
PERF.ECID AS ECID,
PERF.ACTIVITYID AS ACTIVITY_ID,
ACT.LABEL AS ACTIVITY_LABEL,
PERF.COMPONENTINSTANCEID AS PROCESS_INSTANCE_ID,
PERF.COMPOSITEINSTANCEID AS COMPOSITE_INSTANCE_ID,
PERF.DISCRIMINATOR AS ACTIVITY_DISCRIMINATOR,
PERF.ROLEID AS ACTIVITY_ROLE_ID,
PERF.CREATIONDATETIME AS ACTIVITY_START_TIME,
PERF.COMPLETIONDATETIME AS ACTIVITY_END_TIME,
PERF.TASKTIME AS ACTIVITY_RUNNING_TIME_IN_MSEC,
PERF.PARTICIPANT AS ACTIVITY_PARTICIPANT,
PERF.PRIORITY AS ACTIVITY_PRIORITY,
PERF.TENANTID AS TENANT_ID

FROM BPM_CUBE_TASKPERFORMANCE PERF, BPM_CUBE_PROCESS PRC, BPM_CUBE_ACTIVITY ACT
WHERE
PERF.PROCESSID = 61015 AND
PERF.ACTIVITYID = ACT.ACTIVITYID AND
ACT.PROCESSID = PRC.PROCESSID
UNION ALL
SELECT
PERF.TASKPERFID AS SEQUENCE_ID,
PERF.PROCESSID AS PROCESS_ID,
PRC.PROCESSNAME AS PROCESS_NAME,
PRC.REVISION AS REVISION,
PERF.ECID AS ECID,
PERF.ACTIVITYID AS ACTIVITY_ID,
ACT.LABEL AS ACTIVITY_LABEL,
PERF.COMPONENTINSTANCEID AS PROCESS_INSTANCE_ID,
PERF.COMPOSITEINSTANCEID AS COMPOSITE_INSTANCE_ID,
PERF.DISCRIMINATOR AS ACTIVITY_DISCRIMINATOR,
PERF.ROLEID AS ACTIVITY_ROLE_ID,
PERF.CREATIONDATETIME AS ACTIVITY_START_TIME,
PERF.COMPLETIONDATETIME AS ACTIVITY_END_TIME,
PERF.TASKTIME AS ACTIVITY_RUNNING_TIME_IN_MSEC,
PERF.PARTICIPANT AS ACTIVITY_PARTICIPANT,
PERF.PRIORITY AS ACTIVITY_PRIORITY,
PERF.TENANTID AS TENANT_ID,
PERF.FLEXSTRING03 AS D_USER,
PERF.FLEXSTRING02 AS D_CASENUMBER,
PERF.FLEXSTRING01 AS D_PRODUCT
FROM BPM_CUBE_TASKPERFORMANCE PERF, BPM_CUBE_PROCESS PRC, BPM_CUBE_ACTIVITY ACT
WHERE
PERF.PROCESSID IN (20499,32601,27810,47859,20397,9013,15447,33553,8643,46749,48636,47229,47071,47545,46913,47387,33827,47702,57000,54392,57382,57764,57573) AND
PERF.ACTIVITYID = ACT.ACTIVITYID AND
ACT.PROCESSID = PRC.PROCESSID java.sql.SQLSyntaxErrorException: ORA-01789: query block has incorrect number of result columns

The cause of the problem is due to the auto generated SQL script using a non valid union statement. To quick fix is to manually re-run the script and adding the PERF.FLEXxxxx columns in the SQL statement. Or much better, clean up the old process id references in the table BPM_CUBE_PROCESS. Just check the references of processid in the auto generated statement and remove the invalid references.

Advertisements
 
Leave a comment

Posted by on 13-01-2014 in BPM, Oracle

 

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: