[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Loader

Posted on 2011-05-02
15
Medium Priority
?
655 Views
Last Modified: 2012-05-11
In the control file for SQL Loader is there a way to specify in the when clause to load a specific row of data into a specific table?  For example, I want my 2nd row of data to load into TABLE1 and all other rows to load into table 2.

RowHeadingsfor Table1
Row2ofdatacolumns
RowHeadingsfor Table2

Col1|Col2|Col3|Col4|Col5
1|2|3|4|5|
Tab2Col1|TAb2|Col2|Tab2|Col3|TAb2|Col4
0
Comment
Question by:newtoperlpgm
  • 6
  • 6
  • 3
15 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35509049
You can do this if there is some way to tell the difference in the rows other than line number.  At least I'm not sure of a way based on file position.

Is there something in the data that gives a clue what goes where?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35512688
Since you never posted back I'm not sure if you can delimit the rows in some way.

You can run sql loader twice.
First skip one row then only load one:
SKIP=1 LOAD=1

Then just SKIP=2 to load the rest
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35512872
- based on your file format, it is quite difficult to load the data. if you have an indicator/'row marker' to differentiate the data on the 2nd row and the 3rd row and the following row (as illustrate below) then you can use WHEN function to differentiate the rows. i use '1' and '2' in the first column as a 'row marker':

Col1|Col2|Col3|Col4|Col5
1|Tab1Col1|Tab1Col2|Tab1Col3|Tab1Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4

- the following code use WHEN to differentiate the row for table1 and table2. (01) is the first position of the record. then use POSITION and FILLER to get to the beginning of the record and skip the first column which is the 'row marker'.
 
LOAD DATA
INFILE   'datafile.dat'
APPEND
 INTO TABLE table1
 WHEN (01) = '1'
FIELDS TERMINATED BY "|"
( rec_skip filler POSITION(1), column1 , column2 )
INTO TABLE table2
  WHEN (01) = '2'
FIELDS TERMINATED BY "|"
(rec_skip filler POSITION(1), column1, column2 )

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:newtoperlpgm
ID: 35513459
The data files formats are making this quite difficult, that is why I posted the question, to see if there was an easy way I may be missing to skip the column header rows.  I use WHEN statements, but they don't seem to be working correctly, unfortunately.  I am going to try the proposed solutions here and I will post my results, hopefully success.  Thanks.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35513492
>>easy way I may be missing to skip the column header rows

I thought I posted that in http:#a35512688?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35513698
- actually by using WHEN together with the 'row marker', you can skip the header row by not specifying a 'row marker' for that row OR specify any marker different from the 'row marker' for data.
0
 

Author Comment

by:newtoperlpgm
ID: 35699759
Is there any way to skip line 1 and line 3 in one options statement?
SKIP=1 LOAD=1

Then just SKIP=2 to load the rest
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35699789
in posting ID35512872, if you did not set the marker for line 1 and 3, then using the WHEN it will not take the data. the marker can be any number/char that is unique:

Col1|Col2|Col3|Col4|Col5
1|Tab1Col1|Tab1Col2|Tab1Col3|Tab1Col4
Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4
2|Tab2Col1|Tab2Col2|Tab2Col3|Tab2Col4

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35699797
I've never seen a way to specify more than one OPTIONS clause in a single controlfile.

I'm not sure what you mean by skip line 1 and 3.
0
 

Author Comment

by:newtoperlpgm
ID: 35701114
OP_Zaharin:  can I set my row marker in my control file?  My data files won't have row numbers/markers.
Thanks.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35701129
I talked about this in the first two posts:  If you cannot 'flag/tag/identify' the rows in some way other than row position, you cannot use WHEN.

I guess I should say:  I don't know how to use WHEN for row position.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35702844
- no u can't, u specify which marker to read and skip in your control file. you set the marker, in you data file.

- i would suggest you to write a program to load the data, not using sqlloader. i used to write a vb program (long time ago) to load data into my database.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35703046
Write/debug a program to keep from calling sql loader twice?

I probably wouldn't.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35703160
- write a program to read the data file and load the data to the database. no sqlloader involve.
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 1000 total points
ID: 35726224
newtoperlpgm,
- if you don't mind, can you share with us 4-5 lines of your DAT file? we need to understand further the format of your file.

- looking at your example data format, can i say that you want to:
i- skip line 1
ii- insert line 2 data into table1
iii- skip line 3
iv- insert line 4 and the rest into table2
 
Col1|Col2|Col3|Col4|Col5
1|2|3|4|5|
Tab2Col1|TAb2|Col2|Tab2|Col3|TAb2|Col4

- if the above is true, can i know if the 1st column of the 2nd row data is unique? assuming that if it is unique, i'm preparing the following example to illustrate my code. this is the close that i can get. there might be other way of doing this but i haven't found any yet:

-- the DAT file
-- assuming the column1 in line2 data (=1) is unique from the rest of the line:

Col1|Col2|Col3|Col4|Col5
1|2|3|4|5|
Tab2Col1|TAb2Col2|Tab2Col3|TAb2Col4
2|3|4|5|
12|13|14|15|
22|23|24|25|

Open in new window


-- the CTL file
-- SKIP 1 : will skip the 1st line header
-- WHEN col1 = '1' : this condition check unique data in column1 for line 2 and insert to table1
-- WHEN col1 <> 'Tab2Col1' AND col1 <> '1' : this condition check the rest if the data and skip the 2nd header ('Tab2Col1') and 2nd line data in col1 that is unique from other data '1'

OPTIONS (SKIP=1)
LOAD DATA 
INSERT INTO TABLE table1
WHEN col1 = '1'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
(  
col1,
col2,
col3,
col4,
col5
)
INTO TABLE table2
WHEN col1 <> 'Tab2Col1' AND col1 <> '1'
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 TRAILING NULLCOLS
(  
col1 POSITION(1),
col2,
col3,
col4
)

Open in new window


- the data inserted to the table will be as follows:

Table1
======
1 | 2 | 3 | 4 | 5

Table2
======
2 | 3 | 4 | 5
12 | 13 | 14 | 15
22 | 23 | 24 | 25
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

834 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