On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,524 Users
  • 2,269,754 Discussions
  • 7,916,770 Comments

Discussions

SQLLDR : Loading decimal file record in Number column

Vicky007
Vicky007 Member Posts: 60
edited May 15, 2020 7:25AM in SQL & PL/SQL

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

Tagged:

Answers

  • cormaco
    cormaco Member Posts: 2,071 Silver Crown
    edited May 15, 2020 5:32AM

    Please note that there is a dedicated forum for SQL Loader

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,212 Red Diamond
    edited May 15, 2020 7:16AM

    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
    cormaco Member Posts: 2,071 Silver Crown
    edited May 15, 2020 7:20AM
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,212 Red Diamond
    edited May 15, 2020 7:25AM