Fix for Maximum length allowed for char in Oracle 4000 character

Viewed 7677

I am currently using Oracle 11g. As per my requirement, I need to store first 4000 (max limit) characters to my variable (used in triggers).

For that I am using VarName = SUBSTR function(VarName, 1, 4000), but it seems like it is storing nothing (I am passing '111...'(4096 characters to store) out of which I want to store ('111...') first 4000 characters only).

It worked for 4 characters but not working for maximum limits. I tried with length as 3999, 3000 but nothing worked for me.

Kindly look into this and suggest me some solution for this.

(*Note: I can't change the variable type from varchar to clob as this columns is having already millions of legacy data.)

*Modification: I tried with Substr length 2048, it is working but once I am providing 2049 characters to substr, it is again becoming null. But in data type field value is defined as VARCHAR2(4000 CHAR).

2 Answers

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:

  1. in your charset there are characters that occupy only one byte
  2. 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;

RPAD or LPAD:

I could obtain a 4000 chars cell like this:

select rpad('x',3999, '-'),  v.* 
  from v$version v 
 where rownum=1

Gives

|x------[3999 times] | Oracle Database 11g Release 11.2.0.4.0 - 64bit dev|
Related