Total Pageviews

July 21, 2015

7/21/2015 02:28:00 AM
CASE STUDY (Loading Data from Fixed Length file into Oracle)
Suppose we have a fixed length format file containing employees data, as shown below, and wants to load this data into an Oracle table.
7782 CLARK       MANAGER       7839      2572.50                  10
7839 KING         PRESIDENT                      5500.00                  10
7934 MILLER      CLERK              7782       920.00                    10
7566 JONES       MANAGER       7839      3123.75                   20
7499 ALLEN        SALESMAN     7698       1600.00   300.00  30
7654 MARTIN     SALESMAN     7698      1312.50   1400.00 30
7658 CHAN         ANALYST         7566      3450.00                   20
7654 MARTIN     SALESMAN     7698      1312.50   1400.00 30
 SOLUTION:
Steps :-
1.      First Open the file in a text editor and count the length of fields, for example in our fixed length file, employee number is from 1st position to 4th position, employee name is from 6th position to 15th position, Job name is from 17th position to 25th position. Similarly other columns are also located.
2.      Create a table in Oracle, by any name, but should  match columns specified in fixed length file. In our case give the following command to create the table.
SQL> CREATE TABLE emp (empno  NUMBER(5),
        name VARCHAR2(20),
        job  VARCHAR2(10),
        mgr  NUMBER(5),
        sal  NUMBER(10,2),
        comm NUMBER(10,2),
        deptno     NUMBER(3));
                      
3.      After creating the table, now write a control file by using any text edit
  1)   LOAD DATA
  2)   INFILE '/u01/oracle/fix.dat'
  3)   INTO TABLE emp
  4)   (empno         POSITION(01:04)   INTEGER EXTERNAL,
       name         POSITION(06:15)   CHAR,
       job          POSITION(17:25)   CHAR,
       mgr          POSITION(27:30)   INTEGER EXTERNAL,
       sal          POSITION(32:39)   DECIMAL EXTERNAL,
       comm         POSITION(41:48)   DECIMAL EXTERNAL,
  5)   deptno         POSITION(50:51)   INTEGER EXTERNAL)
Notes:

(Do not write the line numbers, they are meant for explanation purpose)

  1.       The LOAD DATA statement is required at the beginning of the control file.
  2.       The name of the file containing data follows the INFILE parameter.
  3.       The INTO TABLE statement is required to identify the table to be loaded into.
  4.       Lines 4 and 5 identify a column name and the location of the data in the datafile to be loaded into that column. empno, name, job, and so on are names of            columns in table emp. The datatypes (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) identify the datatype of data fields in the file, not of corresponding            columns in the emp table.
  5.       Note that the set of column specifications is enclosed in parentheses.

Loading Data into Multiple Tables using WHEN condition
You can simultaneously load data into multiple tables in the same session. You can also use WHEN condition to load only specified rows which meets a particular condition (only equal to “=” and not equal to “<>” conditions are allowed).
For example, suppose we have a fixed length file as shown below
7782 CLARK       MANAGER       7839       2572.50              10
7839 KING         PRESIDENT                      5500.00               10
7934 MILLER      CLERK              7782        920.00                 10
7566 JONES       MANAGER       7839       3123.75                20
7499 ALLEN        SALESMAN      7698       1600.00   300.00 30
7654 MARTIN     SALESMAN       7698    1312.50  1400.00 30
7658 CHAN         ANALYST           7566    3450.00                  20
7654 MARTIN     SALESMAN        7698   1312.50  1400.00 30


Now we want to load all the employees whose deptno is 10 into emp1 table and those employees whose deptno is not equal to 10 in emp2 table.
To do this first create the tables emp1 and emp2 by taking appropriate columns and datatypes. Then, write a control file as shown below
load Data
infile ‘/u01/oracle/empfix.dat’
append into table emp1
WHEN (deptno='10')
  (empno        POSITION(01:04)   INTEGER EXTERNAL,
   name         POSITION(06:15)   CHAR,
   job          POSITION(17:25)   CHAR,
   mgr          POSITION(27:30)   INTEGER EXTERNAL,
   sal          POSITION(32:39)   DECIMAL EXTERNAL,
   comm         POSITION(41:48)   DECIMAL EXTERNAL,
   deptno       POSITION(50:51)   INTEGER EXTERNAL)

INTO TABLE emp2
  WHEN (deptno<>'10')
  (empno        POSITION(01:04)   INTEGER EXTERNAL,
   name         POSITION(06:15)   CHAR,
   job          POSITION(17:25)   CHAR,
   mgr          POSITION(27:30)   INTEGER EXTERNAL,
   sal          POSITION(32:39)   DECIMAL EXTERNAL,
   comm         POSITION(41:48)   DECIMAL EXTERNAL,
   deptno       POSITION(50:51)   INTEGER EXTERNAL)

Using Sequences in sql Loader

LOAD DATA
INFILE *
APPEND INTO TABLE XXI.XX_VENDOR_HEADER
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
VENDOR_NAME
,VENDOR_TYPE_LOOKUP_CODE
,VENDOR_INTERFACE_ID     "AP_SUPPLIERS_INT_S.NEXTVAL"
)

using functions in SQL*loader
LOAD DATA
INFILE *
APPEND INTO TABLE XXI.XX_VENDOR_HEADER
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
VENDOR_NAME
,VENDOR_TYPE_LOOKUP_CODE
,VENDOR_INTERFACE_ID     "AP_SUPPLIERS_INT_S.NEXTVAL"
,vendor_name "substr(:vendor_name,1,10)"
)

comma  separated type
A)  options  ( skip=1 )
 B)  load data
 C)    infile               'Data.csv'          
 D)    truncate into table   scott.sql_loader_demo_simple
 E)  fields terminated by ","      
 F)  optionally enclosed by '"'
 G)    ( customer_full_name
 H)    , account_balance_amt
 I)    , account_start_date   DATE "Mon-DD-YYYY HH:MI:SS am"
 J)    )

   Line A   = Skip the header row of the CSV file.
              If there is no header row it would be: (skip=0).  
    Line B   = This is the command to start loading data.
   Line C   = This is the name of your CSV data file.
   Line D   = This is the schema and name of your Oracle table. 
              The "truncate" specifies that the existing data in the
              table will be truncated or erased prior to the load.
   Line E   = This is the symbol used to separate values in your CSV file.
   Line F   = This allows CSV values to be enclosed in double-quotes.
   Line G-J = This is the list of columns to be loaded.
              The order of this list comes from the CSV file
              and the column names come from the table.
  Line I   = Because this is a date column, the format of the

              dates in the CSV data needs to be specified as shown.