The DBMS_UTILITY package supports the following utility programs:

Function/Procedure Category Return type Description
ANALYZE_DATABASE(method [, estimate_rows [, estimate_percent [, method_opt ]]]) Procedure N/A Analyzes database tables.
ANALYZE_PART_OBJECT(schema, object_name [, object_type [, command_type [, command_opt [, sample_clause ]]]]) Procedure N/A Analyzes a partitioned table.
ANALYZE_SCHEMA(schema, method [, estimate_rows [, estimate_percent [, method_opt ]]]) Procedure N/A Analyzes schema tables.
CANONICALIZE(name, canon_name OUT, canon_len) Procedure N/A Canonicalizes a string by using a method, for example, by removing space characters.
COMMA_TO_TABLE(list, tablen OUT, tab OUT) Procedure N/A Converts a comma-delimited list of names to a table of names.
DB_VERSION(version OUT, compatibility OUT) Procedure N/A Retrieves a database version.
EXEC_DDL_STATEMENT(parse_string) Procedure N/A Executes a data description language (DDL) statement.
FORMAT_CALL_STACK Function TEXT Formats the current call stack.
GET_CPU_TIME Function NUMBER Retrieves the current CPU time.
GET_DEPENDENCY(type, schema, name) Procedure N/A Retrieve objects that are dependent upon the specified object.
GET_HASH_VALUE(name, base, hash_size) Function NUMBER Computes a hash value.
GET_PARAMETER_VALUE(parnam, intval OUT, strval OUT) Procedure BINARY_INTEGER Retrieves database initialization parameter settings.
GET_TIME Function NUMBER Retrieves the current time.
NAME_TOKENIZE(name, a OUT, b OUT, c OUT, dblink OUT, nextpos OUT) Procedure N/A Parses the specified name into its component parts.
TABLE_TO_COMMA(tab, tablen OUT, list OUT) Procedure N/A Converts a table of names to a comma-delimited list.

The implementation of DBMS_UTILITY in PolarDB-O is a partial implementation when compared with native Oracle. Only those functions and procedures listed in the preceding table are supported.

The following table lists the public variables available in the DBMS_UTILITY package.

Public variable Data type Value Description
inv_error_on_restrictions PLS_INTEGER 1 Used by the INVALIDATE procedure.
lname_array TABLE - Lists long names.
uncl_array TABLE - Lists users and names.

LNAME_ARRAY

The LNAME_ARRAY variable is used to store lists of long names including fully-qualified names.

TYPE lname_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

UNCL_ARRAY

The UNCL_ARRAY variable is used to store lists of users and names.

TYPE uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

ANALYZE_DATABASE, ANALYZE SCHEMA, and ANALYZE PART_OBJECT

You can use the ANALYZE_DATABASE(), ANALYZE_SCHEMA() and ANALYZE_PART_OBJECT() procedures to gather statistics on tables in a database. When you execute the ANALYZE statement, Postgres samples the data in a table and records distribution statistics in the pg_statistics system table.

ANALYZE_DATABASE, ANALYZE_SCHEMA, and ANALYZE_PART_OBJECT differ in the number of tables that are processed:

  • ANALYZE_DATABASE analyzes all tables in all schemas within the current database.
  • ANALYZE_SCHEMA analyzes all tables in a specified schema within the current database.
  • ANALYZE_PART_OBJECT analyzes a single table.

The ANALYZE command has the following syntax:

ANALYZE_DATABASE(method VARCHAR2 [, estimate_rows NUMBER
  [, estimate_percent NUMBER [, method_opt VARCHAR2 ]]])

ANALYZE_SCHEMA(schema VARCHAR2, method VARCHAR2
  [, estimate_rows NUMBER [, estimate_percent NUMBER
  [, method_opt VARCHAR2 ]]])

ANALYZE_PART_OBJECT(schema VARCHAR2, object_name VARCHAR2
  [, object_type CHAR [, command_type CHAR
  [, command_opt VARCHAR2 [, sample_clause ]]]])

