Solved

How to load multiple lines in one record?

Posted on 2015-02-12
20
193 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
[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
  • 9
  • 7
  • 4
20 Comments
 
LVL 77

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 35

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 77

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 77

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 35

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 77

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
 

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 77

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 35

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 77

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 77

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 35

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

623 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