Home » RDBMS Server » Server Utilities » Tricky Loading of file!! (Oracle 10g)
Tricky Loading of file!! [message #353592] |
Tue, 14 October 2008 05:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Serious Sam
Messages: 21 Registered: September 2007
|
Junior Member |
|
|
Gurus,
I have a very tricky requirement of loading the data from flat file into Oracle Table.
My Test file looks like this,
AA,Manager,4
BB,kiran,50000
BB,Rajesh,58500
AA,Analyst,3
BB,Rahul,90000
BB,Ramesh,29000
AA,Engineer,2
BB,Kiruba,38000
BB,Sailesh,74560
AA,Worker,6
BB,Sidharth,10000
BB,Maaran,8000
My Table Structures looks like this,
SQL> DESC ROLE_TAB
Name Null? Type
----------------------------------------- -------- -------------
UNIQ_CD VARCHAR2(20)
ROLE_NAME VARCHAR2(50)
CODE_NO NUMBER
SQL> DESC REC_TAB
Name Null? Type
----------------------------------------- -------- ---------------------
UNIQ_CD VARCHAR2(20)
EMP_NAME VARCHAR2(50)
ROLE_NAME VARCHAR2(50)
SALARY NUMBER
Below are the Create Table Scripts,
CREATE TABLE ROLE_TAB
(
UNIQ_CD VARCHAR2(20),
ROLE_NAME VARCHAR2(50),
CODE_NO NUMBER
);
CREATE TABLE REC_TAB
(
UNIQ_CD VARCHAR2(20),
EMP_NAME VARCHAR2(50),
ROLE_NAME VARCHAR2(50),
SALARY NUMBER
);
What i want is, rows with AA in first column should go to ROLE_TAB and rows with BB in first column should go to REC_TAB.
I have acheived this by writing a control file (SQL Loader with WHEN Clause). The problem is that ROLE_NAME of the REC_TAB should be populated based on the records in the flat file.
For Example, the BB records below AA,Manager,1 should be populated with Manager and BB records below AA,Analyst,2 should be populated with Analyst and so on..,
Could any one of you help me in fixing this issue either by control file or by external tables?
Thanks in advance!
|
|
|
Re: Tricky Loading of file!! [message #353718 is a reply to message #353592] |
Tue, 14 October 2008 17:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
One method would be to add a sequence column to each table, use the rows=1 option in sql*loader to process one row at a time, and use nextval and currval to put matching sequence numbers in the corresponding records, then update based on those, then you could drop the sequence columns. Please see the demonstration below.
-- starting tables:
SCOTT@orcl_11g> CREATE TABLE ROLE_TAB
2 (
3 UNIQ_CD VARCHAR2(20),
4 ROLE_NAME VARCHAR2(50),
5 CODE_NO NUMBER
6 );
Table created.
SCOTT@orcl_11g>
SCOTT@orcl_11g> CREATE TABLE REC_TAB
2 (
3 UNIQ_CD VARCHAR2(20),
4 EMP_NAME VARCHAR2(50),
5 ROLE_NAME VARCHAR2(50),
6 SALARY NUMBER
7 );
Table created.
-- test.ctl:
OPTIONS (ROWS=1)
LOAD DATA
INFILE test.dat
INTO TABLE role_tab
WHEN uniq_cd = 'AA'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(uniq_cd,
role_name,
code_no,
seq "test_seq.NEXTVAL")
INTO TABLE rec_tab
WHEN uniq_cd = 'BB'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(uniq_cd POSITION (1),
emp_name,
salary,
seq "test_seq.CURRVAL")
-- load:
SCOTT@orcl_11g> ALTER TABLE role_tab ADD (seq NUMBER)
2 /
Table altered.
SCOTT@orcl_11g> ALTER TABLE rec_tab ADD (seq NUMBER)
2 /
Table altered.
SCOTT@orcl_11g> CREATE SEQUENCE test_seq
2 /
Sequence created.
SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11g> UPDATE rec_tab
2 SET role_name =
3 (SELECT role_name
4 FROM role_tab
5 WHERE role_tab.seq = rec_tab.seq)
6 /
8 rows updated.
SCOTT@orcl_11g> ALTER TABLE rec_tab DROP COLUMN seq
2 /
Table altered.
SCOTT@orcl_11g> ALTER TABLE role_tab DROP COLUMN seq
2 /
Table altered.
-- results:
SCOTT@orcl_11g> COLUMN role_name FORMAT A20
SCOTT@orcl_11g> SELECT * FROM role_tab
2 /
UNIQ_CD ROLE_NAME CODE_NO
-------------------- -------------------- ----------
AA Manager 4
AA Analyst 3
AA Engineer 2
AA Worker 6
SCOTT@orcl_11g> COLUMN emp_name FORMAT A20
SCOTT@orcl_11g> SELECT * FROM rec_tab
2 /
UNIQ_CD EMP_NAME ROLE_NAME SALARY
-------------------- -------------------- -------------------- ----------
BB kiran Manager 50000
BB Rajesh Manager 58500
BB Rahul Analyst 90000
BB Ramesh Analyst 29000
BB Kiruba Engineer 38000
BB Sailesh Engineer 74560
BB Sidharth Worker 10000
BB Maaran Worker 8000
8 rows selected.
SCOTT@orcl_11g>
|
|
|
|
Goto Forum:
Current Time: Fri Jul 12 21:27:26 CDT 2024
|