Solved

oracle sql loader

Posted on 2015-01-28
5
246 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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now