Nih ada contoh case study yang aku ambil dari website oracle, aku dah
implementkan cara di bawah ini dan berhasil.
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 :-
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.
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) );
After creating the table, now write a control file by using
any text editor
$vi empfix.ctl
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)
The LOAD DATA statement is required at the beginning of the
control file.
The name of the file containing data follows the INFILE
parameter.
The INTO TABLE statement is required to identify the table to
be loaded into.
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.
Note that the set of column specifications is enclosed in
parentheses.
After
saving the control file now start SQL Loader utility by typing the following
command.
$sqlldr
userid=scott/tiger control=empfix.ctl log=empfix.log direct=y
After
you have executed the above command SQLLoaderwill shows you the output
describing how
many rows it has loaded.
Moga dapat membantu. Untuk dapat info lebih jelas lagi silakan klik di sini
http://www.oracle-dba-online.com/sql_loader.htm
rgds,
kusnandar
________________________________
From: boss_ayi <[email protected]>
To: [email protected]
Sent: Friday, June 5, 2009 4:00:39 PM
Subject: [indo-oracle] sql loader
mau nanya pak/buk :
misalnya saya punya data.dat seperti berikut:
data.dat
============ ========= ===
Format : ....
File_Type : ...
Total File : ...
Event :"1","a","b" ,"c"
Event :"2","d","e" ,"f"
Event :"3","g","a" ,"b"
End_of_file
============ ========= ===
nah pertanyaannya:
bisa ga kalo saya mau ambil data yang event-nya saja trus di load ke
databasenya, dengan menggunaklan SQL LOADER?
seperti :
"1","a","b", "c"
"2","d","e", "f"
"3","g","a", "b"
kalo bisa gimana caranya? mohon pencerahan
[Non-text portions of this message have been removed]
