Forum Stats

  • 3,826,860 Users
  • 2,260,714 Discussions
  • 7,897,105 Comments

Discussions

SQL Loader loaded with decimal data

User_IAP38
User_IAP38 Member Posts: 14 Green Ribbon

Hi

As data have .dat file as below format, while loaded the dat file face the invalid number ..

can you please help me how to resolve

INPUT FILE:

03159,0001234567,20220902,20220910, 01.5, 01.5,      XCM                                                  

03159,0013888888,20220902,20220910, 14.5, 14.5,      XCM

Table structure :

CREATE TABLE AL_FUT_LOAD

(

STORE NUMBER(38,0), 

SCM_NBR NUMBER(38,0), 

I_DATE DATE, 

L_DATE DATE, 

MLOS NUMBER(3,1), 

MGAIN NUMBER(3,1), 

USERID VARCHAR2(50 CHAR));

loading scripts


LOAD DATA

REPLACE                 

INTO TABLE AL_FUT_LOAD    

DATE FORMAT "YYYY-MM-DD"

TRAILING NULLCOLS          

(        

STORE        POSITION(1:5)     CHAR(5),

SCM_NBR     POSITION(7:16)     INTEGER EXTERNAL(10),

I_DATE        POSITION(18:25)    DATE EXTERNAL(8),

L_DATE        POSITION(27:34)    DATE EXTERNAL(8),

MLOS    POSITION(36:39)    DECIMAL EXTERNAL(4),

MGAIN    POSITION(41:44)    DECIMAL EXTERNAL(4),

USERID   POSITION(46:60)    CHAR(15)  

)

Best Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,067 Red Diamond

    Hi, @User_IAP38

    03159,0001234567,20220902,20220910, 01.5, 01.5,      XCM                                                  

    03159,0013888888,20220902,20220910, 14.5, 14.5,      XCM

    ...

    MLOS    POSITION(36:39)    DECIMAL EXTERNAL(4),

    MGAIN    POSITION(41:44)    DECIMAL EXTERNAL(4),

    USERID   POSITION(46:60)    CHAR(15) 

    It looks like you have the wrong positions for mgain and userid, and possibly mlos.

    I added a couple of rows to the data file to identify the positions:

    03159,0001234567,20220902,20220910, 01.5, 01.5,      XCM
    03159,0013888888,20220902,20220910, 14.5, 14.5,      XCM
    123456789012345678901234567890123456789012345678901234567890
             1         2         3         4         5         6     
    

    It looks like mlos is in positions 36-40 (or 37-40, if you want to ignore the space). Either way, it looks like mgain should be positions 42-46 (or 43-46) and userid starts at position 48.

    Does the data file always have commas? Perha[ps ypu could use delimited fields instead of postitions.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    C:\temp>type al_fut_load.ctl
    LOAD DATA
    INFILE AL_FUT_LOAD.DAT
    REPLACE
    INTO TABLE AL_FUT_LOAD
    FIELDS TERMINATED BY ','
    DATE FORMAT 'YYYYMMDD'
    TRAILING NULLCOLS
    (
    STORE,
    SCM_NBR,
    I_DATE DATE,
    L_DATE DATE,
    MLOS,
    MGAIN,
    USERID "TRIM(:USERID)"
    )
    
    C:\temp>sqlldr [email protected] control=al_fut_load.ctl
    Password:
    
    SQL*Loader: Release 12.2.0.1.0 - Production on Mon May 23 07:32:37 2022
    
    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 AL_FUT_LOAD:
      2 Rows successfully loaded.
    
    Check the log file:
      al_fut_load.log
    for more information about the load.
    
    C:\temp>sqlplus [email protected]
    
    SQL*Plus: Release 12.2.0.1.0 Production on Mon May 23 07:32:43 2022
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Mon May 23 2022 07:32:40 -04:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> set linesize 132
    SQL> select * from al_fut_load;
    
         STORE    SCM_NBR I_DATE    L_DATE          MLOS      MGAIN USERID
    ---------- ---------- --------- --------- ---------- ---------- --------------------------------------------------
          3159    1234567 02-SEP-22 10-SEP-22        1.5        1.5 XCM
          3159   13888888 02-SEP-22 10-SEP-22       14.5       14.5 XCM
    
    SQL>
    

    SY.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,067 Red Diamond

    Hi, @User_IAP38

    03159,0001234567,20220902,20220910, 01.5, 01.5,      XCM                                                  

    03159,0013888888,20220902,20220910, 14.5, 14.5,      XCM

    ...

    MLOS    POSITION(36:39)    DECIMAL EXTERNAL(4),

    MGAIN    POSITION(41:44)    DECIMAL EXTERNAL(4),

    USERID   POSITION(46:60)    CHAR(15) 

    It looks like you have the wrong positions for mgain and userid, and possibly mlos.

    I added a couple of rows to the data file to identify the positions:

    03159,0001234567,20220902,20220910, 01.5, 01.5,      XCM
    03159,0013888888,20220902,20220910, 14.5, 14.5,      XCM
    123456789012345678901234567890123456789012345678901234567890
             1         2         3         4         5         6     
    

    It looks like mlos is in positions 36-40 (or 37-40, if you want to ignore the space). Either way, it looks like mgain should be positions 42-46 (or 43-46) and userid starts at position 48.

    Does the data file always have commas? Perha[ps ypu could use delimited fields instead of postitions.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    C:\temp>type al_fut_load.ctl
    LOAD DATA
    INFILE AL_FUT_LOAD.DAT
    REPLACE
    INTO TABLE AL_FUT_LOAD
    FIELDS TERMINATED BY ','
    DATE FORMAT 'YYYYMMDD'
    TRAILING NULLCOLS
    (
    STORE,
    SCM_NBR,
    I_DATE DATE,
    L_DATE DATE,
    MLOS,
    MGAIN,
    USERID "TRIM(:USERID)"
    )
    
    C:\temp>sqlldr [email protected] control=al_fut_load.ctl
    Password:
    
    SQL*Loader: Release 12.2.0.1.0 - Production on Mon May 23 07:32:37 2022
    
    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 AL_FUT_LOAD:
      2 Rows successfully loaded.
    
    Check the log file:
      al_fut_load.log
    for more information about the load.
    
    C:\temp>sqlplus [email protected]
    
    SQL*Plus: Release 12.2.0.1.0 Production on Mon May 23 07:32:43 2022
    
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    
    Enter password:
    Last Successful login time: Mon May 23 2022 07:32:40 -04:00
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    
    SQL> set linesize 132
    SQL> select * from al_fut_load;
    
         STORE    SCM_NBR I_DATE    L_DATE          MLOS      MGAIN USERID
    ---------- ---------- --------- --------- ---------- ---------- --------------------------------------------------
          3159    1234567 02-SEP-22 10-SEP-22        1.5        1.5 XCM
          3159   13888888 02-SEP-22 10-SEP-22       14.5       14.5 XCM
    
    SQL>
    

    SY.