DBMS_MAX_STRING_SIZE Package

The DBMS_MAX_STRING_SIZE package provides an interface for checking and changing the value of the DBMS_MAX_STRING_SIZE initialization parameter.

• CHECK_MAX_STRING_SIZE Function
  This function checks if the MAX_STRING_SIZE parameter can be updated to a given value and returns a list of violations that would prevent the parameter from being updated.
• MODIFY_MAX_STRING_SIZE Procedure
  This procedure updates the value of the MAX_STRING_SIZE parameter to a given value.

CHECK_MAX_STRING_SIZE Function

This function checks if the MAX_STRING_SIZE parameter can be updated to a given value and returns a list of violations that would prevent the parameter from being updated.

Syntax

DBMS_MAX_STRING_SIZE.CHECK_MAX_STRING_SIZE(
       new_value    IN VARCHAR2)
RETURN DBMS_MAX_STRING_SIZE_TBL;

Parameters

Parameter	Description
new_value	Specifies the new MAX_STRING_SIZE parameter value to be set. The only valid value is:'STANDARD' .

Usage Notes

If the return list is empty, then there are no violations and the MAX_STRING_SIZE update can be performed.

Example

SELECT * FROM TABLE(DBMS_MAX_STRING_SIZE.CHECK_MAX_STRING_SIZE('STANDARD'));
 
TYPE   OBJECT_OWNER OBJECT_NAME COLUMN_NAME    REASON                                       
------ ------------ ----------- -------------- ---------------------------------------------
COLUMN ADMIN        SALES       CUST_NOTES     Physical column exceeds STANDARD length limit   
1 rows selected.   

MODIFY_MAX_STRING_SIZE Procedure

This procedure updates the value of the MAX_STRING_SIZE parameter to a given value.

Syntax

DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE(
          new_value    IN VARCHAR2);

Where: user_account is the user account name (schema name).

Parameters

Parameter	Description
new_value	Specifies the new MAX_STRING_SIZE parameter value to be set. The only valid value is: 'STANDARD'.

Usage Notes

• Using DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE is a one-way change that cannot be reverted. After a database is switched back to the STANDARD style of supporting a maximum length of 4000 bytes for the VARCHAR2, NVARCHAR2, and RAW data types, you cannot re-enable EXTENDED data types.

• The ADMIN user is granted EXECUTE privilege WITH GRANT OPTION clause on DBMS_MAX_STRING_SIZE. Oracle recommends that you do not GRANT EXECUTE on this package to other users.

• The error ORA-20000 is raised if any object exists that would prevent MAX_STRING_SIZE from being updated.

• The ADMIN user is granted EXECUTE privilege WITH GRANT OPTION clause on DBMS_MAX_STRING_SIZE. Oracle recommends that you do not GRANT EXECUTE on this package to other users.

Example

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'max_string_size';

NAME                 VALUE
max_string_size      EXTENDED


   BEGIN
     DBMS_MAX_STRING_SIZE.MODIFY_MAX_STRING_SIZE('STANDARD');
   END;
/ 


PL/SQL procedure successfully completed.

SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'max_string_size';

NAME                 VALUE
max_string_size      STANDARD