Does Oracle Insert Into NVarchar2 (4000) not allow 4000 characters?

Question

I have a table with the field data type NVarchar2 (4000). I am moving data from SQL Server to Oracle. The SQL Server data type is also nvarchar (4000). I checked the MAX size of this field on the SQL Server side, and MAX - 3996, which is 4 characters less than the 4000 limit.

When I try to paste this data into Oracle, I get a "LONG" error message due to size.

What happens here, will Oracle NVarchar2 (4000) not allow 4000 characters? If not, what is the limit, or how can I get around this?

Answer 1

There is a limit of 4000 bytes, not 4000 characters. Thus, an NVARCHAR2 (4000) with a national character set AL16UTF16 would occupy a maximum of 4000 bytes.

From oracle documents MAX_STRING SIZE :

Virtual column tables will be updated with the new metadata data type for virtual columns VARCHAR2 (4000), 4000-byte NVARCHAR2, or RAW (2000).

Decision: -

Also, if you want to save 4000 characters, I would recommend that you use CLOB

CLOB (Large Large Object) is an Oracle data type that can hold up to 4 GB of data. CLOB files are convenient for storing text.

You can try how to change the column data type in the CLOB:

ALTER TABLE table_name ADD (tmpcolumn CLOB); UPDATE table_name SET tmpcolumn =currentnvarcharcolumn; COMMIT; ALTER TABLE table_name DROP COLUMN currentnvarcharcolumn; ALTER TABLE table_name RENAME COLUMN tmpcolumn TO whatevernameyouwant; 

Answer 2

First, as others have pointed out, if you are not using 12.1, the varchar2 and nvarchar2 data types are limited to 4000 bytes in SQL. In PL / SQL, they are limited to 32767. In 12.1, you can increase the SQL limit to 32767 using the MAX_STRING_SIZE parameter.

Secondly, if you are not working with an obsolete database that uses a non-Unicode character set that cannot be updated to use the Unicode character set, you would like to avoid the nvarchar2 and nchar data types in Oracle. In SQL Server, you use nvarchar when you want to save Unicode data. In Oracle, it is preferable to use varchar2 in a database whose character set supports Unicode (usually AL32UTF8 ) when you want to store Unicode data.

If you store Unicode data in an Oracle nvarchar2 column, the national character set will be used - this is almost certainly AL16UTF16 , which means that at least 2 bytes of memory are required for each character. A NVARCHAR2(4000) , therefore, probably cannot store more than 2000 characters. If you use the varchar2 column, on the other hand, you can use the variable width Unicode character set ( AL32UTF8 ), in which case English characters usually only need 1 byte, most European characters require 2 bytes, and most Asian characters require 3 bytes ( this, of course, is just a generalization). This will usually allow you to store significantly more data in the varchar2 column.

If you need to store more than 4000 bytes of data, and you are using Oracle 11.2 or later, you will have to use the LOB data type ( CLOB or NCLOB ).

Answer 3

According to the documentation , although the width refers to the number of characters, the number of which is still limited to 4000 bytes:

The character width specifications for the NVARCHAR2 data type are character numbers. The maximum column size is 4000 bytes.

You probably have 4 multibyte characters.