Forum Stats

  • 3,827,727 Users
  • 2,260,812 Discussions
  • 7,897,362 Comments

Discussions

insert data Sqlloader to oracle table using specfied format

User_IAP38
User_IAP38 Member Posts: 14 Green Ribbon
edited Jan 24, 2022 3:45PM in SQL & PL/SQL

Hi Team,

in SQL loaded that dat file contains the data as below format

1111320220124

1121420220124

Table structure

CREATE TABLE tmp_order(id number,sid number,exp_date date );

SQLLOADER CTL FILE

LOAD DATA

REPLACE

INTO TABLE tmp_order

TRAILING NULLCOLS

(

ID POSTION(1:3) INTEGER EXTERNAL(3),

SID POSTION(4:5) INTEGER EXTERNAL(2),

EXP_DATE POSTION(6:13) INTEGER EXTERNAL(8)

)

while load data into table look like below format

id sid exp_date

111 13 2022-01-24 00:00:00

112 14 2022-01-24 00:00:00

kindly help me how to create the ctl file and load the data as above format


Thanks

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond
    Answer ✓

    Hi, @User_IAP38

    You can use this control file:

    -- SQLLOADER CTL FILE
    LOAD DATA
    REPLACE
    INTO TABLE tmp_order
    DATE FORMAT "YYYYMMDD"
    TRAILING NULLCOLS
    (
    ID       POSITION(1:3)  INTEGER EXTERNAL(3),
    SID      POSITION(4:5)  INTEGER EXTERNAL(2),
    EXP_DATE POSITION(6:13) DATE    EXTERNAL(8)
    )
    

    Make sure POSITION is spelled correctly

Answers