Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql loader : record delimiter

Posted on 2003-03-21
7
Medium Priority
?
5,165 Views
Last Modified: 2008-01-09
Is it possible to change the predefined (newline) record (row) delimiter for sqlldr ?

(Records in my *.txt file are delimited by '[]'.)
0
Comment
Question by:IzabellaK
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:bkowalski
ID: 8182035
Yes, use the "terminated by" clause, for example:

load data
truncate
into table <tablename>
fields terminated by '[]'
0
 
LVL 1

Author Comment

by:IzabellaK
ID: 8182307
As I mentioned in my question,
I have to delimit not fields but records (a set of fields).
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8182980
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:IzabellaK
ID: 8183535
It's a good solution for constant file name;
but how should I specify it in case of :

sqlldr data=... ?
0
 
LVL 1

Author Comment

by:IzabellaK
ID: 8183662
It's a good solution for constant file name;
but how should I specify it in case of :

sqlldr data=... ?
0
 
LVL 11

Accepted Solution

by:
pennnn earned 1500 total points
ID: 8183712
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
 
LVL 1

Author Comment

by:IzabellaK
ID: 8203638
thank you.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.
Suggested Courses

581 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