?
Solved

sql loader : record delimiter

Posted on 2003-03-21
7
Medium Priority
?
4,730 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
[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
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 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