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.
