SQL Loader + position [message #245941] |
Tue, 19 June 2007 06:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #245999 is a reply to message #245941] |
Tue, 19 June 2007 09:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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
|
|
|