Home » RDBMS Server » Server Utilities » SQL Loader + position
SQL Loader + position [message #245941] Tue, 19 June 2007 06:53 Go to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hi, I have now only one problem left which I do not understand:

the table in which I will upload contains the following columns:
create table test
(
...
REMARKS1 VARCHAR2(70) NULL,
REMARKS2 VARCHAR2(70) NULL,
REMARKS3 VARCHAR2(70) NULL,
REMARKS4 VARCHAR2(70) NULL,
REMARKS5 VARCHAR2(70) NULL,
REMARKS6 VARCHAR2(70) NULL,
REMARKS7 VARCHAR2(70) NULL,
REMARKS8 VARCHAR2(70) NULL,
REMARKS9 VARCHAR2(70) NULL,
REMARKS10 VARCHAR2(70) NULL,
...
)

the sqlloader ctlfile looks like this

...
Remarks1 position(82:151) char,
Remarks2 position(152:221) char,
Remarks3 position(222:291) char,
Remarks4 position(292:361) char,
Remarks5 position(362:431) char,
Remarks6 position(432:501) char,
Remarks7 position(502:571) char,
Remarks8 position(572:641) char,
Remarks9 position(642:711) char,
Remarks10 position(712:781) char,
...

But 10 lines of my file were not uploaded. The logfile says

Record 2201: Rejected - Error on table test, column REMARKS2.
ORA-12899: value too large for column "test"."REMARKS2" (actual: 71, maximum: 70)

This is what I do not understand. The ctl file reads for all the remarks columns 70 characters. The table also allows maximum 70 characters, so why does the logfile say that the system tried to load 71 chars in such a column.

Can somebody please help me with this?

[mod-edit] colors removed.

[Updated on: Tue, 19 June 2007 08:04] by Moderator

Report message to a moderator

Re: SQL Loader + position [message #245956 is a reply to message #245941] Tue, 19 June 2007 07:19 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it, perhaps, because there are also multi-byte characters and your file contains some of them?
Re: SQL Loader + position [message #245960 is a reply to message #245941] Tue, 19 June 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to LittleFoot answer: post your database characterset to be sure it is a multi-byte one (as %UTF8, AL16UTF16...).

Regards
Michel
Re: SQL Loader + position [message #245999 is a reply to message #245941] Tue, 19 June 2007 09:32 Go to previous messageGo to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hi,

Can one of you please give me some examples of multi-byte characters?

This is the Characterset I am using: AL32UTF8

I thought that the position(n:m) will read the characters between n and m. And also varchar2(70) will allow 70 characters. I never thought that one character could be counted as 2.

Thanks for your help!
Re: SQL Loader + position [message #246025 is a reply to message #245999] Tue, 19 June 2007 10:45 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"varchar2(70)" by default means 70 BYTES unless your NLS_LENGTH_SEMANTICS parameter was set to CHAR (default is also BYTE).

Regards
Michel
Previous Topic: EXP-00003: no storage definition found for segment(25, 19)
Next Topic: Re: How to do a full export/import
Goto Forum:
  


Current Time: Sun Dec 04 10:56:45 CST 2016

Total time taken to generate the page: 0.21383 seconds