Wednesday, March 23, 2016

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).

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...