insert data Sqlloader to oracle table using specfied format

User_IAP38

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

Frank Kulash

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

EdStevens

In that undelimited string of input data, what is the 'id', what is the 'sid', and what is the 'date'?

Frank Kulash

Hi, @User_IAP38

Whenever you have a problem, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include a CREATE TABLE statement and a complete data file, including any special cases you may need to handle, and as much of the control file as you can write. Always give your full Oracle version (e.g. 18.4.0.0.0).

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

1111320220124

1121420220124

Is this really a csv file? There aren't any commas. Are you saying the csv file looks like this

A,1111320220124,1.6

B,1121420220124,.9

and the second column (always exactly 13 digits) needs to be broken into three columns (a three-digit NUMBER, a 2-digit NUMBER and a DATE)?

BluShadow

CSV = Character Separated Values

Your data = No characters separating anything.

Regardless, you can use position format in your SQL*Loader control file to specify the exact positions and sizes of each "field" in the data.

https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#SUTIL1144

User_IAP38

https://community.oracle.com/tech/developers/discussion/comment/16823100#Comment_16823100

i have updated with actual code and details

Thanks

BluShadow

So, now you've updated it (which makes our answers look out of place, so don't do that, just add comments with further information if you must)... what's not working with the control file you've created?

Frank Kulash

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