SQL LOADER,Steps in loading the flat file data into the database table,Syntax to develop the control file.

SQL * LOADER

           

It is one of the oracle tools and it is used to load the data from flat file to oracle base  table. It is used in the development of Interfaces ( I ) and conversions ( C ) in the RICE components

 There are five types of files are used with SQL * LOADER.

1) Data file 2) Control File 3) Discard File 4) Bad File 5) Log File

1) Data File or Flat file :

            File contains data in specific format as per the requirement of the client. It will be received from the client. Extension is( .dat , .txt, .csv) .csv is an excel file csvà common separated value. If data is available in excel file with extension .xls then save it as .cvs file before going to load into database table. Mostly data in the form of excel files.

2) Control file : It is SQL * LOADER program. It provides syntax to load the data form flat file to database table.

This file has to be developed and executed by the Technical Consultant. It will be developed in the notepad and saved with extension ( .ctl )

3) Bad File : It will  be generated automatically by the SQL * LOADER. Bad file contains the records, which are rejected by the SQL * LOADER 

                        SQL *  LOADER  rejects the records in the following scenario

                        1)  Any Data Type mismatch, 2) No. of fields mismatched 3) Internal Errors, 3) Data file Format

                        problem . Extension of this file is ( .bad )

4)Discard File:  It will   be It will  be generated automatically by the SQL * LOADER. It contains the records, which are rejected by the  Control file. Control file rejects the record, when the record is not as per the specified  format in the Control file. Discard File Extension is ( .dis)

5)Log File : It contains the Log information like Program Time of start, Time of end, No of records, which are successfully uploaded into the database table. Rejected Records which are sent to the Bad file and Discard file. Error Message during the execution of Control file. Extension is ( .log )

Steps in loading the flat file data into the database table

1) Received the flat file form the client and check the file format.

2) Identify the data base table name and database

3) Develop the control file using notepad and save it.

            Syntax to develop the control file.

           

            LOAD DATA

            INFILE   ‘ Data File Path  with file name ‘

            INSERT INTO TABLE    <Table Name>

            FIELDS TERMINATED   by ','    OPTIONALLY ENCLOSED BY  ' " '

            TRAILING NULLCOLS

            (Column1    ,    column2    ,     column3    ,......)

            Order of the columns in the flat file

 

   Eg1:

            Data file Path ( C:\ MyUser\User10\emp.txt )

            Data format  order Empno, empname, salary, comm, deptno

            1001, Smith, 5600, 300, 20

            1002, Allen, 4500, , 10

            1003, Ramu, 7600, , 30

            1004, Krishna, 5400,, 40

            Table into which data to be transferred

             create table MyEmp ( empno number (10), ename varchar2(20), salary number(10), deptno number(10)  )

            Control file

            LOAD DATA

            INFILE   'C:\MNRAO\emp.txt'

            INSERT INTO TABLE  MyEmp

            FIELDS TERMINATED   by ','

            (empno, ename, salary, comm, deptno )

            Save as control file ( eg : C:\user\EmpLoader.ctl )

4) Go to Command Prompt where the SQL * Loader is Installed. ( start à runà cmd )

            D:Oracle\Proddb\8.1.7\bin\  SQLLDR  Username/Password@PROD

            Control = Control file Path.

            D:Oracle\vis\9.2.0\bin > SQLLDR scott/tiger@vis

            Control = C:\user\EmpLoader.ctl

            Eg2:  if data is available in the format ( empno, ename,  deptno, salary, comm.)

            1001,Smith,20,5600,300

            1002,Allen,10,4500,,

            1003,Ramu,30,7600,350

            1004,Krishna,40,5400,,

            Then write control file as below

            LOAD DATA

            INFILE   'C:\MNRAO\emp.txt'

            INSERT INTO TABLE  MyEmp

            FIELDS TERMINATED   by ','

            (empno, ename,  deptno, salary, comm )

            Appending  records to database table

            LOAD DATA

            INFILE   'C:\MNRAO\emp.txt'

            APPEND INTO TABLE  MyEmp1

            FIELDS TERMINATED   by ','

            ( empno, ename, salary, comm, deptno )

            Truncate table and insert records into table

LOAD DATA

INFILE   'C:\MNRAO\emp.txt'

TRUNCATE INTO TABLE  MyEmp1

FIELDS TERMINATED   by ','

