Clarity

Expand all | Collapse all

Process getting failed after Clarity 14.3 Upgrade

Jump to Best Answer
  • 1.  Process getting failed after Clarity 14.3 Upgrade

    Posted 11-06-2015 08:28 AM

    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



  • 2.  Re: Process getting failed after Clarity 14.3 Upgrade

    Posted 11-06-2015 08:37 AM

    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)



  • 3.  Re: Process getting failed after Clarity 14.3 Upgrade

    Posted 11-06-2015 08:45 AM

    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,



  • 4.  Re: Process getting failed after Clarity 14.3 Upgrade

    Posted 11-06-2015 08:56 AM

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



  • 5.  Re: Process getting failed after Clarity 14.3 Upgrade
    Best Answer

    Posted 11-06-2015 09:18 AM

    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.



  • 6.  Re: Process getting failed after Clarity 14.3 Upgrade

    Posted 11-06-2015 09:23 AM

    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.