Solved

oracle sql loader

Posted on 2015-01-28
5
270 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
[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
  • 3
  • 2
5 Comments
 
LVL 77

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

752 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