Clarity PPM1

Expand all | Collapse all

BPM-0704: An error occurred while executing custom script:

Jump to Best Answer
  • 1.  BPM-0704: An error occurred while executing custom script:

    Posted 04-09-2019 01:37 PM

    Any Idea the below piece of information is giving the error message : 

     

    <core:invokeStatic className="java.lang.System" method="getenv" var="NIKU_HOME">
    <core:arg value="NIKU_HOME"/>
    </core:invokeStatic>

    <gel:parse file="${NIKU_HOME}/config/properties.xml" var="properties"/>
    <gel:set asString="true" select="$properties/properties/webServer/webServerInstance[@id='app']/@sslEntryUrl" var="URL"/>

    <!-- Get a DB Connection to Clarity -->
    <gel:setDataSource dbId="Niku" />

    <!-- QUERY FOR PROJECT INFO -->
    <sql:query escapeText="0" var="result">
    <![CDATA[
    select
    inv.id ideaID,
    inv.code ideaCode,
    inv.name ideaName
    from inv_ideas ida,
    inv_investments inv
    where inv.id = ida.id
    and ida.id = ?
    ]]>
    </sql:query>

     

    Error Message : 

     

     

    BPM-0704: An error occurred while executing custom script: org.apache.commons.jelly.JellyTagException: null:28:85: <sql:query> select inv.id ideaID, inv.code ideaCode, inv.name ideaName from inv_ideas ida, inv_investments inv where inv.id = ida.id and inv.id =?: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00911: invalid character at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:194) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:248) at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:96) at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:187) at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:248) at com.niku.union.gel.GELScript.run(GELScript.java:58) at com.niku.union.gel.GELController.invoke(GELController.java:74) at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00911: invalid character at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddal.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddal.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddam.t(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.y(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.x(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.executeQuery(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde5.executeQuery(Unknown Source) at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:175) ... 11 more Root cause java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00911: invalid character at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcr.a(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.b(Unknown Source) at com.ca.clarity.jdbc.oraclebase.ddcq.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddal.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddal.a(Unknown Source) at com.ca.clarity.jdbc.oracle.ddam.t(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.y(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.x(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde3.executeQuery(Unknown Source) at com.ca.clarity.jdbc.oraclebase.dde5.executeQuery(Unknown Source) at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:175) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:248) at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:96) at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:187) at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20) at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:248) at com.niku.union.gel.GELScript.run(GELScript.java:58) at com.niku.union.gel.GELController.invoke(GELController.java:74) at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834)



  • 2.  Re: BPM-0704: An error occurred while executing custom script:

    Posted 04-10-2019 05:17 AM

    In your GEL script, you have:

    and ida.id = ?

    Whilst the Oracle error message shows

    and inv.id =?

     

    So which is the correct cut-paste as these are not the same.

     

    Either way, do you really have ida.id = ? in your GEL script?  I would normal have:

    and inv.id = ${v_objectID}

    where earlier in the GEL script it would be undertaking the variable assignment

    <core:set value="${gel_objectInstanceId}" var="v_objectID"/>



  • 3.  Re: BPM-0704: An error occurred while executing custom script:
    Best Answer

    Posted 04-10-2019 07:00 AM

    We can leave the 'inv.id = ?' in the SQL in the GEL script, but you must provide the parameter (to replace the '?')

     

    i.e. you'd need something like;

     

    <sql:param value="${v_objectID}"/>

     

    as part of the SQL statement, i.e. before the </sql:query>

     

    (its technically better to do it this way that 'hard-coding' the parameter value into the SQL string due to the way that Oracle analyses and caches queries (specifically the execution plans). In practice though, you'd only really notice problems (database performance) if the statements with the hard-coded value were called lots of times, but it's just good practice to use parameters rather than explicit hard-coding)



  • 4.  Re: BPM-0704: An error occurred while executing custom script:

    Posted 04-12-2019 01:29 AM

    I was unaware of the <sql:param tag - thanks for providing.  Are you sure that Oracle would cache the SQL including the '?' marks (ie ida.id = ?), as I would have thought it is the GEL script doing the substitution for the question marks and would be sending ida= 5000000 (as an example) to Oracle to be parsed and then cached.



  • 5.  Re: BPM-0704: An error occurred while executing custom script:

    Posted 04-12-2019 03:36 AM

    ^ pretty much ; I would think of it as the GEL sending two bits of information to Oracle ; the SQL statement (which is always the same across multiple calls) and the parameter

     

    So its just the 'normal' oracle QEP caching ; in that since the '?' is a parameter so the data value does not form a part of the query so when it is executed (again) the optimiser can use a saved execution-plan to run the query. When we 'hard-code' a string value into the SQL then it looks like a new query each time and the optimiser has to work out a new execution plan.

     

    Only really an issue is the query is going to be called lots and lots of times though ; just a good habit to write queries like that I think.



  • 6.  Re: BPM-0704: An error occurred while executing custom script:

    Posted 04-10-2019 09:08 AM

    Hi Arun - 

     

    Did David's and Roland's responses help?

     

    -Stephanie



  • 7.  Re: BPM-0704: An error occurred while executing custom script:

    Posted 04-10-2019 09:19 AM

    Hi Stephanie, 

     

    Yes, It helped. I fixed the code.