( empno,ename,salary,comm,deptno )

           

            INSER T à  database table should be empty

            APPEND à  table may be with record or empty

            TRUNCATE à it will delete the existing records and  inserts the new records into the database table

            SQL LODER options

            D:\oracle\visdb\9.2.0\bin> SQLLDR scott/tiger@vis  - LOAD  n

            -LOAD   n  à To load first n No.of records of flat file  into database table

            -SKIP   n  à to skip first n No. of records of flat file and to load the remaining records of flat file to the end.

-SKIP  n   -LOAD n à to skip first n No.of records of flat file and load the next n No.of records of flat file into the database table

            D:\oracle\visdb\9.2.0\bin> SQLLDR scott/tiger@vis  -SKIP n   -LOAD  n

            -ROWS  n à  for every n No.of records commit

FILLER à to ignore the columns of the table ( it will be empty )

LOAD DATA

INFILE   'C:\MNRAO\emp.txt'

TRUNCATE INTO TABLE  MyEmp1

FIELDS TERMINATED   by ','

( empno, ename, salary, comm  FILLER,  deptno )

METHOD à Method loading the data

            These are of two types

1)      Conventional  à consider all constraints and inserts the records into the table. It is a default

2)      Direct  à No constraints will be effective ( it is a faster )

            D:\oracle\visdb\9.2.0\bin> SQLLDR scott/tiger@vis  DIRECT=true 

            Note : for DIRECT need not to precede with  symbol    - (hyphen)

Imp Note : if file has been received in the excel file format with extension ( .xlc ), then save the file with extension (.cvs ) . The filed separator will be comma ( , ) ( default )

Loading data with default values

Eg:

create table EMP_DEFAULT( empno number(10),

                                                ename varchar(50),

                                                jdate date,

                                                sal number(10),

                                                comm number(10),

                                                location varchar(50),

                                                deptno number(10))

Test it

select * from EMP_DEFAULT

Requirement

EmpNo à should be generated automatically by using database sequence

Ename à should be in upper case only

Jdate à sysdate

Comm à 10% of basic sal ( sal * 0.10)

Location à     ‘H’ : Hyderabad

                        ‘B’ : Banglore

                        ‘C’ : Chennai

                              : Mumbai

Deptno à constant =10

Steps to develop the application for above requirement

1)      create the sequence using TOAD utility

Create à Sequence à

Sequence owner : SCOTT

Sequence name  : EMP_SEQ

Start with           :  1

Code generated by TOAD utility

CREATE SEQUENCE SCOTT.EMP_SEQ

START WITH 0

INCREMENT BY 1

MINVALUE 0

NOCACHE

NOCYCLE

            NOORDER

2)      Develop the control file ( DEFAULTS.ctl )

LOAD DATA

INFILE *

INSERT  INTO TABLE  EMP_DEFAULT

FIELDS TERMINATED   by ','

TRAILING NULLCOLS

( empno "EMP_SEQ.nextval",

ename "UPPER(:ename)",

sal,

location "DECODE ( :location, 'H','HYDERABD',

                                         'B','BANGLORE',

                                         'C','CHENNAI',

                                             'MUMBAI')",

COMM  "(:sal*0.1)",

jdate  sysdate,

deptno constant "10" )

begindata

  ,Saritha,12000,H

  ,Ramu,13500,B

  ,Ranjith,5600,C

  ,Rakesh,8600,,

  ,venkat,12400,B

            Leave space for the first filed (empno) as it is being generated automatically by using sequence.

Note :  in the above UPPER, DECODE are the sql functions. We can use all built-in SQL functions but we can not use the user defined functions.

            3) go to command prompt and SQL * Loader

LOG file and BAD file

The log file will be generated automatically  in the ORACLE bin directory. It will be generated by the name of control

file

D:\oracle\visdb\9.2.0\bin  \ DEFAULTS.txt

Sample of LOG file as shown below

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 256000 bytes

Continuation:    none specified

Path used:      Conventional

Table EMP_DEFAULT, loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

EMPNO                               FIRST     *   ,       CHARACTER           

    SQL string for column : "EMP_SEQ.nextval"

ENAME                                NEXT     *   ,       CHARACTER           

    SQL string for column : "UPPER(:ename)"

SAL                                  NEXT     *   ,       CHARACTER           

LOCATION                             NEXT     *   ,       CHARACTER           

    SQL string for column : "DECODE ( :location, 'H','HYDERABD',

                                                     'B','BANGLORE',

                                                     'C','CHENNAI',

                                                         'MUMBAI')"

COMM                                 NEXT     *   ,       CHARACTER           

    SQL string for column : "(:sal*.1)"

JDATE                                                     SYSDATE

DEPTNO                                                    CONSTANT

    Value is '10'

