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:
