Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

SQLLDR : Loading decimal file record in Number column

Vicky007May 15 2020 — edited May 15 2020

HI, I have below file data and i m trying to load it using below control file. i get below error on load :

OPTIONS (SKIP=1)

load data

TRUNCATE

into TABLE M1

fields terminated by ","

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

  TID,      

  "DATE" TIMESTAMP  "DD-MON-YYYY HH24.MI.SS",

TIME,             

  NCE "CASE WHEN :NCE = 'null' THEN 0 END",     

  AMOUNT,                      

  CHECKE,

value used for ROWS parameter changed from 64 to 61

Record 1: Rejected - Error on table M1, column AMOUNT.

ORA-01722: invalid number

File set is like :

TID,DATE,TIME,NCE,AMOUNT,FEE

1959399618,04-MAY-2020,15:36:13,null,20000.0,0

1959399619,04-MAY-2020,15:36:13,null,1000,0

20000.0 is not getting loaded and giving above error , any reason for this ?

FYI : NLS_NUMERIC_CHAR is set to .,

Thanks

Comments
Post Details
Added on May 15 2020
4 comments
97 views