Home » RDBMS Server » Server Utilities » SQLLDR Insert problem
SQLLDR Insert problem [message #333424] Fri, 11 July 2008 11:24 Go to next message
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 #333427 is a reply to message #333424] Fri, 11 July 2008 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

A content free post, results in equally worthless response.
Re: SQLLDR Insert problem [message #333429 is a reply to message #333424] Fri, 11 July 2008 11:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post the table definition,your control file, sample data and exact error output.
Explaining what you did will help us very little.
Re: SQLLDR Insert problem [message #333434 is a reply to message #333429] Fri, 11 July 2008 11:37 Go to previous messageGo to next message
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 #333435 is a reply to message #333424] Fri, 11 July 2008 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition give us your database character set.

Regards
Michel
Re: SQLLDR Insert problem [message #333436 is a reply to message #333434] Fri, 11 July 2008 11:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If you do not specify a data length in your SQL*Loader control file, the default is 255. So use:

title char (500)
Re: SQLLDR Insert problem [message #333438 is a reply to message #333436] Fri, 11 July 2008 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Barbara,
You are quite the expert on SQLLDR!
I keep learning new factoids from the knowledgeable experts here.

Barbara Boehmer wrote on Fri, 11 July 2008 09:39
If you do not specify a data length in your SQL*Loader control file, the default is 255. So use:

title char (500)

Re: SQLLDR Insert problem [message #333442 is a reply to message #333436] Fri, 11 July 2008 11:55 Go to previous messageGo to next message
jyn780
Messages: 5
Registered: July 2008
Junior Member
Thanks Barbara that did solve the problem Smile .
I have another problem, a new line character is always inserted in the last column i.e the edition_name. I could not figure out why.

[Updated on: Fri, 11 July 2008 11:56]

Report message to a moderator

Re: SQLLDR Insert problem [message #333445 is a reply to message #333442] Fri, 11 July 2008 12:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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 Go to previous messageGo to next message
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 #333453 is a reply to message #333452] Fri, 11 July 2008 12:40 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
jyn780 wrote on Fri, 11 July 2008 13:38

available_on date 'mm-dd-yy',



This does not match your data.
Re: SQLLDR Insert problem [message #333463 is a reply to message #333452] Fri, 11 July 2008 13:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I am using Windows and unable to reproduce your problem. You need to determine what your newline character is by copying and pasting into an ascii function or dumping it or some such thing. It is probably chr(10) || chr(13) or some such thing. You can try that instead of just chr (10). See if you can post something like below.

SCOTT@orcl_11g> column edition_name format a12
SCOTT@orcl_11g> column dump	    format a45
SCOTT@orcl_11g> select edition_name,
  2  	    dump (edition_name) as dump
  3  from   products_delta
  4  /

EDITION_NAME DUMP
------------ ---------------------------------------------
             NULL
1st ed       Typ=1 Len=6: 49,115,116,32,101,100

2 rows selected.

SCOTT@orcl_11g> 

Re: SQLLDR Insert problem [message #333466 is a reply to message #333424] Fri, 11 July 2008 14:58 Go to previous message
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.
Previous Topic: How to overwrite Functions/ Procedures using expdp
Next Topic: SQLLDR using input file in different server
Goto Forum:
  


Current Time: Wed Dec 07 05:08:21 CST 2016

Total time taken to generate the page: 0.07914 seconds