Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 584 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 45 Data Integration
- 45 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 666 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
SQLLDR : Loading decimal file record in Number column

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 dataTRUNCATEinto TABLE M1fields 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 61Record 1: Rejected - Error on table M1, column AMOUNT.ORA-01722: invalid number
File set is like :
TID,DATE,TIME,NCE,AMOUNT,FEE1959399618,04-MAY-2020,15:36:13,null,20000.0,01959399619,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
Answers
-
-
I can't reproduce it:
I:\>sqlplus [email protected]
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 15 07:12:15 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri May 15 2020 07:12:08 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select value from nls_session_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';
VALUE
----------
.,
SQL> host
Microsoft Windows [Version 10.0.17763.1098]
(c) 2018 Microsoft Corporation. All rights reserved.
I:\>type c:\temp\m1.ctl
OPTIONS (SKIP=1)
load data
infile 'c:\temp\m1.txt'
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
)
I:\>type c:\temp\m1.txt
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
I:\>sqlldr control=c:\temp\m1.ctl
Username:[email protected]
Password:
SQL*Loader: Release 12.2.0.1.0 - Production on Fri May 15 07:13:57 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table M1:
2 Rows successfully loaded.
Check the log file:
m1.log
for more information about the load.
I:\>exit
SQL> select * from m1;
TID DATE TIME NCE AMOUNT CHECKE
---------- ------------------------------ ---------- ---------- ---------- ----------
1959399618 04-MAY-20 12.00.00.000000 AM 15:36:13 0 20000 0
1959399619 04-MAY-20 12.00.00.000000 AM 15:36:13 0 1000 0
SQL>
SY.
-
OP has opened a new thread here:
-
I believe your issue is character set related SQL*Loader Example When Loading Data Into A Unicode Character Set (UTF8/AL32UTF8) Database(Doc ID 435128.1)
SY.