This discussion is archived
1 Reply Latest reply: Jan 22, 2013 6:26 AM by Vortex13 RSS

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

Muthu Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points