If I am right, you'd better upgrade to oracle 12. Let me explain:
Oracle (up to version 11 included) has a HARD limit of 4000 BYTES for char/varchar columns and this limit can't be overcomed by simply declaring a column as "varchar2(4000 CHAR)".
if your database uses a multi-byte character set, that limit is still in place, so you will be able to actually store 4000 characters only if these two conditions are met:
- in your charset there are characters that occupy only one byte
the string you want to store is made up only using the characters that are encoded to a single byte
For example: if your database character set is UTF8, the "A" character occupies one byte, but the "à" character needs two bytes:
| Bytes required |Bytes required | Bytes required
string | if database charset |if database charset | if database charset
| is ASCII |is UTF8 | is UTF16
--------- |-------------------------------------------------------------------
"A" | 1 byte | 1 bytes | 2 bytes
"à" | 1 byte | 2 bytes | 2 bytes
"àA" | 2 byte | 3 bytes | 4 bytes
so in a VARCHAR2(4000 CHAR) column, in a UTF8 database, you will be able to store a string made of 4000 "A" characters, but if you use 4000 "à" character it will be limited to 2000 characters: in UTF8 any character whose code unicode number >= 128 (ie: it isn't an ASCII character) takes multiple bytes. in UTF16 any charcter eats at least 2 bytes.
By looking at the symptoms you are describing (and at your name) I think you aren't using a single byte character set... and I also think that most of the characters you are using require at least two bytes, so here is the reason of the 2000 characters actual limit: you are facing the 4000 bytes hard limit.
I think that the only "easy" solution for you is to upgrade the database to oracle 12, where this hard limit can be raised to 32000 byte (AFAIK it doesn't do it "out of the box": you need to set it up accordingly, during database creation).
if you create a oracle 12 database with the raised limit, your varchar2(4000 CHAR) columns won't meet the hard limit any more.
Keep also in mind that when you normally declare a column as VARCHAR2(4000), oracle by default interprets it as VARCHAR2(4000 BYTE). this default can be changed by executing within your session this:
alter session set NLS_LENGTH_SEMANTICS='CHAR'
it TEORICALLY can be set at instance level for the whole database and all the session, but oracle warns you NOT to do it because the system would misbehave, so stick to using it at session level.
Hope this helps.
P.S. Maybe you could find useful also this hint:
In my database I wrote a "after logon" trigger that executes automatically that alter session only for the user(s) of my application
CREATE OR REPLACE TRIGGER TRG_MYAPP_AFTER_LOGON
AFTER LOGON on database
declare
function IsMyAppUser return boolean is
cnt number;
begin
-- if the user is the owner of the schema, all its commands are expected
-- to use the "char" length semantics
if user = 'MYAPP' then
return true;
end if;
--- this is specific to my app: I have a table that lists the login names
--- of the users who have been created only with the purpose of using the app
select count(*) into cnt
from my_app_users u
where u.usr_login=user
and ROWNUM=1;
return cnt>0;
end;
BEGIN
if IsMyAppUser then
execute immediate 'alter session set nls_length_semantics = ''CHAR''';
end if;
END TRG_QBF_AFTER_LOGON;