Mastering SQL Loader: How to Load Dates with Ease

SQL Loader (SQLLDR) is a utility in Oracle to load flat file data to a table. Here’s a sample control file logic helpful when Dates are present in the input file.

Date logic (format) in the control file for SQLLDR (SQL Loader processing)

Input file

Here’s a comma-separated input file. The first record is the header. While loading, the SQL Loader skips the first record. The input is a comma-separated file. The first field is Date, and the second field is employee-id. I have named the input file test.dat, and it is located in the D:/ drive.

JOINDATE,EMPID
'01.10.2023','ABCDEFG'
'02.12.2023,'HIJKLMN'

Before loading, create a table in Oracle SQL-developer

create table sample
(
JOINDATE DATE,
EMPID CHAR(07)
);

Create a control file

Here the name of the control file is p.ctl, which is located in the D:/ drive.

load data 
INFILE 'D:\sample.dat'
into table sample
truncate
fields terminated by ","
(
JOINDATE POSITION(2:10) DATE"DD-MM-YYYY",
EMPID POSITION(15:22)
)

Run the SQLLDR utility

Type ‘cmd’, and open the command prompt. Run the below command, which loads flat file data to a table. The skip=1 states to skip the first record.

SQLLDR database-name/password@portname control=D:/p.ctl, skip=1

References

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.