Parameters

  • ANALYZE_DATABASE and ANALYZE_SCHEMA
    Parameter Description
    method The method parameter specifies whether the ANALYZE procedure populates the pg_statistics table or removes entries from the pg_statistics table. If you specify a method of DELETE, the ANALYZE procedure removes the relevant rows from pg_statistics. If you specify a method of COMPUTE or ESTIMATE, the ANALYZE procedure analyzes one or more multiple tables and records the distribution information in pg_statistics. The COMPUTE and ESTIMATE methods have no difference. Both methods execute the Postgres ANALYZE statement. All other parameters are validated and then ignored.
    estimate_rows The number of rows on which the estimated statistics is based. One of estimate_rows or estimate_percent must be specified if the ESTIMATE method is specified.

    This parameter is ignored, but is included for compatibility.

    estimate_percent The percentage of rows on which the estimated statistics is based. One of estimate_rows or estimate_percent must be specified if the ESTIMATE method is specified.

    This parameter is ignored, but is included for compatibility.

    method_opt The object types to be analyzed. The following combinations are supported:
    [ FOR TABLE ]
    [ FOR ALL [ INDEXED ] COLUMNS ] [ SIZE n ]
    [ FOR ALL INDEXES ]

    This parameter is ignored, but is included for compatibility.

  • ANALYZE_PART_OBJECT
    Parameter Description
    schema The name of the schema whose objects are analyzed.
    object_name The name of the partitioned object to be analyzed.
    object_type The type of object to be analyzed. Valid values: T: table, I: index.

    This parameter is ignored, but is included for compatibility.

    command_type The type of the analysis function to be run. Valid values:
    • E: gathers estimated statistics based on a specified number of rows or a percentage of rows in the sample_clause clause.
    • C: computes exact statistics.
    • V: validates the structure and integrity of the partitions.

    This parameter is ignored, but is included for compatibility.

    command_opt If command_type is set to C or E, the following combinations are supported:
    [ FOR TABLE ]
    [ FOR ALL COLUMNS ]
    [ FOR ALL LOCAL INDEXES ]

    If command_type is set to V and object_type is set to T, CASCADE is supported.

    This parameter is ignored, but is included for compatibility.

    sample_clause If command_type is set to E, the following clause is included to specify the number of rows or percentage of rows on which the estimated statistics is based:
    SAMPLE n { ROWS | PERCENT }

    This parameter is ignored, but is included for compatibility.

CANONICALIZE

The CANONICALIZE procedure supports the following features to manage an input string:

  • If the string is not enclosed in double quotation marks, checks whether the string uses the characters of a valid identifier. If not, an error message is returned. If the string is enclosed in double quotation marks, all characters are allowed.
  • If the string is not enclosed in double quotation marks and does not contain periods, capitalizes all alphabetic characters and eliminates leading and trailing spaces.
  • If the string is enclosed in double quotation marks and does not contain periods, removes the double quotation marks.
  • If the string contains periods and no portion of the string is enclosed in double quotation marks, capitalizes each portion of the string and encloses each portion in double quotation marks.
  • If the string contains periods and portions of the string are double-quoted, returns the double-quoted portions unchanged including the double quotation marks and returns the non-double-quoted portions capitalized and enclosed in double quotation marks.
CANONICALIZE(name VARCHAR2, canon_name OUT VARCHAR2,
  canon_len BINARY_INTEGER)

Parameters

Parameter Description
name The string to be canonicalized.
canon_name The canonicalized string.
canon_len The number of bytes in a name to be canonicalized starting from the first character.

Examples

The following procedure applies the CANONICALIZE procedure on its input parameter and displays the results.

CREATE OR REPLACE PROCEDURE canonicalize (
    p_name      VARCHAR2,
    p_length    BINARY_INTEGER DEFAULT 30
)
IS
    v_canon     VARCHAR2(100);
BEGIN
    DBMS_UTILITY.CANONICALIZE(p_name,v_canon,p_length);
    DBMS_OUTPUT.PUT_LINE('Canonicalized name ==>' || v_canon || '<==');
    DBMS_OUTPUT.PUT_LINE('Length: ' || LENGTH(v_canon));
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

EXEC canonicalize('Identifier')
Canonicalized name ==>IDENTIFIER<==
Length: 10

EXEC canonicalize('"Identifier"')
Canonicalized name ==>Identifier<==
Length: 10

EXEC canonicalize('"_+142%"')
Canonicalized name ==>_+142%<==
Length: 6

EXEC canonicalize('abc.def.ghi')
Canonicalized name ==>"ABC"." DEF"." GHI"<==
Length: 17

EXEC canonicalize('"abc.def.ghi"')
Canonicalized name ==>abc.def.ghi<==
Length: 11

