11g: Performance

Soon, we will be posting the articles.

Keep visiting.

Oracle SQL Loader

An Oracle utility to read, upload the data into tables from data files. It has two files on which it executes.

1. Control File 
2. Data File. 

When the SQL Loader is executed, it creates three file:

1. LOG File : It gets created on every execution of SQL Loader program. A programmer can name a log file. It contains a log report of overall execution of the SQL Loader Program which tells us:
• Number of lines read by program
• Number of lines inserted or updated into a table
• Number of errors encountered and its explanation
2. BAD File : Bad file gets generated only if SQL Loader Program encountered errors due to formatting of data, datatype, column mismatch, data size.
3. DISCARD File :  These files gets generated when records failed to load into a table based on WHEN conditions specified in control file.
   

Control file has following instructions:

• Lines to skip from data files
• Type of DML to be performed i.e. INSERT or APPEND
• Table to be used.
• Location and name of data file.
• Columns of a table to be populated.
• Instructions for BAD file, LOG file and DISCARD file.

Below is an example of a control file program.

OPTIONS (SKIP = 1)

LOAD DATA

INFILE '$XXAR_TOP/data/in/ra_customers.csv'

APPEND INTO TABLE XXAR_CUSTOMERS_STG

FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

CUSTOMER_NAME "UPPER(:CUSTOMER_NAME)",

CUSTOMER_NUMBER,

ORIG_SYSTEM_REFERENCE,

STATUS "DEFAULT 'A'",

CUSTOMER_TYPE,

CUSTOMER_PROFILE_CLASS_NAME

)

OPTIONS : It tells what are the options given for execution of sql loader program.
LOAD DATA : A must written keyword for a control file.
INFILE : It tells the location and file name from where data will be read by sql loader program.
LOADING TYPE:

1. INSERT : It specifies only to insert data into specified table.
2. APPEND : It specifies only to append the data into a preloaded table.
3. REPLACE : It specifies to delete the existing data first and load the new data.
4. TRUNCATE : It specifies to truncate the table and then load the new data.

FIELDS TERMINATED BY: It tells by which special character columns are differentiated and separated.
OPTIONALLY ENCLOSED BY:


Using SQL functions in control file.
Sql functions can also be used to convert / modify the data while inserting into the table.
Example: Upper(column_name1) can be used to manipulate the case. The upper case value of column_name1 would be populated.
Similarly, functions like case manipulation functions, date functions, number functions, conditional (DECODE, CASE) could be used in control file program. Keywords like DEFAULT, CONSTANT  can also be used.

Running the SQL Loader Program:
SQL Loader Program can be run in two ways:

1. Register a concurrent program in oracle applications with an executable of execution method = 'SQL*Loader'.
2. Using a command on Unix platform. 

Unix command :  sqlldr USERID=apps/apps@dbname CONTROL=load_customers.ctl