Home » SQL & PL/SQL » Client Tools » Someone please explain this behavior with multi-byte characters (Oracle 11G, RHEL)
Someone please explain this behavior with multi-byte characters [message #648939] Tue, 08 March 2016 02:59 Go to next message
cool.aquarian
Messages: 3
Registered: March 2016
Junior Member
I am dealing with a strange behavior related to multibyte character data.


1. I have the following table:
CREATE TABLE MUTIBYTE_TEST
(
S_NO NUMBER(3),
PAYLOAD NVARCHAR2(10),
PAYLOAD2 NVARCHAR2(4000)
)

2. I try to insert below 10 character multibyte data using SQL Developer:

INSERT INTO MUTIBYTE_TEST VALUES(2,'无锡市职业病防治医院','');

It runs fine, and I can see the data.


3. I try to insert same 4000 multibyte characters into PAYLOAD2 as well (by repeating the 10 multibyte characters 400 times):

INSERT INTO MUTIBYTE_TEST VALUES(2,'无锡市职业病防治医院','无锡市职业病防治医院无锡市职业病防治医院无锡市职业病防治医院 ..... <repeated 400 times=4000 chars>');

Now I get the below error:

Error report -
SQL Error: ORA-01704: string literal too long
01704. 00000 - "string literal too long"
*Cause: The string literal is longer than 4000 characters.
*Action: Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.


Why is it not allowing me to insert the same multibyte characters after repeating 400 times ?

[Updated on: Tue, 08 March 2016 03:00]

Report message to a moderator

Re: Someone please explain this behavior with multi-byte characters [message #648941 is a reply to message #648939] Tue, 08 March 2016 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The maximum size of a (N)VARCHAR2 is 4000 BYTES, your string with 4000 CHARACTERS exceeds the limit.

Re: Someone please explain this behavior with multi-byte characters [message #648950 is a reply to message #648941] Tue, 08 March 2016 10:52 Go to previous messageGo to next message
cool.aquarian
Messages: 3
Registered: March 2016
Junior Member
Then why is NVARCHAR2(10) able to accommodate the 10 CHARACTERS '无锡市职业病防治医院' ??
Re: Someone please explain this behavior with multi-byte characters [message #648951 is a reply to message #648950] Tue, 08 March 2016 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because it is declared as 10 character length.
The other one is declared as 4000 character length but the internal limit is anyway 4000 bytes whatever you declare which means 4000 characters if you have single byte characters but less if you have multi-byte characters.

[Edit: NVARCHAR2 length does not depend on "nls_length_semantics", it is always defined as CHAR length]

[Updated on: Tue, 08 March 2016 11:47]

Report message to a moderator

Re: Someone please explain this behavior with multi-byte characters [message #648965 is a reply to message #648951] Tue, 08 March 2016 19:34 Go to previous messageGo to next message
cool.aquarian
Messages: 3
Registered: March 2016
Junior Member
thanks, that clears it.
Didn't know that oracle will implicitly set the internal limit to 4000 bytes even if I specify the length as 4000 NVARCHAR2.
Re: Someone please explain this behavior with multi-byte characters [message #648970 is a reply to message #648965] Wed, 09 March 2016 00:38 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle Built-in Data Types

Previous Topic: UTL_FILE
Next Topic: sqlplus output formatting
Goto Forum:
  


Current Time: Sun Oct 21 05:41:22 CDT 2018