How To Enable Oracle’s EXTENDED Option for VARCHAR2 Columns

Oracle 12c added support for extending the allowed length of VARCHAR2 columns from 4000 to 32,767 bytes but  made the discovery of whether this feature is enabled require privileges that a normal Oracle user is not granted. This article explains how to tell CopyStorm that the Oracle feature “max_string_size=EXTENDED” is enabled without requiring special Oracle permissions.

For your reference, if an Oracle user has enough privileges then a simple query can be used to determine if the EXTENDED Varchar2 feature is enabled.

  • SELECT name,value FROM V$PARAMETER WHERE name=’max_string_size’

It is likely that your account will return a “Table or View not Found” when running this query because of your limited permissions.

To inform CopyStorm that the EXTENDED feature is enabled simply create a file named Oracle.xml and drop into the your private CopyStorm configuration directory specified on the Advanced tab or into the directory [CopyStormRootDir]/config.  The content of the Oracle.xml file should be:

<Oracle>


      <DatabaseProperty name="max_string_size" value="EXTENDED" />


</Oracle>
 

Restart CopyStorm  and future  VARCHAR2 columns will be up to 32,764 bytes in length. You may wonder why the limit is 32,764 rather than 32,767. The reason is that Salesforce defaults to a UTF-8 Extended character set and each character can take up to 4 bytes.