PolarDB:DBMS_UTILITY

LNAME_ARRAY

LNAME_ARRAY stores lists of long names, including fully qualified names.

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

UNCL_ARRAY

UNCL_ARRAY stores lists of users and names.

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

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

Parameters — ANALYZE_PART_OBJECT

Syntax

CANONICALIZE(name VARCHAR2, canon_name OUT VARCHAR2,
  canon_len BINARY_INTEGER)

Parameters

Examples

All examples use this wrapper procedure:

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;

Quoted identifier with special characters — quotes stripped:

EXEC canonicalize('"_+142%"');

Canonicalized name ==>_+142%<==
Length: 6

Syntax

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

Parameters

Example

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

Syntax

DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)

Parameters

Example

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: PostgreSQL 11.15 (POLARDB Database Compatible with Oracle 11.15.25)
Compatibility: PostgreSQL 11.15 (POLARDB Database Compatible with Oracle 11.15.25)

Syntax

EXEC_DDL_STATEMENT(parse_string VARCHAR2)

Parameters

Example

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

If parse_string is not a valid DDL statement, PolarDB for PostgreSQL (Compatible with Oracle) raises an error: Oracle databases accept an invalid parse_string without raising an error.

EXEC dbms_utility.exec_ddl_statement('select rownum from dual');
ERROR:  'parse_string' must be a valid DDL statement

Syntax

DBMS_UTILITY.FORMAT_CALL_STACK RETURN TEXT

Syntax

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE RETURN TEXT

Syntax

DBMS_UTILITY.FORMAT_ERROR_STACK RETURN TEXT

PolarDB for PostgreSQL (Compatible with Oracle) implements FORMAT_ERROR_STACK differently from Oracle. In Oracle, FORMAT_ERROR_STACK returns strings containing SQLCODE and SQLERRM information. In PolarDB for PostgreSQL (Compatible with Oracle), FORMAT_ERROR_STACK behaves the same as FORMAT_ERROR_BACKTRACE.

Syntax

DBMS_UTILITY.GET_CPU_TIME RETURN NUMBER

Parameters

Example

SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

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

The value 603 means 6.03 hundredths of a second, or 0.0603 seconds of CPU time.

Usage notes

• GET_DEPENDENCY does not show dependencies for functions or stored procedures.

Syntax

GET_DEPENDENCY(type VARCHAR2, schema VARCHAR2, name VARCHAR2)

Parameters

Example

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()

Syntax

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

Parameters

Example

The following example builds a hash table from the ename column of the emp table, generating up to 1,024 hash values starting from 100.

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

Syntax

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

Parameters

Example

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

client_encoding: UTF8

Syntax

DBMS_UTILITY.GET_TIME RETURN NUMBER

Parameters

Example

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

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

Syntax

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

Parameters

Examples

All examples use this wrapper procedure:

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;

Single unquoted name — capitalized:

BEGIN
    name_tokenize('emp');
END;

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

Two-part name — both components capitalized:

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

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

Three-part name with mixed quoting — quoted parts preserved, unquoted part capitalized:

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: 36

Name with database link:

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

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

Syntax

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

Parameters

Example

The following example converts a comma-separated list into a table using COMMA_TO_TABLE, then converts it back to a comma-separated list using TABLE_TO_COMMA.

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