EXEC canonicalize('"abc".def."ghi"')
Canonicalized name ==>"abc"." DEF"."ghi"<==
Length: 17

EXEC canonicalize('"abc.def".ghi')
Canonicalized name ==>"abc.def"." GHI"<==
Length: 15

COMMA_TO_TABLE

You can use the COMMA_TO_TABLE procedure to convert a comma-delimited list of names into a table of names. Each entry in the list is changed into a table entry. The names must be formatted as valid identifiers.

COMMA_TO_TABLE(list VARCHAR2, tablen OUT BINARY_INTEGER,
  tab OUT { LNAME_ARRAY | UNCL_ARRAY })

Parameters

Parameter Description
list The comma-delimited list of names from the tab parameter.
tablen The number of entries in a list.
tab The table that contains the listed names.
LNAME_ARRAY DBMS_UTILITY LNAME_ARRAY. For more information, see LNAME_ARRAY.
UNCL_ARRAY DBMS_UTILITY UNCL_ARRAY. For more information, see UNCL_ARRAY.

Examples

The following example shows how the COMMA_TO_TABLE procedure converts a list of names to a table and displays the table entries.

CREATE OR REPLACE PROCEDURE comma_to_table (
    p_list      VARCHAR2
)
IS
    r_lname     DBMS_UTILITY.LNAME_ARRAY;
    v_length    BINARY_INTEGER;
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(p_list,v_length,r_lname);
    FOR i IN 1..v_length LOOP
        DBMS_OUTPUT.PUT_LINE(r_lname(i));
    END LOOP;
END;

EXEC comma_to_table('polardb.dept, polardb.emp, polardb.jobhist')

polardb.dept
polardb.emp
polardb.jobhist

DB_VERSION

You can use the DB_VERSION procedure to return the version number of the database.

DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)

Parameters

Parameter Description
version The version of the database.
compatibility The compatibility of the database. The meaning is defined by implementation.

Examples

The following anonymous block displays the database version information.

DECLARE
    v_version       VARCHAR2(150);
    v_compat        VARCHAR2(150);
BEGIN
    DBMS_UTILITY.DB_VERSION(v_version,v_compat);
    DBMS_OUTPUT.PUT_LINE('Version: '       || v_version);
    DBMS_OUTPUT.PUT_LINE('Compatibility: ' || v_compat);
END;

Version: polardb 10.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 32-bit
Compatibility: polardb 10.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.220080704 (Red Hat 4.1.2-48), 32-bit

EXEC_DDL_STATEMENT

You can use the EXEC_DDL_STATEMENT procedure to run a DDL command.

EXEC_DDL_STATEMENT(parse_string VARCHAR2)

Parameters

Parameter Description
parse_string The DDL command to be run.

Examples

The following anonymous block creates the job table.

BEGIN
    DBMS_UTILITY.EXEC_DDL_STATEMENT(
        'CREATE TABLE job (' ||
          'jobno NUMBER(3),' ||
          'jname VARCHAR2(9))'
    );
END;

If the parse_string does not include a valid DDL statement, the following error message is returned:

#  exec dbms_utility.exec_ddl_statement('select rownum from dual');
ERROR:  polardb-20001: 'parse_string' must be a valid DDL statement

In this case, the behavior of PolarDB-O differs from that of Oracle. Oracle supports the invalid parse_string and no error message is returned.

FORMAT_CALL_STACK

You can use the FORMAT_CALL_STACK function to return the formatted contents of the current call stack.

DBMS_UTILITY.FORMAT_CALL_STACKreturn VARCHAR2

This function can be used in a stored procedure, function, or package to return the current call stack in a readable format. This function is helpful in debugging.

GET_CPU_TIME

You can use the GET_CPU_TIME function to return the CPU time in hundredths of a second from some arbitrary point in time.

Parameters

Parameter Description
cputime The number of hundredths of a second of CPU time.

Examples

The following SELECT command retrieves the current CPU time, which is 603 hundredths of a second or 0.0603 seconds.

SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

get_cpu_time
--------------
          603

GET_DEPENDENCY

You can use the GET_DEPENDENCY procedure to list the objects that are dependent on the specified object. The procedure does not show dependencies for functions or procedures.

GET_DEPENDENCY(type VARCHAR2, schema VARCHAR2,
  name VARCHAR2)

Parameters

