sqlldr - record terminator as '¦' and not 'linefeed' or 'new line' 1

[naushi]

I have a text file with data in some columns to be up to 8000 characters and most dtaa has CR (carriage return) in them. SO I decided to make my record terminator to be '|' a pipe.

Question is how do I specifiy in my control file that my record terminator is a pipe. The default is CR which I want to avoid. Also, will it treat the CR in the data as part of the column? Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[jimbopalmer]

Load Data into table Mytable
fields terminated by "|"
(first_column,
and so on...
I tried to remain child-like, all I acheived was childish.

 

[ThomVF]

Jimbo is right.

Further explanation of what is happening:

If your data is included inside the control file (aka "Stream Record Format", using "INFILE *" syntax), you optionally specify the "record" terminator, in Hex. (default is NewLine).

If your data is outside the control file, then you are specifying "field" terminators or fixed/variable record-size (no terminator), not record terminators. You can specify a global field terminator, or a field-by-field terminator.

"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[rastorin]

HI !!!

Try this commands in control file:

LOAD DATA
INFILE 'yuor_directory_file_reload'
APPEND
INTO TABLE yuor_table_name
FIELDS TERMINATED BY '|'
( lg_refnum,
....

... )

 

[naushi]

I guess there is miscommunication. Pipe is my record terminator, not my field terminator. Because there are carriage returns in the text fields that are coming from SQL Server I have to make my record terminator to be "|" followed by a CR and I am using the stream parameter and it works. But some how there are black boxes coming through the data and Oracle loader takes them as CR. Also when I look into my CLOB fileds it shows only 20 characters. I dont know if those text fields got loaded in their ehtirety or I dont know how to look at CLOB data. Regards,
Naushi Hussain
Naushi.hussain@honeywell.com