sql loader : record delimiter

Is it possible to change the predefined (newline) record (row) delimiter for sqlldr ?

(Records in my *.txt file are delimited by '[]'.)
LVL 1
IzabellaKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bkowalskiCommented:
Yes, use the "terminated by" clause, for example:

load data
truncate
into table <tablename>
fields terminated by '[]'
0
IzabellaKAuthor Commented:
As I mentioned in my question,
I have to delimit not fields but records (a set of fields).
0
pennnnCommented:
INFILE filename.dat "STR '^'"
The above will change the record delimiter to the carret character "^". You can specify any string there - it can be a whole string, not only character.
You can also specify it in hexadecimal format to avoid character set problems:
INFILE filename.dat "STR x'1234'"
The default record delimiter is '\n'.

Hope that helps!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

IzabellaKAuthor Commented:
It's a good solution for constant file name;
but how should I specify it in case of :

sqlldr data=... ?
0
IzabellaKAuthor Commented:
It's a good solution for constant file name;
but how should I specify it in case of :

sqlldr data=... ?
0
pennnnCommented:
That's a very good question... :)
I don't think you can specify that option in the command line. On the other hand the record delimiter is only valid for the INFILE specified before it in the control file...
Maybe you can still speciify it in the control file. Here's what the manual says about it:
"If you specify a datafile on the command line and also specify datafiles in the control file with INFILE, the data specified on the command line is processed first. The first datafile specified in the control file is ignored. All other datafiles specified in the control file are processed."
So the question is whether the first data file in the control file is ignored but its options (record delimiter) are still valid (i.e. using the file specified in the command line, but with the delimiter from the control file).
That's something you can try and see if it wokrs.
If it doesn't work I think the only option would be to specify the infile in an environment variable and then refer to it in the control file. Something like this (unix):
<control file>:
INFILE $datafile "STR 'x'"

<command line>
$ EXPORT datafile="myfile.dat"
$ sqlldr usr/pwd ....

Hope that helps!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IzabellaKAuthor Commented:
thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.