BPM-0704: An error occurred while executing custom script

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

Processes error out with BPM-0704: An error occurred while executing custom script. You may also see other BPM numbers with similar error messages. When you click on the Show Details option for the error, you will see the following farther into the error:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427: single-row subquery returns more than one row

You will see warnings and/or errors in the bg-niku.log like:

WARN 2009-07-01 16:45:03,458 [Custom script execution Thread-2726] utilities.PersistenceUtils (none:none:none) -->
The exception trace is too big to be attached to the process error message. Only a truncated
exception trace will be attached to the process error message. Here is the complete exception trace:
org.apache.commons.jelly.JellyTagException:
...
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427: single-row subquery returns more than one row
...
Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01427:
single-row subquery returns more than one row
...

Solution:

We have discovered that when the gel_objectInstanceId or processInstanceId variable is passed in steps other than the start step it can cause unexpected results.

Here is what you should do to fix the problem:

This is an example of the code that will cause this type of issue. (NOTE: This gel script is in a step that is not the start step). We also recommend that you do not place any gel in the start step except for the set persist ID.

Example of Bad Script:

<gel:script
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:core="jelly:core"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
    xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <!-- **************************************************** -->
    <!-- Program:  Update number of Proceses left             -->
    <!-- Modification history:                                -->
    <!-- Dependencies  Loop10, loop20                         -->
    <!-- **************************************************** -->
    <!-- End Parameters-->
    <gel:setDataSource dbId="niku"/>
    <!-- Query the number of sub-approvals remaining -->
    <sql:query var="appcode">
      select  code appcodeattribute, odf_parent_id parentid from odf_ca_tfelectronicsign
      where id=  '${ gel_objectInstanceId }'     -----    THIS IS WHAT YOU NEED TO AVOID!!!
      </sql:query>
    <core:set value="${appcode.rows[0].appcodeattribute}" var="appcodeattribute"/>
    <core:set value="${appcode.rows[0].parentid}" var="parentid"/>
    <sql:update> update odf_ca_project set ${appcodeattribute}='tfNotRequired' where id = ${parentid} </sql:update>
    <gel:log>parentID= ${parentid}</gel:log>
</gel:script>

This is how you could correct the same script.

1. Place the following script in your start step. This script sets the gel_objectInstanceId to myObjectID. NOTE: This variable name can be anything.

   <gel:script xmlns="http://www.w3.org/2001/XMLSchema" 
       xmlns:core="jelly:core" 
       xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
       xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
       <!-- **************************************************** --> 
       <!-- Program:  Create Persist variable for Gel_object--> 
       <!-- **************************************************** --> 
       <gel:persist scope="INSTANCE" value="${gel_objectInstanceId}" var="myObjectID"/> 
   </gel:script>

2. Replace the Bad Script above with script that looks like this. Notice the use of myObjectID (which was persisted in the start step) instead of the gel_objectInstanceId.

   <gel:script 
       xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
       xmlns:core="jelly:core" 
       xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" 
       xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
       xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" 
       xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
       xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" 
       xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
       <!-- **************************************************** --> 
       <!-- Program:  Update number of Proceses left --> 
       <!-- Dependencies  Loop10, loop20                              --> 
       <!-- **************************************************** --> 
       <!-- End Parameters--> 
       <gel:setDataSource dbId="niku"/> 
       <!-- Query tthe number of sub-approvals remaining --> 
       <sql:query var="appcode"> 
         select  code appcodeattribute, odf_parent_id parentid from odf_ca_tfelectronicsign
         where id= '${myObjectID}' </sql:query> 
       <core:set value="${appcode.rows[0].appcodeattribute}" var="appcodeattribute"/> 
       <core:set value="${appcode.rows[0].parentid}" var="parentid"/> 
       <!-- Update the number of Subs remaining to Loop 20 --> 
       <sql:update> 
         update odf_ca_project set ${appcodeattribute}='tfNotRequired' where id = ${parentid} 
         </sql:update> 
        <gel:log>parentID= ${parentid}</gel:log> 
   </gel:script>

So in short, look for any query or update statements that have the gel_objectInstanceID (or processInstanceId) variable anywhere outside of the start step. Then fix them as described above. 

Environment

Release: NCSVCS05900-8.1-Clarity-Service Connect-for MS-SQL
Component: