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

[Arunachalam Angusamy]

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)

[Roland Parrotte]

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"/>

[David Morton]

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)

[Roland Parrotte]

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.

[David Morton]

^ 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.

[Stephanie Nelson]

Hi Arun - 

 

Did David's and Roland's responses help?

 

-Stephanie

[Arunachalam Angusamy]

Hi Stephanie, 

 

Yes, It helped. I fixed the code.