srikanthradix
asked on
SQL Loader control file
This is the control file i am using to load into multiple tables. But, it is loading only into one table table1. Please suggest. I have copied the sample data into code block.
LOAD DATA
APPEND
INTO TABLE TABLE1 WHEN FILLERCOL = '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE2 WHEN FILLERCOL = '2'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
LOAD DATA
APPEND
INTO TABLE TABLE1 WHEN FILLERCOL = '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE2 WHEN FILLERCOL = '2'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
2012076490|1|6835319,7140975,7411435,7232743
2012076490|2|658893,695333,695421,688091
2012076490|3|3899,29732,7288,178991,29941
2012131180|1|6835319,7140975,7411435,7232743
2012131180|2|658893,695333,695421,688091
2012131180|3|3899,29732,7288,178991
2012131180|4|GR000016,GR000017,GR000020,GR000002
2012189150|1|6835319,7140975,7411435,7232743
2012189150|2|658893,695333,695421,688091
2012189150|3|3899,29732,7288,178991
Can you examine the log file and see whats the error?
ASKER
Please find the log file attached.
I ran the sql loader like this.
$ sqlldr control = control.ctl data = data.txt log = data.log bad = data.bad discard = data.discard userid = $userid/$pass
SQL*Loader: Release 10.2.0.3.0 - Production on Fri Apr 10 15:38:57 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 10
data.txt
I ran the sql loader like this.
$ sqlldr control = control.ctl data = data.txt log = data.log bad = data.bad discard = data.discard userid = $userid/$pass
SQL*Loader: Release 10.2.0.3.0 - Production on Fri Apr 10 15:38:57 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 10
data.txt
Dude,
I dont think you are able to load in table1 too.
The data types in table and format in the file is not matching. So is the error.
Tip:
Try loading only one record in one table till the data is successfully loaded in Table1. Once you are set, you can load more records.
HTH
I dont think you are able to load in table1 too.
The data types in table and format in the file is not matching. So is the error.
Tip:
Try loading only one record in one table till the data is successfully loaded in Table1. Once you are set, you can load more records.
HTH
ASKER
I apologize for that. I ftp'ed with ASCII format. Hence they were not loaded into table1
Please find the log file attached.
data.txt
Please find the log file attached.
data.txt
ASKER
In a site, i found this information regarding loading into multiple tables. But, i do not use position delimiter. please suggest.
http://www.orafaq.com/wiki/SQL*Loader_FAQ
Loading into multiple tables
One can also specify multiple "INTO TABLE" clauses in the SQL*Loader control file to load into multiple tables. Look at the following example: LOAD DATA
INFILE *
INTO TABLE tab1 WHEN tab = 'tab1'
( tab FILLER CHAR(4),
col1 INTEGER
)
INTO TABLE tab2 WHEN tab = 'tab2'
( tab FILLER POSITION(1:4),
col1 INTEGER
)
BEGINDATA
tab1|1
tab1|2
tab2|2
tab3|3
The "tab" field is marked as a FILLER as we don't want to load it.
Note the use of "POSITION" on the second routing value (tab = 'tab2'). By default field scanning doesn't start over from the beginning of the record for new INTO TABLE clauses. Instead, scanning continues where it left off. POSITION is needed to reset the pointer to the beginning of the record again.
http://www.orafaq.com/wiki/SQL*Loader_FAQ
Loading into multiple tables
One can also specify multiple "INTO TABLE" clauses in the SQL*Loader control file to load into multiple tables. Look at the following example: LOAD DATA
INFILE *
INTO TABLE tab1 WHEN tab = 'tab1'
( tab FILLER CHAR(4),
col1 INTEGER
)
INTO TABLE tab2 WHEN tab = 'tab2'
( tab FILLER POSITION(1:4),
col1 INTEGER
)
BEGINDATA
tab1|1
tab1|2
tab2|2
tab3|3
The "tab" field is marked as a FILLER as we don't want to load it.
Note the use of "POSITION" on the second routing value (tab = 'tab2'). By default field scanning doesn't start over from the beginning of the record for new INTO TABLE clauses. Instead, scanning continues where it left off. POSITION is needed to reset the pointer to the beginning of the record again.
--instead of
TABLE TABLE2 WHEN FILLERCOL = '2'
can you try
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'
corrected post
--instead of
INTO TABLE TABLE2 WHEN FILLERCOL = '2'
--can you try
INTO TABLE TABLE2 WHEN FILLERCOL <> '1'
ASKER
@ajexpert:
it is still not loading into table2.
it is still not loading into table2.
can u send me the log file?
ASKER
ok,
Lets try this. Note both are inserting records into table1.
With this you should be able to load all 10 records in table1.
Let me know along with log file
Lets try this. Note both are inserting records into table1.
With this you should be able to load all 10 records in table1.
Let me know along with log file
--instead of
INTO TABLE TABLE1 WHEN FILLERCOL = '1'
--can you try
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'
ASKER
Do you want me to write like this? Sorry about that, i was a little confused.
LOAD DATA
APPEND
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE2 WHEN FILLERCOL <> '1' AND FILLERCOL <> '3' AND FILLERCOL <> '4'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
LOAD DATA
APPEND
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE2 WHEN FILLERCOL <> '1' AND FILLERCOL <> '3' AND FILLERCOL <> '4'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
ASKER
Please note that rows which have FILLERCOL as 3 and 4 should be discarded. Sorry, i did not mention this before.
ok, try this.
6 records should be loaded in table1
6 records should be loaded in table1
LOAD DATA
APPEND
INTO TABLE TABLE1 WHEN FILLERCOL = '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE1 WHEN FILLERCOL = '2'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
ASKER
can you try
LOAD DATA
APPEND
INTO TABLE TABLE1 WHEN FILLERCOL = '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
ASKER
strange...
what if you eliminate the WHEN CLAUSE altogether
Can you try the simple load without filter condition i.e. without WHEN
what if you eliminate the WHEN CLAUSE altogether
Can you try the simple load without filter condition i.e. without WHEN
ASKER
if i tried without the when condition all the records are getting loaded in the table1, none into table2. Please find the zip file which contains all the files.
sqlldr.zip
sqlldr.zip
If data is being loaded in table1 but not in table2, means the datatype of table2 is not compatible with incoming data
Can you send me the structure of TABLE1 AND TABLE2
OR
Compare the structure of TABLE1 AND TABLE2.
Can you send me the structure of TABLE1 AND TABLE2
OR
Compare the structure of TABLE1 AND TABLE2.
ASKER
Actually, i tried like this in reverse, it loaded into table2 but not into table1.
LOAD DATA
INFILE 'data.txt'
BADFILE 'data.bad'
DISCARDFILE 'data.discard'
APPEND
INTO TABLE TABLE2
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE1
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
LOAD DATA
INFILE 'data.txt'
BADFILE 'data.bad'
DISCARDFILE 'data.discard'
APPEND
INTO TABLE TABLE2
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
INTO TABLE TABLE1
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
COLUMN1 INTEGER EXTERNAL TERMINATED BY '|'
, FILLERCOL FILLER TERMINATED BY '|'
, COLUMN2 INTEGER EXTERNAL
, COLUMN3 INTEGER EXTERNAL
, COLUMN4 INTEGER EXTERNAL
, COLUMN5 INTEGER EXTERNAL
)
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your effort and time.
Glad that I could help