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: