Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle sql loader

Posted on 2015-01-28
5
Medium Priority
?
275 Views
Last Modified: 2015-01-30
Hi,

I would like to know multiline data with sql loader.
My requirement is I need to load the data from "AMP" prefixed number till I get the "row" which contains the next "AMP" prefixed number.
One record can be in two lines or 3 lines. If we load the empty columns before the AMP prefixed column also can be loaded. But in my sample create table I did not add those columns to store the null values.
 Here I attached the data file and control file and create table script. Can you please let me know how can I achieve this. I tried to use continueif clause. But if the multiline is more than 2 rows then it is not reading the 3rd record.

Thank you in advance.

Thanks.
0
Comment
Question by:GouthamAnand
  • 3
  • 2
5 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40575968
What happened to the files you had originally uploaded?

Also post the expected results after the data was loaded.
0
 

Author Comment

by:GouthamAnand
ID: 40577780
Hi,

I have table like below.

create table tb_item(item_no varchar2(10),weight number,volume number);

And I have csv file(semi colon separated)  containing the data like below.

Item1;1;1;
;11;11;
;111;111;
Item2;2;2;
;22;22;
Item3;3;3;
;33;33;
;333;333;

But I need to load the data as if the data file contains the data like below

Item1;1;1;
Item1;11;11;
Item1;111;111;
Item2;2;2;
Item2;22;22;
Item3;3;3;
Item3;33;33;
Item3;333;333;

ie 2nd and 3rd rows does not contain item1 (in the data file) but I need to load as if they are there. Like same for 5 th row which is not containing item2 and also 7 the 8 th rows for item3.

ie after loading my table should contain the data like below

select * from tb_item;

Item1      1      1
Item1      11      11
Item1      111      111
Item2      2      2
Item2      22      22
Item3      3      3
Item3      33      33
Item3      333      333

Can you please suggest how can I achieve this?

Thanks,
Sai
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40578275
I'm not sure if you can perform sort of a back-reference using sql loader.  I'm still looking to see if I can come up with some magic.

As a possible work-around:
Create a staging table.
Load the data into it.
Then insert into the regular table.

create table tb_item_stage(item_no varchar2(10),
	weight1 number,volume1 number,
	weight2 number,volume2 number,
	weight3 number,volume3 number
	);

[\code]

Control file (I used inline data but you can use a flat file):
[code]

load data
infile *
continueif next preserve (1:1) = ';'
into table tb_item_stage
truncate
fields terminated by ";"
trailing NULLCOLS
(
item_no,
weight1,
volume1,
junk1 filler,
weight2,
volume2,
junk2 filler,
weight3,
volume3
)
begindata
Item1;1;1;
;11;11;
;111;111;
Item2;2;2;
;22;22;
Item3;3;3;
;33;33;
;333;333;

Open in new window


insert into tb_item
select item_no, weight1, volume1 from tb_item_stage where weight1 is not null
union all
select item_no, weight2, volume2 from tb_item_stage where weight2 is not null
union all
select item_no, weight3, volume3 from tb_item_stage where weight3 is not null
/

Open in new window

0
 

Author Comment

by:GouthamAnand
ID: 40579499
Thank you very much.
This exactly suits my requirement.
0
 

Author Closing Comment

by:GouthamAnand
ID: 40579503
Thank you very much.
This solution exactly suits my requirement.
0

Featured Post

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.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

971 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