?
Solved

oracle sql loader

Posted on 2015-01-28
5
Medium Priority
?
276 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

621 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