Oracle | DBMS_MAX_STRING

Oracle DBMS_MAX_STRING_SIZE Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Assumed to allow dynamically resetting the database maximum string size: To be validated.

AUTHID DEFINER

Data Types

These types are independent objects owned by SYS: They are not defined inside the package (see Dependencies, below). /* Record type returned by CHECK_MAX_STRING_SIZE API */ CREATE OR REPLACE TYPE dbms_max_string_size_rec force as object( type VARCHAR2(128), object_owner dbms_id, object_name dbms_id, column_name dbms_id, reason VARCHAR2(4000)) NOT PERSISTABLE; / /* Table type returned by CHECK_MAX_STRING_SIZE API */ CREATE OR REPLACE TYPE dbms_max_string_size_tbl FORCE AS TABLE OF (dbms_max_string_size_rec) NOT PERSISTABLE; /

Dependencies

DBA_REGISTRY	DBMS_MAX_STRING_SIZE_TBL	PLITBLM
DBA_TAB_COLS	DBMS_SCHEDULER	PROPS$
DBA_VIEWS	DBMS_SQL	V$PARAMETER
DBMS_ASSERT	DBMS_STANDARD	V$PDBS
DBMS_MAX_STRING_SIZE_REC

Documented No

Exceptions

Error Code	Reason
ORA-2000	An object exists that will prevent the string size modification

First Available 20c

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role.

Source {ORACLE_HOME}/rdbms/admin/dbmsmss.sql
{ORACLE_HOME}/rdbms/admin/prvtmss.plb

Subprograms

CHE CK_MAX_STRING_SIZE

MODIFY_MAX_STRING_SIZE

MODIFY_MAX_STRING_SIZE_POST

CHECK_MAX_STRING_SIZE

Determines if the MAX_STRING_SIZE can be changed to the "new" value

Only supported value is "STANDARD" and dbms_max_string_size.check_max_string_size(newValue IN VARCHAR2) RETURN dbms_max_string_size_tbl; PRAGMA SUPPLEMENTAL_LOG_DATA(check_max_string_size, READ_ONLY);

SELECT * FROM TABLE (dbms_max_string_size.check_max_string_size('STANDARD');

      

      no rows selected

MODIFY_MAX_STRING_SIZE

Updates MAX_STRING_SIZE to the "new" value

23c Beta 1 bug not fixed since 20c
BugDB bug opened at Feb 1, 2023.
Bug still not fixed in 23.2. But updated Aug 11, 2023 dbms_max_string_size.modify_max_string_size(newValue IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(modify_max_string_size, UNSUPPORTED);

exec dbms_max_string_size.modify_max_string_size('STANDARD');

      BEGIN dbms_max_string_size.modify_max_string_size('STANDARD'); END;

      *

      ERROR at line 1:

      ORA-01422: exact fetch returns more than requested number of rows

      ORA-06512: at "SYS.DBMS_MAX_STRING_SIZE", line 9

      ORA-06512: at "SYS.DBMS_MAX_STRING_SIZE", line 284

      ORA-06512: at line 1

MODIFY_MAX_STRING_SIZE_POST

Post-processing set for modify_max_string_size dbms_max_string_size.modify_max_string_size_post(newValue IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(modify_max_string_size_post, UNSUPPORTED);

exec dbms_max_string_size.modify_max_string_size_post('STANDARD');

      

      PL/SQL procedure successfully completed.