Oracle create loader file

Creating a loader file is tedious. The syntax is complicated. Moreover, small errors lead to a rejection of records. Luckily, we have the possibility to generate them from SQL Developer.

One possibility is to use the SQL Loader. This facility has an import/ export module that allows to create such files. If one would like to export data to have them imported elsewhere, one may use the export facility with subsequent parameters:

One then gets a control file that looks like:

OPTIONS (ERRORS=50)
LOAD DATA 
INFILE 'C:\Users\tmaanen\Downloads\EMPLOYEES_BU_DATA_TABLE_1.ldr' "str '\r\n'"
APPEND
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE "HR"."EMPLOYEES_BU"
FIELDS TERMINATED BY'|'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS ( 
"EMPLOYEE_ID" ,
"FIRST_NAME" CHAR (20),
"LAST_NAME" CHAR (25),
"EMAIL" CHAR (25),
"PHONE_NUMBER" CHAR (20),
"HIRE_DATE" DATE "YYYY-MM-DD HH24:MI:SS" ,
"JOB_ID" CHAR (10),
"SALARY" ,
"COMMISSION_PCT" ,
"MANAGER_ID" ,
"DEPARTMENT_ID" )

I then truncated the table to see if the system works. I fired a command to load the records. I got:

Success.

Another possibility is to use the import facility. This is useful if one has a dataset for which a SQL Loader script is requested. One has several screens. One of them allows to select the SQL Loader.

The generated script is:

load data 
infile 'export.csv' "str '\r\n'"
append
into table FLIP
fields terminated by '|'
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( EMPLOYEE_ID,
             FIRST_NAME CHAR(4000),
             LAST_NAME CHAR(4000),
             EMAIL CHAR(4000),
             PHONE_NUMBER CHAR(4000),
             HIRE_DATE DATE "RRRRMMDD",
             JOB_ID CHAR(4000),
             SALARY,
             COMMISSION_PCT,
             MANAGER_ID,
             DEPARTMENT_ID
           )

This can be executed with desired results: