Oracle Cloud Infrastructure Documentation

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.

Parent topic: Autonomous Database Supplied Package Reference

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