The answer accepted for the question is correct and the documentation has a lot of information, but I will give a practical example because I have suffered enough with this oracle.
Let’s start by checking, with the SQL below, the value of NLS_CHARACTERSET, which is the "cause" of the problem:
SELECT *
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';
If you are using UTF8, as already reported, a single character can occupy 3 bytes. Then, in a database configured this way, if you run the following SQL:
CREATE TABLE NLS_TESTS (
COMCHAR VARCHAR2(3 CHAR),
SEMCHAR VARCHAR2(3)
);
INSERT INTO NLS_TESTS VALUES('aei', 'aei'); --Será inserido
INSERT INTO NLS_TESTS VALUES('áéí', 'áéí'); --Não será inserido!!
INSERT INTO NLS_TESTS VALUES('áéí', 'aei'); --Será inserido!!
The second insert will not be inserted as the SEMCHAR VARCHAR2(3) uses the NLS_LENGTH_SEMANTICS current to determine the size of the text. That is, to UTF8 if the character occupies 2 bytes, then these 2 bytes will actually be occupied in the column.
Already the third insert will be executed without error as the column COMCHAR VARCHAR2(3 CHAR) is using 'CHAR', which basically indicates that the column should have enough space for 3 characters, regardless of how many bytes this takes.
I’ve been through this problem where changing column to column for VARCHAR2(x CHAR) was not feasible, one way around this would be by using the following code in your current transaction, to change the NLS_CHARACTERSET session:
declare
v_s_CharacterSet VARCHAR2(160);
begin
select VALUE
into v_s_CharacterSet
from NLS_DATABASE_PARAMETERS
where PARAMETER = 'NLS_CHARACTERSET';
if v_s_CharacterSet in ('UTF8', 'AL32UTF8') then
execute immediate 'alter session set nls_length_semantics=char';
end if;
end;
This also explains why when running from another computer it worked. The session that was opened where it worked, did not use UTF8 or AL32UTF8.
You’re probably wearing a encoding different. http://answall.com/q/21230/101 Or if it’s a encoding that each character occupies a different size may be the different text that made it fit.
– Maniero
How it is inserted into the database, through an application such as PL/SQL or SQL?
– jefissu
I tried to insert by SQL Developer and also through the application I am developing. The machine in which the problem occurred has Windows. The rest are variants of Linux or Mac. What we’re finding is that the problem is Windows encoding, but we’re not sure yet.
– ewerton
Depending on the client you are using you can specify the encoding. This encoding setting can be done in client software or even in the connection string of your app. Do you need to work with VARCHAR2? I ask this because depending on the amount of characters it would be interesting to change to a different type of data.
– Fábio Jânio