Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
#sql-pl-sql
4 comments
169 views