How to do a Data load in oracle

Loading a bulk amount of data from a source like excel file to data base is described below.
To do this we need 3 files.
1) A control file (.ctl)
2) A schell script (.ksh)
3) Script doing dataload(insert or update script)

Control file (.ctl) 

Usually data load is doing like initially we load data to a staging(temporary)table.From there we load data into our target tables.So in control file we specify the fields in staging table which are going to accept the incoming values.
A control file will simply specify the order of database table fields which will be filled with data from the source file.An example of control file is given below.

Let's say EMPLOYEE is a table which temporarly stores incoming data.Save this in .ctl file format.
eng:Load_Data.ctl

LOAD DATA

REPLACE INTO TABLE  EMPLOYEE

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

(

emp_name          "TRIM(:employee_name)" ,

emp_age                   "TRIM(:employee_age)"  ,

emp_salary                "TRIM(:employee_salary)"  ,

emp_ssn                 "TRIM(:employee_ssn)"                        

)

Here a keyword REPLACE is used.It says, when ever we are loading a new sheet of data the older data in the table will be replaced with new set of incoming data.If we use APPEND , which means the new set of incoming data will be appended to the end of existing data in the table.

The above example shows , left side contains fields of data base tables and right side shows the header part of source.Usually the right side part has no relevance but the left side has.The fields which we are arranged should be in the order of fields in the incoming file.The first column of the incoming file will be inserted to emp_name and second will be to emp_age and so on.If we are interchanging the emp_name and emp_age, what ever be the right side content the first will be inserted to emp_age and second to emp_name.So the order which we specify should be accurate.We can leave columns in the staging table by not specifying those in the control file.

Schell Script(.ksh)

This script will do the data load process.Example is shown below.

DATE_NOW=`date +"%Y%m%d%H%M%S"`

print -n ": "; read DB_NAME

print -n ": " ; read IN_FILE_NAME

print -n ": "; stty -echo; read DB_PASSWORD; stty echo --stty is used to hide the input as it is password

LOG=log_name.log

rm -f $LOG

rm -f SQL_Load.log

c=`sqlplus -s scheme_name/${DB_PASSWORD}@${DB_NAME} <<EOF

set feed off

set head off

select count(1) from all_objects where object_name=EMPLOYEE AND owner=scheme_name;

EOF`

echo $c

if [ $c -eq 0 ]  --If db name and pasword is given

then

echo "table create"

sqlplus -s scheme_name/${DB_PASSWORD}@${DB_NAME} @staging table creating script .sql >> $LOG

fi

echo "Load Initial data to staging table.. "

sqlldr scheme/${DB_PASSWORD}@${DB_NAME} data=data/$IN_FILE_NAME(file to be loaded) control=Load_Data.ctl skip=1 log=SQL_Load.log

           

            sqlplus -s scheme/${DB_PASSWORD}@${DB_NAME} @script to load.sql      >> $LOG

            sqlplus -s scheme/${DB_PASSWORD}@${DB_NAME} << EOF >> ${LOG}         

                                   

                        set head off

                        set feedback off

                        set termout off

                        set space 0

                        set pagesize 0

                        set linesize 180

                        set trimspool on                       

                                   

EOF

echo "Process Completed... "

Script to load data 

Now the entire data is in the staging table.We can insert or update data into target table from the staging table using a cursor or a loop.