?
Solved

SQL Loader control file

Posted on 2009-04-10
25
Medium Priority
?
1,393 Views
Last Modified: 2013-12-12
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
)
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

Open in new window

0
Comment
Question by:srikanthradix
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
25 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118494
Can you examine the log file and see whats the error?
0
 

Author Comment

by:srikanthradix
ID: 24118605
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
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118634
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:srikanthradix
ID: 24118665
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
0
 

Author Comment

by:srikanthradix
ID: 24118680
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.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118738

--instead of
TABLE TABLE2 WHEN FILLERCOL = '2'
can you try
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'

Open in new window

0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118749
corrected post
--instead of
INTO TABLE TABLE2 WHEN FILLERCOL = '2'
--can you try
INTO TABLE TABLE2 WHEN FILLERCOL <> '1'

Open in new window

0
 

Author Comment

by:srikanthradix
ID: 24118784
@ajexpert:
it is still not loading into table2.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118790
can u send  me the log file?
0
 

Author Comment

by:srikanthradix
ID: 24118815
Please find the log file attached.

data.txt
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118838
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

--instead of
INTO TABLE TABLE1 WHEN FILLERCOL = '1'
--can you try
INTO TABLE TABLE1 WHEN FILLERCOL <> '1'
 

Open in new window

0
 

Author Comment

by:srikanthradix
ID: 24118910
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
)
0
 

Author Comment

by:srikanthradix
ID: 24118916
Please note that rows which have FILLERCOL as 3 and 4 should be discarded. Sorry, i did not mention this before.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24118928
ok, try this.
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
)

Open in new window

0
 

Author Comment

by:srikanthradix
ID: 24118997
It is still not loading. Please find the log file attached.

data.txt
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24119008
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
)

Open in new window

0
 

Author Comment

by:srikanthradix
ID: 24119046
no load again. log file attached.

data.txt
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24119070
strange...
what if you eliminate the WHEN CLAUSE altogether
Can you try the simple load without filter condition i.e. without WHEN
0
 

Author Comment

by:srikanthradix
ID: 24119161
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
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24119182
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.
0
 

Author Comment

by:srikanthradix
ID: 24119201
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
)


0
 

Author Comment

by:srikanthradix
ID: 24119277
table1 and table2 structures attached.

table-structure.txt
0
 
LVL 14

Accepted Solution

by:
ajexpert earned 2000 total points
ID: 24119842
You have to make use of POSITION clause to force it reading file from start

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   POSITION(1)        INTEGER EXTERNAL TERMINATED BY '|'
    , FILLERCOL         FILLER TERMINATED BY '|'
    , COLUMN2           INTEGER EXTERNAL
    , COLUMN3           INTEGER EXTERNAL
    , COLUMN4           INTEGER EXTERNAL
    , COLUMN5           INTEGER EXTERNAL
)

Open in new window

0
 

Author Closing Comment

by:srikanthradix
ID: 31569029
Thank you for your effort and time.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24119889
Glad that I could help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question