Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to load multiple lines in one record?

Posted on 2015-02-12
20
Medium Priority
?
196 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 750 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 750 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

704 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