Home » RDBMS Server » Server Utilities » SQL Loader issue
SQL Loader issue [message #586764] Mon, 10 June 2013 08:55 Go to next message
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 #586765 is a reply to message #586764] Mon, 10 June 2013 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader issue [message #586767 is a reply to message #586765] Mon, 10 June 2013 09:52 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe the simplest option would be use of external tables feature. It allows you to write a query, while the "tables" you use are, actually, these two files. All you have to do is to write a SELECT statement which will combine these two tables the way you are satisfied with and use it in INSERT INTO statement which will insert data into the PARENT_TABLE.

Drawback of such a solution is a fact that files have to reside on a database server (not your client PC), but if that's not a problem, give it a try.
Re: SQL Loader issue [message #586808 is a reply to message #586767] Mon, 10 June 2013 17:53 Go to previous message
Barbara Boehmer
Messages: 7995
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.

Previous Topic: SQL Loader - High Volume of Date
Next Topic: SQLLDR Squence Number
Goto Forum:
  


Current Time: Sat Oct 25 23:52:17 CDT 2014

Total time taken to generate the page: 0.08766 seconds