Table EMP_DEFAULT:

  5 Rows successfully loaded.

  0 Rows not loaded due to data errors.

  0 Rows not loaded because all WHEN clauses were failed.

  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  82752 bytes(64 rows)

Read   buffer bytes: 1048576

Total logical records skipped :          0

Total logical records read :             5

Total logical records rejected :         0

Total logical records discarded :        0

Run began on Mon Mar 14 11:17:57 2011

Run ended on Mon Mar 14 11:18:00 2011

Elapsed time was:     00:00:03.25

CPU time was:         00:00:00.03

BAD File   :  It will be generated in the folder, where control file is located.

Registering  with Oracle Application

            Ensure that, the table is already exist in the database

            TOAD Login :            APPS/APPS@VIS

            create table EMP_TEST( empno number(10), ename varchar(50), deptno number(10))

1)      Develop the control file

LOAD DATA

INFILE *

discardfile 'C:\MNRAO\test.dis'

TRUNCATE  INTO TABLE  EMP_TEST

FIELDS TERMINATED   by ','

TRAILING NULLCOLS

( empno, ename,deptno  )

begindata

1001,Ramesh,55

1002,Sirisha,43

1004,Venkat,76

1005,Ramu,23

                                          (OR)

Control file  and Data file separately

Control file

LOAD DATA

INFILE  'C:\MNRAO\myemp.txt'

discardfile 'C:\MNRAO\test.dis'

TRUNCATE  INTO TABLE  EMP_TEST

FIELDS TERMINATED   by ','

TRAILING NULLCOLS

( empno, ename,deptno  )

Data File

1001,Ramesh,55

1002,Sirisha,43

1004,Venkat,76

1005,Ramu,23

Note : 1) When data file is using with oracle application then, the file should be provided with new line at the end of the data file  ( if new line is not provided, then last record will not be loaded into the table )

            2) When data file is using with at the command prompt then, the file should not have the new line at the end of the data file ( if new line is provided, then empty record will be loaded into the table )

Move the file from local machine to Server

D:\oracle\visappl\po\11.5.0\bin

2)      Create exe file with Execution Method as  SQL * LOADER

3)      Create concurrent program with Execution Method as  SQL * LOADER

         Repeat the remaining as explained in the previous examples

OPTIONALLY ENCLOSED BY  ' " '

It is used, If the data is in the following format.

1000, “name, S/o Father Name”, 5600

Here name and father name are of same field but the filed terminator ( , ) presents in the data 

Eg:

LOAD DATA

INFILE *

discardfile 'C:\MNRAO\test.dis'

TRUNCATE  INTO TABLE  ITEM_TEST

                        FIELDS TERMINATED   by ','    OPTIONALLY ENCLOSED BY  ' " '

TRAILING NULLCOLS

( Item_Code, Item_Name, Item_desc, Item_price   )

begindata

ACD01, Key Board , “ Computer Peripherals, Input device”, 350

BFS03, Monitor, “Computer Peripherals, Output device”, 6500

CFG21, Chair, “ Front Office, Reception”, 2300

GHT2A, Table, “Office, Conference Hall”, 8500

Control file with parameters

These are used to pass the data dynamically

For example we can pass the data file name dynamically as the input parameter

Syntax to pass the Parameters

“&1”

“&2”

“&3”

We must use the parameter names in a sequential order such as 1, 2, 3, 4…..100

The maximum parameters that we can define are 100    

Eg:

Table :

            create table ITEM_TEST(

                        Item_Code varchar2(10),

                        Item_Name varchar(50),

                        Item_desc varchar2(100),

                        Item_price  number(10))

Control file

            LOAD DATA

INFILE    ‘&1’

TRUNCATE  INTO TABLE  ITEM_TEST

FIELDS TERMINATED   by ','   

TRAILING NULLCOLS

( Item_Code, Item_Name, Item_desc, Item_price   )

Data file

ACD01,KeyBoard,Peripherals,350

BFS03,Monitor,Computer,6500

CFG21,Chair,Office,2300

GHT2A,Table,Conference Hall,8500

                 

1)  write control file as above

2)  move the control file from local machine to server

   D:\oracle\visappl\po\11.5.0\bin

3)  create executable with executable method as SQL * LOADER

4)  create the concurrent program with executable method as SQL * LOADER

define the parameter for data file

seq,     Parameter

1           Enter the Data File Path

Value Set : 100 characters

Here token is not necessary, reason is that, these parameters will be considered sequentially, where as with oracle reports there is no order for the parameters ( user parameters).