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
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!

Report message to a moderator

Re: Tricky Loading of file!! [message #353718 is a reply to message #353592] Tue, 14 October 2008 17:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
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>

Report message to a moderator

Re: Tricky Loading of file!! [message #353739 is a reply to message #353592] Tue, 14 October 2008 23:29 Go to previous message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member
Barabara,

You are my ALL TIME FAVOURITE!! Thank you so much!!

Thanks,
Sam

Report message to a moderator

Previous Topic: Date function validation 'merged)
Next Topic: Initiate a DataPump from a Procedure
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 24 11:28:38 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.