4 Replies Latest reply: Aug 3, 2009 10:04 AM by 703034 RSS

    SQL Error: ORA-01840: input value not long enough for date format

    703034
      This is a delete SQL in an application engine, which does not give any error, but does not delete any rows either. I converted the delete "from " to "Select * ", and can see the following error:

      select * FROM PS_GPDE_DV_TRIG40 WHERE EXISTS ( SELECT 'X' FROM
      PS_GPDE_DV_ELIG_MO ELG WHERE ELG.EMPLID = PS_GPDE_DV_TRIG40.EMPLID AND
      ELG.EMPL_RCD = PS_GPDE_DV_TRIG40.EMPL_RCD AND 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 AND ELG.CAL_RUN_ID = 'CAL_0812' )
      Error report:
      SQL Error: ORA-01840: input value not long enough for date format


      The interesting thing is that if I attach a condition of emplid, then the above SQL does not give an error and returns rows:

      select * FROM PS_GPDE_DV_TRIG40 WHERE EXISTS ( SELECT 'X' FROM
      PS_GPDE_DV_ELIG_MO ELG WHERE ELG.EMPLID = PS_GPDE_DV_TRIG40.EMPLID AND
      ELG.EMPL_RCD = PS_GPDE_DV_TRIG40.EMPL_RCD AND 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 AND ELG.CAL_RUN_ID = 'FM_CG_0812' )
      and emplid = '1234'

      now, if the number of rows are increased(by giving more number of emplids), this starts to give error when the number of rows returned exceeds around 30. This is completely independent of which employee is used, so there is no question of data dependency.

      Cost based optimizer is off and un nest subquery is off on the init.ora file

      unnestsubquery=false

      optimizercost_based_transformation=off


      I dont know if it has got something to do with this issue.

      Any explanations would be welcome!!

      Thanks,
      Vikas
        • 1. Re: SQL Error: ORA-01840: input value not long enough for date format
          Nicolas.Gasparotto
          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 :
          ...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...
          After change :
          ...TRUNC(ELG.PRD_END_DT , 'MM') =PS_GPDE_DV_TRIG40.GPDE_DV_EVT_EFFDT...
          Nicolas.
          • 2. Re: SQL Error: ORA-01840: input value not long enough for date format
            703034
            Hi Nicloas,
            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?
            • 3. Re: SQL Error: ORA-01840: input value not long enough for date format
              Nicolas.Gasparotto
              vikas.bhartiya@asc-consultants.de wrote:
              ... This is a delivered code by Oracle...
              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.

              And to be sure about what date is causing the issue, you could write a small PL/SQL :
              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;
              /
              With that code, you have the rowid containing the date causing the trouble, and the date itself.

              Nicolas.

              PS : could you post : module ? bundle ? Peopletools ? Oracle db version ?
              Edited by: N. Gasparotto on Jul 31, 2009 7:52 PM
              • 4. Re: SQL Error: ORA-01840: input value not long enough for date format
                703034
                Hi Nicolas,
                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!

                Thanks,
                Vikas