Process getting failed after Clarity 14.3 Upgrade

[Anon Anon]

Hi All,

 

After the Clarity 14.3 Upgrade some of our processes are getting failed which were working in 13.3 and 14.3

One of the errors which we are getting in the error messages is shown below-

 

 

 

 

LEFT OUTER JOIN nbi_project_current_facts pcf ON pcf.project_id=inv.id

LEFT OUTER JOIN nbi_project_forecast pf ON pf.project_id=inv.id

JOIN odf_ca_project cap ON cap.id=inv.id

JOIN prtask this_dp ON this_dp.prid=dp.ts_dpms_task

JOIN prtask tsk ON tsk.prprojectid=inv.id

JOIN odf_ca_task cat ON cat.id=tsk.prid

WHERE dp.id=5019002

AND NVL((SELECT COUNT(*) FROM prj_baselines pb WHERE pb.project_id=inv.id AND pb.code=this_dp.prexternalid),0)=0

AND tsk.prexternalid IS NOT NULL -- Fix for duplicates creation: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12702: invalid NLS parameter string used in SQL function

 

at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:194)

at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247)

at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:95)

at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:186)

at org.apache.commons.jelly.tags.core.IfTag.doTag(IfTag.java:42)

at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247)

at org.apache.commons.jelly.impl.ScriptBlock.run(ScriptBlock.java:95)

at org.apache.commons.jelly.TagSupport.invokeBody(TagSupport.java:186)

at com.niku.union.gel.tags.ScriptTag.doTag(ScriptTag.java:20)

at org.apache.commons.jelly.impl.TagScript.run(TagScript.java:247)

at com.niku.union.gel.GELScript.run(GELScript.java:67)

at com.niku.union.gel.GELController.invoke(GELController.java:74)

at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:207)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12702: invalid NLS parameter string used in SQL function

 

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

 

Please help

[David Morton]

That is an Oracle error complaining about something to do with a TO_CHAR / DATE format processing.  You need to check the syntax of the rest of that SQL statement (you only posted the last few lines), looking especially at date/char conversion commands.

 

(this could be data-dependant error, i.e. works for some data, fails for other data)

[Anon Anon]

Hi David,

 

Following lines hae been used.

 

REPLACE( inv.schedule_start, ' ', 'T') AS schedule_start,

    REPLACE( inv.schedule_finish, ' ', 'T') AS schedule_finish,

    ((inv.schedule_finish) - (inv.schedule_start) ) - 2*(inv.schedule_finish-inv.schedule_start)/7 -

      (CASE WHEN TO_CHAR(inv.schedule_start,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -

      (CASE WHEN TO_CHAR(inv.schedule_finish,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) AS project_duration,

[David Morton]

[ I'd consider using the date format of 'D' which returns a number, then you don't have to worry about NLS. ]

 

But I don't see anything wrong with that bit of code (even if a little inelegant).

[Anon Anon]

Hi Dave,

 

I contacted DBA team in order to check with the paramerter and came to know that  the value of nls_date_language has been set to 'AMERICAN', in the above legacy code it is using 'english' , so this was creating an issue, when i replaced the value to 'AMERICAN' it started working.

[David Morton]

Perhaps your database has lost the "English" NLS information (not sure how that would happen though), leaving you with "simplified-English" (or "American" as it is generally known).

 

If you were testing your date using a numeric conversion (i.e. 'D') then you would not be reliant on NLS funnyness - as suggested above.