How to create a SQL*Loader Concurrent Program

SQL*Loader : loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. When building customizations for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Table might be seeded interface table or customize staging table to do further validation.

                       To Load data from external system we might consider one option as SQL* Loader. In this mainly we required two thing.

1. Data File( in .csv format) : We need to have a csv file with data - the csv can also contain header information. The header in a csv file can be skipped by adding a special parameter in the control file. Take note of the columns in the csv file
2. Control File (.ctl ) : The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc.

Starting a SQL*Loader load can be done by command line by executing the below:

sqlldr apps/apps_password@host:port/sid control=control_file_name.ctl log=log_file_name.log

Syntax:

OPTIONS(SKIP=1)

LOAD DATA

infile '*'

REPLACE/INSERT/APPEND into table table_name

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

Column1,

Column2,

Column3,

Column n

)

Note:- To register SQL Loader concurrent program you need to keep both control file .ctl and Data file .csv on bin folder of application. Suppose you are registering SQL loader program for XX_CUSTOM application then control and data file should place in XX_CUSTOM_TOP/bin folder.

OPTIONS(SKIP=1) : This sysntax used when we have column name as well in Data file. while loading data we want to skip first line which is column header and load data from second line. 

When you run SQL loader it will create three types of files.

1. Bad File :- When we load data using sql loader due to some data descrepancy sql loader reject the records at this time bad file will create automatically. Extension of Bad file is '.bad'. SQL loder will reject records if data is in incorrect format or any internal error occurs while running sql loader.
2. Discard File :- When we have written some condition in control file to load data conditionally. Discard file contain all those records which was rejected by sql loader for not satisfying SQL loader condition we write. Extension of this type of file is '.dis'.
3. Log file :- Log file will be created automatically after completion of sql loader process. it contains information like count of success records and bad file and discard file details with location. Extension of log file will be '.log'

To register Concurrent program of Type SQL Loader :

1.  Create the SQL*Loader Executable in Oracle E-Business Suite. Go to responsibility System Administrator - Concurrent - Program - Executable. Select Execution Method SQL*Loader to let EBS know SQL*Loader needs to be started. The Execution File Name holds the name of the control file you want to start (without extension .ctl ). The control file needs to be located in the bin directory of your customization application.
2. Create the concurrent program in Oracle E-Business Suite. Go to responsibility System Administrator - Concurrent - Program - Define. In this use same Executable which we register in first step.
3. Now create one parameter for Data file path. Assign 100 character value set. Optionally add a default value of path like XXCUSTOM_TOP/bin/data_file.csv