| SQL Loader issue [message #586764] |
Mon, 10 June 2013 08:55  |
 |
BlackJack@13
Messages: 4
Registered: June 2013
|
Junior Member |
|
|
Hi All,
can anyone assist me how to write the CTL file for this kind of situation.
a.txt
id name subject
12 aaa History
23 bbb Science
45 ccc Zoology
b.txt
id layer LayerNo
12 xxx12 1
23 yyy23 2
23 lll23 3
45 xxx45 1
45 yyy45 2
45 lll45 3
i have files a.txt which is parent file and another one is child one called file b.txt . Both files are linked together by common field called "id".
Interesting part child file have multiple layers name associated with ids. (we are only aware that in b.txt for each id there could be max 3 layers)
So they needs to get loaded into Table called PARENT_TBL
So PARENT_TABLE looks like
ID\tNAME\tSUBJECT\tLAYER\tLAYERNO
How I'm going to achieve this ?
[mod-edit: code tags added by bb]
[Updated on: Mon, 10 June 2013 17:30] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Loader issue [message #586808 is a reply to message #586767] |
Mon, 10 June 2013 17:53  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If your data files are not on your server, then you can use SQL*Loader to load the files into two staging tables, then use CTAS to create the parent_table or insert. Although SQL*Loader can load from multiple data files and can load into multiple tables, it can't join the data files. Please see the example below.
SCOTT@orcl_11gR2> host type a.txt
id name subject
12 aaa History
23 bbb Science
45 ccc Zoology
SCOTT@orcl_11gR2> host type b.txt
id layer LayerNo
12 xxx12 1
23 yyy23 2
23 lll23 3
45 xxx45 1
45 yyy45 2
45 lll45 3
SCOTT@orcl_11gR2> host type testa.ctl
load data
infile a.txt
into table a
fields terminated by x'09'
(id position(1), name, subject)
SCOTT@orcl_11gR2> host type testb.ctl
load data
infile b.txt
into table b
fields terminated by x'09'
(id position(1), layer, layerno)
SCOTT@orcl_11gR2> create table a
2 (id number,
3 name varchar2(15),
4 subject varchar2(15))
5 /
Table created.
SCOTT@orcl_11gR2> create table b
2 (id number,
3 layer varchar2(15),
4 layerno number)
5 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=testa.ctl log=testa.log
SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jun 10 15:48:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=testb.ctl log=testb.log
SQL*Loader: Release 11.2.0.1.0 - Production on Mon Jun 10 15:48:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
SCOTT@orcl_11gR2> select * from a
2 /
ID NAME SUBJECT
---------- --------------- ---------------
12 aaa History
23 bbb Science
45 ccc Zoology
3 rows selected.
SCOTT@orcl_11gR2> select * from b
2 /
ID LAYER LAYERNO
---------- --------------- ----------
12 xxx12 1
23 yyy23 2
23 lll23 3
45 xxx45 1
45 yyy45 2
45 lll45 3
6 rows selected.
SCOTT@orcl_11gR2> create table parent_table as
2 select a.id, a.name, a.subject, b.layer, b.layerno
3 from a, b
4 where a.id = b.id (+)
5 /
Table created.
SCOTT@orcl_11gR2> select * from parent_table
2 /
ID NAME SUBJECT LAYER LAYERNO
---------- --------------- --------------- --------------- ----------
12 aaa History xxx12 1
23 bbb Science yyy23 2
23 bbb Science lll23 3
45 ccc Zoology xxx45 1
45 ccc Zoology yyy45 2
45 ccc Zoology lll45 3
6 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
