This content has been marked as final. Show 4 replies
I think it is data issue combined with a not very judicious date format mask (it could fail when null data, because of your concatenation with '01', and work when not null data).
Did you try with a simple TRUNC ?
Your code :
After change :
...TO_DATE(TO_CHAR(ELG.PRD_END_DT, 'YYYY') || TO_CHAR(ELG.PRD_END_DT , 'MM') || '01' , 'YYYY-MM-DD') =PS_GPDE_DV_TRIG40.GPDE_DV_EVT_EFFDT...
...TRUNC(ELG.PRD_END_DT , 'MM') =PS_GPDE_DV_TRIG40.GPDE_DV_EVT_EFFDT...
Thanks for your response.
This works, however, I cant see any data with null date(or which when converted to char may return nulll). This is a delivered code by Oracle, and I am not able to figure out why this happened.
It is correct what you said, if the date is null, it passes '01' to be cconverted to date format into the to_date function and hence gives this error. But as I said, I dont see any row with null or blank date!!
Could there be any other explanation?
email@example.com wrote:Hmmm, well, this is an horrible piece of code, and if this is standard code, you could (should?) easily raise a SR for that, especially because is can hit some bugs.
... This is a delivered code by Oracle...
And to be sure about what date is causing the issue, you could write a small PL/SQL :
With that code, you have the rowid containing the date causing the trouble, and the date itself.
set serveroutput on declare W_PRD_END_DT DATE; begin for x in (select PRD_END_DT, rowid rwd FROM PS_GPDE_DV_TRIG40) loop begin select TO_DATE(TO_CHAR(x.PRD_END_DT, 'YYYY') || TO_CHAR(x.PRD_END_DT , 'MM') || '01' , 'YYYY-MM-DD') into W_PRD_END_DT from dual; exception when others then dbms_output.put_line(x.rwd||' '||x.PRD_END_DT); end; end loop; end; /
PS : could you post : module ? bundle ? Peopletools ? Oracle db version ?
Edited by: N. Gasparotto on Jul 31, 2009 7:52 PM
I used similar PL/SQL(changed tablename to PS_GPDE_DV_ELIG_MO) to identify the culprit rows but it does not return any rows after successfully completing the PL/SQL block. Also, to be sure, I exported the same data(using data mover) to another installation and in this installation it does not give any error at all; I think this rules out any data issues.
Currently we are in contact with Oracle, even though an SR has not be raised formally.
This is PeopleSoft HRMS 9.0, German country extension(Global Payroll). PeopleTools is 8.49.09. Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Interstingly, this issue has not occurred at any other client site so far.
No clue why this happens only in their environement!