Oracle: Init parameter MAX_STRING_SIZE

The value of max_string_size specifies the maximum size of VARCHAR2, NVARCHAR2 and RAW types in SQL and table columns.

This value is either STANDARD (in which case the maximum size of varchar2 strings is 4000 bytes) or EXTENDED (which allows for 32767 bytes).

The value of max_string_size needs to be extended in order to use the collate clause when creating tables or PL/SQL objects (otherwise, the error ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set is thrown).

In order to set max_string_size to exended, the script $ORACLE_HOME/rdbms/admin/utl32k.sql needs to be run (using catcon.pl when doing it in a CDB an all PDBs in this CDB).

Once set to extended, the value cannot be reverted back to standard.

select value from v$parameter where name = 'max_string_size';
--
-- STANDARD
--
 
create table tq84_max_string_size(txt   varchar2(4001));
-- ORA-00910: specified length too long for its datatype
 
create table tq84_max_string_size(txt   varchar2(4000));
-- Table TQ84_MAX_STRING_SIZE created.

select value from v$parameter where name = 'max_string_size';
--
-- EXTENDED
--
create table tq84_max_string_size(txt   varchar2(32768));
-- ORA-00910: specified length too long for its datatype
xf 
create table tq84_max_string_size(txt   varchar2(32767));
-- Table TQ84_MAX_STRING_SIZE created.

declare
   txt varchar2(32768);
begin
   null;
end;
/
-- PLS-00215: String length constraints must be in range (1 .. 32767)
 
declare
   txt varchar2(32767);
begin
   null;
end;
/
-- PL/SQL procedure successfully completed.