| SQLLDR Insert problem [message #333424] |
Fri, 11 July 2008 11:24  |
jyn780
Messages: 5
Registered: July 2008
|
Junior Member |
|
|
Hi,
I have a problem when inserting data using SQLLDR.
The SQLLDR fails saying that the length of the data exceeds the length of the column in the table.
But when I try to insert the same row manually it gets inserted.
And also the max length defined in the table is 500 and the length of the data is 303 chars.
Can anyone help me with this?
Thanks.
|
|
|
|
|
|
|
|
| Re: SQLLDR Insert problem [message #333434 is a reply to message #333429] |
Fri, 11 July 2008 11:37  |
jyn780
Messages: 5
Registered: July 2008
|
Junior Member |
|
|
Sorry for not following the guidelines.
The ctl file is:
PTIONS ( DIRECT=TRUE, MULTITHREADING=TRUE )
UNRECOVERABLE
LOAD DATA
TRUNCATE
INTO TABLE prod_delta
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ean,
title,
price,
format_code,
format_desc,
publisher_name,
available_on,
page_count,
subject_code,
subject_desc,
category_code,
category_desc,
edition_name,
created_at SYSDATE,
updated_at SYSDATE
)
And the rejected data is:
9780063854932|Toward an Understanding of Metropolitan America: Report of the Social Science Panel on the Significance of Community in the Metropolitan Environment of the Advisory Committee to the Dept. of Housing and Urban Development, Assembly of Behavioral and Social Sciences, National Research Council||TC|Trade Cloth||01/01/1974|193|||||
The record is rejected on the title column. The error is:
Record 8673511: Rejected - Error on table PROD_DELTA, column TITLE.
Field in data file exceeds maximum length.
The max length of the title column in the db is 500 chars.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQLLDR Insert problem [message #333445 is a reply to message #333442] |
Fri, 11 July 2008 12:15 |
 |
Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
It sounds like there is a probably a newline character in your data. You can remove any that exist from the end using rtrim. The following assumes that chr(10) is your newline character. It may be different on your system.
edition_name "rtrim (:edition_name, chr(10))"
If the newline character is in the middle somewhere you can use replace:
edition_name "replace (:edition_name, chr(10), '')"
[Updated on: Fri, 11 July 2008 12:21] Report message to a moderator |
|
|
|
| Re: SQLLDR Insert problem [message #333452 is a reply to message #333424] |
Fri, 11 July 2008 12:38 |
jyn780
Messages: 5
Registered: July 2008
|
Junior Member |
|
|
The newline in my file actually indicates a new record. The edition_name column is the last column in the data that I receive.
9780063854932|Toward an Understanding of Metropolitan America: Report of the Social Science Panel on the Significance of Community in the Metropolitan Environment of the Advisory Committee to the Dept. of Housing and Urban Development, Assembly of Behavioral and Social Sciences, National Research Council||TC|Trade Cloth||01/01/1974|193|||||
9780080238326|Geomathematical and Petrophysical Studies in Sedimentology, an International Symposium: Proceedings of Papers Presented at Sessions Sponsored by the International Association for Mathematical Geology at the Tenth International Congress on Sedimentology in Jerusalem, July 1979|133.00|TC|Trade Cloth|Elsevier Science & Technology Books|01/01/1979|285|060|Science/Tech|060C|Science|1st ed
for the first record the edition_name is empty. But it still inserts a new line character in the edition_name column.
For the second one a newline char is inserted after '1st ed'
I modified the ctl file to
PTIONS ( DIRECT=TRUE, MULTITHREADING=TRUE )
UNRECOVERABLE
LOAD DATA
TRUNCATE
INTO TABLE products_delta
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ean,
title char(500),
price,
format_code,
format_desc,
publisher_name,
available_on date 'mm-dd-yy',
page_count,
subject_code,
subject_desc,
category_code,
category_desc,
edition_name "rtrim (:edition_name, chr(10))",
created_at SYSDATE,
updated_at SYSDATE
)
I am on a unix machine.
[Updated on: Fri, 11 July 2008 12:38] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQLLDR Insert problem [message #333466 is a reply to message #333424] |
Fri, 11 July 2008 14:58 |
jyn780
Messages: 5
Registered: July 2008
|
Junior Member |
|
|
Hi Barbara,
I tried chr(13) and it worked. I checked the ascii code for newline on Unix and it was 10 so I used 10, but now with 13 it worked fine.
Thanks a lot for your help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.