Home » RDBMS Server » Server Utilities » SQL LOADER (ORACLE 10g)
SQL LOADER [message #321807] Wed, 21 May 2008 06:34 Go to next message
icebox_xxx_13
Messages: 7
Registered: May 2008
Location: Philippines
Junior Member
Hi! can anyone help me about sql loader? i've been searching lots of forums but did not found any answers.

My problem is this. I have an interface file to upload to my database. This file consist of the following fields:

"AA","AAA, AAA","AAAA"
"BB","BBB, BBB","BBBB"
"CC","CCC, CCC","CCCC"

I need to append these file into 2 tables. My expected output is this:

table A

Column1  Column2
AA       AAA, AAA
BB       BBB, BBB
CC       CCC, CCC

table B

Column1   Column2
AA         AAAA
BB         BBBB
CC         CCCC


can anyone help me with this? using sql*loader?
Thanks in advance!



[mod-edit: added code tags]

[Updated on: Wed, 21 May 2008 22:22] by Moderator

Report message to a moderator

Re: SQL LOADER [message #321816 is a reply to message #321807] Wed, 21 May 2008 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For myself, I would use external table far more easier than SQL*Loader but have a look at Benefits of Using Multiple INTO TABLE Clauses

And also: "Case Study 5: Loading Data into Multiple Tables", ulcase5.* files in $ORACLE_HOME/rdbms/demo directory.

Regards
Michel

[Updated on: Wed, 21 May 2008 06:53]

Report message to a moderator

Re: SQL LOADER [message #321946 is a reply to message #321807] Wed, 21 May 2008 21:33 Go to previous messageGo to next message
icebox_xxx_13
Messages: 7
Registered: May 2008
Location: Philippines
Junior Member
thx for your time sir. i rily need to know if it is possible to end up with the result i am expecting using sql*Loader without using POSITION? thx!
Re: SQL LOADER [message #321950 is a reply to message #321946] Wed, 21 May 2008 22:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
-- test.dat:
"AA","AAA, AAA","AAAA"
"BB","BBB, BBB","BBBB"
"CC","CCC, CCC","CCCC"


-- test.ctl:
load data
infile test.dat
into table a
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(column1, column2)
into table b
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(column1 position (1), filler1 filler, column2)


SCOTT@orcl_11g> CREATE TABLE a
  2    (column1 VARCHAR2 (8),
  3  	column2 VARCHAR2 (8))
  4  /

Table created.

SCOTT@orcl_11g> CREATE TABLE b
  2    (column1 VARCHAR2 (8),
  3  	column2 VARCHAR2 (8))
  4  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM a
  2  /

COLUMN1  COLUMN2
-------- --------
AA       AAA, AAA
BB       BBB, BBB
CC       CCC, CCC

SCOTT@orcl_11g> SELECT * FROM b
  2  /

COLUMN1  COLUMN2
-------- --------
AA       AAAA
BB       BBBB
CC       CCCC

SCOTT@orcl_11g> 

Re: SQL LOADER [message #322662 is a reply to message #321807] Sun, 25 May 2008 20:38 Go to previous message
icebox_xxx_13
Messages: 7
Registered: May 2008
Location: Philippines
Junior Member
thanks... it worked fine!
Previous Topic: export parameter clarificationsq
Next Topic: SQL LOADER (Oracle 10g)
Goto Forum:
  


Current Time: Tue Dec 24 10:11:03 CST 2024