1 Reply Latest reply: Jan 22, 2013 8:26 AM by Vortex13 RSS

    ORA-01858: a non-numeric character was found where a numeric was expected

    Muthu
      Hello all,

      I am getting below error while running sqlloder.

      Record 1: Rejected - Error on table "GL"."GL_INTERFACE", column ACCOUNTING_DATE.
      ORA-01858: a non-numeric character was found where a numeric was expected

      Record 2: Rejected - Error on table "GL"."GL_INTERFACE", column ACCOUNTING_DATE.
      ORA-01858: a non-numeric character was found where a numeric was expected


      I herewith attached the control file and data file...

      Control file

      load data
      infile 'test.txt'
      into TABLE GL_INTERFACE
      fields terminated by "," optionally enclosed by '"'
      ( STATUS, LEDGER_ID, USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME, ACCOUNTING_DATE, CURRENCY_CODE, DATE_CREATED, CREATED_BY,
      SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,SEGMENT6, SEGMENT7, ACTUAL_FLAG, ENTERED_DR, ENTERED_CR, GROUP_ID )


      *Data file*

      NEW,1,Payables,Payments,to_date('22-Dec-2012', 'DD-Mon-YYYY'),USD,to_date('23-Dec-2012', 'DD-Mon-YYYY'), 1110,03,610,000000,0000000,3111002,000000,0000000,A,1100.00,0.00,123456
      NEW,1,Payables,Payments,to_date('22-Dec-2012', 'DD-Mon-YYYY'),USD,to_date('23-Dec-2012', 'DD-Mon-YYYY'), 1110,03,610,000000,0000000,6540102,000000,0000000,A,0.00,1100.00,123456


      Please guide me what could be the issue...

      Thanks and Regards,
      Muthu
        • 1. Re: ORA-01858: a non-numeric character was found where a numeric was expected
          Vortex13
          The issue is you have the SQL TO_DATE in your data file. Your data file should just have the data without the TO_DATE. You can put TO_DATE function in your control file if needed, like this:
          Fields Terminated By '|' Optionally Enclosed By '"'
          Trailing Nullcols
          ( PO              "Trim(:po)"
          , ITEM            "Trim(:item)"
          , ORDERDATE       "to_date(Trim(:orderdate),'MM/DD/YYYY HH24:MI:SS')"
          )
          {code}
          
          HTH,
          --Johnnie