SQLLDR : Loading decimal file record in Number column

Vicky007

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

cormaco

Please note that there is a dedicated forum for SQL Loader

Solomon Yakobson

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.

cormaco

OP has opened a new thread here:

SQLLDR : Loading Decimal value in NUMBER Column

Solomon Yakobson

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.