Solved

How to load multiple lines in one record?

Posted on 2015-02-12
20
175 Views
Last Modified: 2015-02-13
create table T(
C1 number ,
C2 varchar2(4000),
C3 varchar2(50) ) ;

Want to load the data below. For example, 2 records:

10|^/dir10/dir9/, /dir8/,
/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/bbbbbbbbbbbbbbbbbbbbbbb/,
/ccccccccccccccccccccc/dddddd,^|^This is a test^|
20|^'/ffffffffffffffffffffffffffff/gggggggggggggggg', /hhhhhhhhhhhhhhhhhhhhhhhhhhh/gggggggggg /jjjjjjjjjj/
mmmmmmmmmmmm/ /nnnnnnnnnnnnn/pppppppppppppppppp/qqqqqq
ooooooooooo/pppppppppppp, dddddddd/
rrrrrrrrrrrr/sssssssssssss/tttttttt^|^The one is also a test^|

... many lines

The data is delimited by | and optionally by ^. The multiple variable lines in one record need to be loaded into  column C2. How to write the control file if I want to load the data into T? Can the format be kept?

Thanks
0
Comment
Question by:jl66
  • 9
  • 7
  • 4
20 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40606465
Is there something that lets you know if the next line is a continuation of the previous record or a new record?

If so, look at CONTINUEIF:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL1102

If not, then you might be out of luck.
0
 

Author Comment

by:jl66
ID: 40606537
Thanks for the info. However I tried a few, but could not get there.

1st record:

10|^/dir10/dir9/, /dir8/,
/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/bbbbbbbbbbbbbbbbbbbbbbb/,
/ccccccccccccccccccccc/dddddd,^|^This is a test^|

2nd record:

20|^'/ffffffffffffffffffffffffffff/gggggggggggggggg', /hhhhhhhhhhhhhhhhhhhhhhhhhhh/gggggggggg /jjjjjjjjjj/
mmmmmmmmmmmm/ /nnnnnnnnnnnnn/pppppppppppppppppp/qqqqqq
ooooooooooo/pppppppppppp, dddddddd/
rrrrrrrrrrrr/sssssssssssss/tttttttt^|^The one is also a test^|

What you said is that we can not load them in??
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 40606572
If the data is strictly delimited, then I am not aware of a way to load it either.

As suggested, the CONTINUEIF parameter is helpful if you can get something in the data to determine where a record ends.

Another parameter to look at is CONCATENATE.  But, that would only be helpful if the number of lines in a record is constant.  The doc for that parameter is here -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#SUTIL1101  It is actually the section before the link that slightwv posted.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40606635
>>What you said is that we can not load them in??

I figured the 'new' record started with a number from what you posted.

The problem is a 'human' can infer.  SQL Loader cannot.  You need to tell it the rules of engagement.

If you can say that 100% of the time a number in column 1 defines a new record, then we have something to work with and it is likely possible.

If the data meant for C2 or C3 can contain numbers and the line break might possibly mean there is a number in column 1 of the data, then I don't know of a way.
0
 

Author Comment

by:jl66
ID: 40606768
Yes. Surely C1 is a number. In real situation, it is a PK.
0
 

Author Comment

by:jl66
ID: 40606770
Also the last column is vachar2 for sure.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40606826
Neither of those posts really help.

In the data will the first column in the data ALWAYS, 100% of the time, be a number 0-9 when a new record starts?

In other words:  is this possible in the data (notice the 6 or row C1=20:
10|^/dir10/dir9/, /dir8/,
/aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa/bbbbbbbbbbbbbbbbbbbbbbb/,
/ccccccccccccccccccccc/dddddd,^|^This is a test^|
20|^'/ffffffffffffffffffffffffffff/gggggggggggggggg', /hhhhhhhhhhhhhhhhhhhhhhhhhhh/gggggggggg /jjjjjjjjjj/
6mmmmmmmmmmmm/ /nnnnnnnnnnnnn/pppppppppppppppppp/qqqqqq
ooooooooooo/pppppppppppp, dddddddd/
rrrrrrrrrrrr/sssssssssssss/tttttttt^|^The one is also a test^|
0
 

Author Comment

by:jl66
ID: 40607217
Q: In the data will the first column in the data ALWAYS, 100% of the time, be a number 0-9 when a new record starts?
A: Yes. In addition, C1 is a unique number for each record. Never being a null.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40607639
Not the first column in the record.  The first character on every line in the file.  Is there any possible way that the data on the continued lines can begin with a number?  Basically, can the C2 column in the table contain a number, anywhere in the data?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40607745
To reinforce what johnsone posted:

Take a look at the sample I posted in http:#a40606826

See the wrapped line that starts with a 6?  Is that possible in the real data?

Can ANY line other than the start of a NEW record ANYWHERE in the data begin with a number?
0
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.

 

Author Comment

by:jl66
ID: 40607879
Q: See the wrapped line that starts with a 6?  Is that possible in the real data?
A: Possible.

What not to change is the rule that a unique number (C1) appears after 3 delimiters |^, ^|^, and ^|. Assume that in the the text field below there is no | or ^. The text field can be multiple lines. For example,

101|^text1^|^string1^|
102|^text2^|^string2^|
...

Any 1st letter of a new line in "text field" can not be excluded a number.

Is that clear?
0
 

Author Comment

by:jl66
ID: 40607886
What I can do is that I can have the field delimiter revised if you think the possible way to load the data in after the change.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40607894
>>Is that clear?

I think so.

I'm thinking that  there isn't a 'pattern' in the data for CONTINUEIF to decide when the next row of data begins.

>>What I can do is that I can have the field delimiter revised

The delimiter really doesn't matter.  What matters is a defined 'pattern' in the data that can be used with CONTINUEIF.

CONTINUEIF needs a pattern to determine when the next row of data begins.

I'll try to get some time to experiment later today to see if it is smart enough to be able to look for a series of numbers followed by a delimiter.  Never tried that!

Between johnsone and myself hopefully we can come up with some magic.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40608047
Based on the documentation, I don't think you can do any type of pattern matching.  It is looking for a fixed number of characters to compare to a fixed number of characters for the CONTINUEIF operator.  The only allowed operator are = and != (or <>).  Based on that, I don't think you can use a pattern.

Would it be possible to get the source data with a fixed number of rows per record?  Even if there are extra blank lines after the last delimiter?  If so, I think that could work.  A quick test I did seems to show that it will lose the line breaks in this case, but it seems like it would be easy to load.

Also, what OS are you running the SQL*Loader session on?
0
 

Author Closing Comment

by:jl66
ID: 40608649
Thanks a lot.
0
 

Author Comment

by:jl66
ID: 40608659
Thanks a lot for your effort. The data was loaded, The key is to process the raw loading data by adding a special delimiter to the end of line with 'sed'.  The rest are from the docs.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40608661
Any reason for the "B" penalty grade?

What additional information did you need before providing an "A" grade?

Please review:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40608668
I retract my grade comment.  It was based on the first "Thanks" post.

>>The key is to process the raw loading data by adding a special delimiter to the end of line with 'sed'

Since you needed to do extra work that wasn't part of the solutions offered, the "B" is justified.

Sorry for the confusion.
0
 

Author Comment

by:jl66
ID: 40608689
Wrong "adding a special delimiter to the end of line"
Right "adding a special delimiter to the end of each record"
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40608704
That is why I was asking for OS.  I was going to look for a sed or awk way of putting something into one or more of the lines so we could determine where a record ended.  I wasn't going to suggest that because if you were in a Windows environment, it wouldn't have worked.

Glad you got it working.
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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

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

14 Experts available now in Live!

Get 1:1 Help Now