Parameter Description
type The type of the name object. Valid values: INDEX, PACKAGE, PACKAGE BODY, SEQUENCE, TABLE, TRIGGER, TYPE, and VIEW.
schema The name of the schema in which the name object exists.
name The name of the object for which dependencies are to be retrieved.

Examples

The following anonymous block retrieves dependencies on the EMP table.

BEGIN
    DBMS_UTILITY.GET_DEPENDENCY('TABLE','public','EMP');
END;

DEPENDENCIES ON public.EMP
------------------------------------------------------------------
*TABLE public.EMP()
*   CONSTRAINT c public.emp()
*   CONSTRAINT f public.emp()
*   CONSTRAINT p public.emp()
*   TYPE public.emp()
*   CONSTRAINT c public.emp()
*   CONSTRAINT f public.jobhist()
*   VIEW .empname_view()

GET_HASH_VALUE

You can use the GET_HASH_VALUE function to compute a hash value for a specified string.

hash NUMBER GET_HASH_VALUE(name VARCHAR2, base NUMBER,
  hash_size NUMBER)

Parameters

Parameter Description
name The string for which a hash value is computed.
base The value starting from which hash values are generated.
hash_size The number of hash values for the expected hash table.
hash The hash value that is generated.

Examples

The following anonymous block creates a table of hash values by using the ename column of the emp table and then displays the key along with the hash value. The hash values start from 100 and include a maximum of 1,024 distinct values.

DECLARE
    v_hash          NUMBER;
    TYPE hash_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
    r_hash          HASH_TAB;
    CURSOR emp_cur IS SELECT ename FROM emp;
BEGIN
    FOR r_emp IN emp_cur LOOP
        r_hash(r_emp.ename) :=
            DBMS_UTILITY.GET_HASH_VALUE(r_emp.ename,100,1024);
    END LOOP;
    FOR r_emp IN emp_cur LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(r_emp.ename,10) || ' ' ||
            r_hash(r_emp.ename));
    END LOOP;
END;

SMITH      377
ALLEN      740
WARD       718
JONES      131
MARTIN     176
BLAKE      568
CLARK      621
SCOTT      1097
KING       235
TURNER     850
ADAMS      156
JAMES      942
FORD       775
MILLER     148

GET_PARAMETER_VALUE

You can use the GET_PARAMETER_VALUE procedure to retrieve database initialization parameter settings.

status BINARY_INTEGER GET_PARAMETER_VALUE(parnam VARCHAR2,
  intval OUT INTEGER, strval OUT VARCHAR2)

Parameters

Parameter Description
parnam The name of the parameter whose value is returned. The parameters are listed in the pg_settings system view.
intval The value of an integer parameter or the length of the strval parameter.
strval The value of a string parameter.
status Returns 0 if the parameter value is INTEGER or BOOLEAN. Returns 1 if the parameter value is a string.

Examples

The following anonymous block shows the values of two initialization parameters.

DECLARE
    v_intval        INTEGER;
    v_strval        VARCHAR2(80);
BEGIN
    DBMS_UTILITY.GET_PARAMETER_VALUE('max_fsm_pages', v_intval, v_strval);
    DBMS_OUTPUT.PUT_LINE('max_fsm_pages' || ': ' || v_intval);
    DBMS_UTILITY.GET_PARAMETER_VALUE('client_encoding', v_intval, v_strval);
    DBMS_OUTPUT.PUT_LINE('client_encoding' || ': ' || v_strval);
END;

max_fsm_pages: 72625
client_encoding: SQL_ASCII

GET_TIME

You can use the GET_TIME function to return the current time in hundredths of a second.

Parameters

Parameter Description
time The number of hundredths of a second elapsed since the program is started.

Examples

The following example shows the calls to the GET_TIME function.

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

 get_time
----------
  1555860

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

 get_time
----------
  1556037

NAME_TOKENIZE

You can use the NAME_TOKENIZE procedure to parse a name into its component parts. Names that are not enclosed in double quotation marks are capitalized. The double quotation marks are removed from names with double quotation marks.

NAME_TOKENIZE(name VARCHAR2, a OUT VARCHAR2,   b OUT VARCHAR2,c OUT VARCHAR2, dblink OUT VARCHAR2,   nextpos OUT BINARY_INTEGER)

Parameters

Parameter Description
name The string that contains a name in the following format:
a[.b[.c]][@dblink ]
a Returns the leftmost component.
b Returns the second component if the component exists.
c Returns the third component if the component exists.
dblink Returns the database link name.
nextpos Position of the last character parsed in the name.

