| Field in data file exceeds maximum length [message #70693] |
Mon, 15 July 2002 07:25  |
steph
Messages: 15
Registered: July 2002
|
Junior Member |
|
|
Hi,
I'm trying to load data that's longer than the 4000 character limit for varchar2(4000). I don't mind losing the extra characters so I'm using the SUBSTR
command on the field "ACTION_TEXT" but ignore's it
and refuses to load my records where that field is
longer than 4000 characters.... what am I doing wrong?
load data
infile 'TPR_CMP_TIME_SHEET.DAT' "str '<er>'"
into table TPR_CMP_TIME_SHEET
REPLACE
fields terminated by '<ec>'
TRAILING NULLCOLS
(REGIONAL_OFFICE_NUMBER ,
REFERENCE_ID ,
SEQUENCE_NO ,
DATE_F ,
AUTHOR_NAME ,
ACTION_TYPE_CODE ,
ACTION_TEXT CHAR(4000) "SUBSTR(:ACTION_TEXT, 1, 4000)" ,
TIME_F ,
BF_DATE ,
WP_ATTACH_IND ,
WP_ATTACHEMENT ,
MOD_DATE ,
MOD_USER )
Thanks,
Steph
|
|
|
|
|
|
| Re: Field in data file exceeds maximum length [message #70697 is a reply to message #70693] |
Mon, 15 July 2002 12:37  |
steph
Messages: 15
Registered: July 2002
|
Junior Member |
|
|
I assume that by the 'STREAM' option you imply fields
of variable lenght delimited by a special character,
(called [["str terminator_string"]] in the documentation)
either a ASCII or the equivalent in Hexadecimal format
My flat file is delimited as follows:
-records are delimited by '3C65723E' and
-fields are delimited by '3C65633E' in hexadecimal format
ex:
load data
infile 'TPR_CMP_TIME_SHEET.DAT' "str X'3C65723E'"
into table TPR_CMP_TIME_SHEET
fields terminated by X'3C65633E'
I've tried both the hexadecimal and the equivalent ASCII delimiters in my control file with the same
result.
I don't have any problems with the delimiters if the
field lenght is less than 4000 characters, so I question why this would be an issue.
I'm using SQL*Loader: Release 9.0.1.1.1 on WinNT
and SQL*Loader: Release 9.0.1.0.0 on SunOS 5.7
(I've tried both platforms with the same result)
Steph
|
|
|
|
|
|
| Re: Field in data file exceeds maximum length [message #70706 is a reply to message #70697] |
Tue, 16 July 2002 05:01 |
steph
Messages: 15
Registered: July 2002
|
Junior Member |
|
|
That doesn't work.... it rejects every record that contains an "ACTION_TEXT"
field that is longer than 255 characters... instead
of every every record that contains an "ACTION_TEXT"
field that is longer than 4000 characters.
I don't mind losing the tail end of the field if Oracle
will accept my record.
It really seems like it's the 'SUBSTR' function that's
not working like I expect it too.
Any other ideas?
Steph
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Field in data file exceeds maximum length [message #71925 is a reply to message #70693] |
Wed, 05 February 2003 18:14 |
Shan
Messages: 14
Registered: December 1999
|
Junior Member |
|
|
This is due to restriction in sql*ldr default column length of 255 (Note this is as per version 8.1.5)
LOAD DATA
INFILE 'c:footest.txt'
INSERT
INTO TABLE testing
FIELDS TERMINATED BY ','
(col1,col2,col3,col4 char(4000),col5)
Hope this helps
shan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