Examples

The following stored procedure is used to display the returned parameter values of the NAME_TOKENIZE procedure for various names.

CREATE OR REPLACE PROCEDURE name_tokenize (
    p_name          VARCHAR2
)
IS
    v_a             VARCHAR2(30);
    v_b             VARCHAR2(30);
    v_c             VARCHAR2(30);
    v_dblink        VARCHAR2(30);
    v_nextpos       BINARY_INTEGER;
BEGIN
    DBMS_UTILITY.NAME_TOKENIZE(p_name,v_a,v_b,v_c,v_dblink,v_nextpos);
    DBMS_OUTPUT.PUT_LINE('name   : ' || p_name);
    DBMS_OUTPUT.PUT_LINE('a      : ' || v_a);
    DBMS_OUTPUT.PUT_LINE('b      : ' || v_b);
    DBMS_OUTPUT.PUT_LINE('c      : ' || v_c);
    DBMS_OUTPUT.PUT_LINE('dblink : ' || v_dblink);
    DBMS_OUTPUT.PUT_LINE('nextpos: ' || v_nextpos);
END;

Tokenize the name parameter set to emp:

BEGIN
    name_tokenize('emp');
END;

name   : emp
a      : EMP
b      :
c      :
dblink :
nextpos: 3

Tokenize the name parameter set to polardb.list_emp:

BEGIN
    name_tokenize('polardb.list_emp');
END;

name   : polardb.list_emp
a      : polardb
b      : LIST_EMP
c      :
dblink :
nextpos: 12

Tokenize the name parameter set to "polardb"."Emp_Admin".update_emp_sal:

BEGIN
    name_tokenize('"polardb"." Emp_Admin".update_emp_sal');
END;

name   : "polardb"." Emp_Admin".update_emp_sal
a      : polardb
b      : Emp_Admin
c      : UPDATE_EMP_SAL
dblink :
nextpos: 32

Tokenize the name parameter set to polardb.emp@polardb_dblink:

BEGIN
    name_tokenize('polardb.emp@polardb_dblink');
END;

name   : polardb.emp@polardb_dblink
a      : polardb
b      : EMP
c      :
dblink : polardb_DBLINK
nextpos: 18

TABLE_TO_COMMA

You can use the TABLE_TO_COMMA procedure to convert a table of names into a comma-delimited list of names. Each table entry is changed into a list entry. The names must be formatted as valid identifiers.

TABLE_TO_COMMA(tab { LNAME_ARRAY | UNCL_ARRAY },
  tablen OUT BINARY_INTEGER, list OUT VARCHAR2)

Parameters

Parameter Description
tab The table that contains names.
LNAME_ARRAY DBMS_UTILITY LNAME_ARRAY. For more information, see LNAME_ARRAY.
UNCL_ARRAY DBMS_UTILITY UNCL_ARRAY. For more information, see UNCL_ARRAY.
tablen The number of entries in the list.
list The comma-delimited list of names specified by the tab parameter.

Examples

The following example shows how the COMMA_TO_TABLE procedure converts a comma-delimited list to a table and how the TABLE_TO_COMMA procedure then converts the table back to a comma-delimited list and displays the list.

CREATE OR REPLACE PROCEDURE table_to_comma (
    p_list      VARCHAR2
)
IS
    r_lname     DBMS_UTILITY.LNAME_ARRAY;
    v_length    BINARY_INTEGER;
    v_listlen   BINARY_INTEGER;
    v_list      VARCHAR2(80);
BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE(p_list,v_length,r_lname);
    DBMS_OUTPUT.PUT_LINE('Table Entries');
    DBMS_OUTPUT.PUT_LINE('-------------');
    FOR i IN 1..v_length LOOP
        DBMS_OUTPUT.PUT_LINE(r_lname(i));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('-------------');
    DBMS_UTILITY.TABLE_TO_COMMA(r_lname,v_listlen,v_list);
    DBMS_OUTPUT.PUT_LINE('Comma-Delimited List: ' || v_list);
END;

EXEC table_to_comma('polardb.dept, polardb.emp, polardb.jobhist')

Table Entries
-------------
polardb.dept
polardb.emp
polardb.jobhist
-------------
Comma-Delimited List: polardb.dept, polardb.emp, polardb.jobhist