PolarDB:DBMS_UTILITY

DBMS_UTILITY types

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

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

Usage

ANALYZE_DATABASE, ANALYZE_SCHEMA, and ANALYZE_PART_OBJECT

Use the ANALYZE_DATABASE(), ANALYZE_SCHEMA(), and ANALYZE_PART_OBJECT() stored procedures to collect statistics for tables in a database. When you run an ANALYZE statement, Postgres samples the data in the table and records distribution statistics in the pg_statistics system table.

The main difference between ANALYZE_DATABASE, ANALYZE_SCHEMA, and ANALYZE_PART_OBJECT is their scope:

• ANALYZE_DATABASE: Analyzes all tables in all schemas within the current database.

• ANALYZE_SCHEMA: Analyzes all tables in a given schema within the current database.

• ANALYZE_PART_OBJECT: Analyzes a single table.

Syntax

The syntax for the ANALYZE commands is as follows:

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	Specifies whether the ANALYZE stored procedure populates the pg_statistics table or deletes rows from it. The DELETE method deletes the relevant rows from pg_statistics. The COMPUTE and ESTIMATE methods analyze one or more tables and record distribution information in pg_statistics. Note COMPUTE and ESTIMATE are identical. Both methods execute a Postgres ANALYZE statement. All other parameters are validated and then ignored.
  estimate_rows	Specifies the number of rows on which to base the estimated statistics. This parameter is provided for compatibility and is ignored.
  estimate_percent	Specifies the percentage of rows on which to base the estimated statistics. This parameter is provided for compatibility and is ignored.
  method_opt	The type of object to analyze. Any combination of the following: [ FOR TABLE ] [ FOR ALL [ INDEXED ] COLUMNS ] [ SIZE n ] [ FOR ALL INDEXES ] This parameter is provided for compatibility and is ignored.

• ANALYZE_PART_OBJECT

  Parameter	Description
  schema	The name of the schema containing the objects to analyze.
  object_name	The name of the partitioned object to analyze.
  object_type	The type of object to analyze. Valid values are T for table and I for index. This parameter is provided for compatibility and is ignored.
  command_type	The type of analysis to perform. Valid values are: E: Collects estimated statistics based on the number or percentage of rows specified in the sample_clause. C: Computes exact statistics. V: Validates the structure and integrity of the partitions. This parameter is provided for compatibility and is ignored.
  command_opt	If command_type is C or E, the value can be any combination of the following: [ FOR TABLE ] [ FOR ALL COLUMNS ] [ FOR ALL LOCAL INDEXES ] If command_type is V, the value can be CASCADE if object_type is T. This parameter is provided for compatibility and is ignored.
  sample_clause	If command_type is E, this clause specifies the number or percentage of rows on which to base the estimate. SAMPLE n { ROWS | PERCENT } This parameter is provided for compatibility and is ignored.

Canonicalize

The CANONICALIZE stored procedure canonicalizes an input string as follows:

• If a string is not enclosed in double quotation marks, the procedure validates that it is a valid identifier. If the validation fails, the procedure raises an exception. If the string is enclosed in double quotation marks, all characters are allowed.

• If a string is not enclosed in double quotation marks and contains no periods, the procedure converts all alphabetic characters to uppercase and removes any leading or trailing spaces.

• If a string is enclosed in double quotation marks and contains no periods, the procedure removes the double quotation marks.

• If a string contains periods and no segment is enclosed in double quotation marks, the procedure converts each segment to uppercase and then encloses it in double quotation marks.

• If a string contains periods and some segments are enclosed in double quotation marks, the procedure returns the quoted segments as is, including the quotation marks. It converts the unquoted segments to uppercase and encloses them in double quotation marks.

Syntax

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

Parameters

Examples

1. Create a wrapper stored procedure to call DBMS_UTILITY.CANONICALIZE and display 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;

2. Execute the wrapper procedure with a quoted string that contains special characters.

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

   The following result is returned:

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

COMMA_TO_TABLE

Syntax

The COMMA_TO_TABLE stored procedure converts a comma-separated list of names into a table. Each entry in the list becomes 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

Example

1. Create a wrapper stored procedure to convert a list of names to a table.

   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;

2. Execute the following statement:

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

   The statement returns the following result:

   polardb.dept
    polardb.emp
    polardb.jobhist

DB_VERSION

Syntax

The DB_VERSION stored procedure returns the database version.

DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)

Parameters

Example

Execute the following anonymous block to display 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;

The output is:

Version: PostgreSQL 11.15 (POLARDB Database Compatible with Oracle 11.15.25)
Compatibility: PostgreSQL 11.15 (POLARDB Database Compatible with Oracle 11.15.25)

EXEC_DDL_STATEMENT

Syntax

EXEC_DDL_STATEMENT executes a DDL statement.

EXEC_DDL_STATEMENT(parse_string VARCHAR2)

Parameters

Example

The following anonymous block creates the job table.

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

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

FORMAT_CALL_STACK

Syntax

The FORMAT_CALL_STACK function returns the current call stack as formatted text.

DBMS_UTILITY.FORMAT_CALL_STACK return TEXT

Call this function from within a stored procedure, function, or package to get the current call stack.

FORMAT_ERROR_BACKTRACE

Syntax

The FORMAT_ERROR_BACKTRACE function returns the formatted call stack from the point at which an exception is raised.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE RETURN TEXT

This function is designed for use within a stored procedure, function, or package.

FORMAT_ERROR_STACK

Syntax

The FORMAT_ERROR_STACK function returns the call stack at the point of an exception as a formatted string.

DBMS_UTILITY.FORMAT_ERROR_STACK return TEXT

You can call this function within a stored procedure, function, or package to retrieve the call stack.

GET_CPU_TIME

Syntax

The GET_CPU_TIME function returns the current CPU time in hundredths of a second, measured from an arbitrary point in time.

Parameter

Example

Run the following SELECT statement to retrieve the current CPU time.

SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

The statement returns a result similar to the following, where the value 603 represents 603 hundredths of a second (6.03 seconds).

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

GET_DEPENDENCY

Syntax

Use the GET_DEPENDENCY stored procedure to list the objects that depend on a specified object. It does not show dependencies for functions or stored procedures.

GET_DEPENDENCY(type VARCHAR2, schema VARCHAR2,
  name VARCHAR2)

Parameters

Example

Execute the following anonymous block to find dependencies on the EMP table.

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

The result is as follows:

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

Syntax

Use the GET_HASH_VALUE function to compute the hash value for a given string.

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

Parameters

Example

The following anonymous block creates a hash table using the ename column of the emp table and then displays each key and its corresponding hash value. The hash values start at 100, and the function generates up to 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;

The following is the sample output:

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

Syntax

Use the GET_PARAMETER_VALUE stored procedure to retrieve the value of a database initialization parameter.

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

Parameters

Example

The following anonymous block displays the value of an initialization parameter.

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;

The following is the result:

client_encoding: UTF8

GET_TIME

Syntax

The GET_TIME function returns the current time in hundredths of a second.

Parameters

Example

This example calls the GET_TIME function.

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

The query returns the following output:

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

NAME_TOKENIZE

Syntax

The NAME_TOKENIZE stored procedure parses a name into its components. It converts unquoted components to uppercase and removes the double quotation marks from quoted components.

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

Parameters

a[.b[.c]][@dblink ]

Examples

Run the following statement to create a stored procedure that displays the output parameters from NAME_TOKENIZE for different 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;

• Single unquoted name—capitalized: This example tokenizes emp.

  BEGIN
      name_tokenize('emp');
  END;

  The following output is displayed:

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

• Two-part unquoted name—both components capitalized: This example tokenizes polardb.list_emp.

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

  The following output is displayed:

  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: This example tokenizes "polardb"."Emp_Admin".update_emp_sal.

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

  The following output is displayed:

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

• Name with a database link: This example tokenizes polardb.emp@polardb_dblink.

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

  The following output is displayed:

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

TABLE_TO_COMMA

Syntax

The TABLE_TO_COMMA stored procedure converts a table of names into a comma-delimited list. Each table entry becomes a list item. The names must be valid identifiers.

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

Parameters

Example

1. Execute the following statement to create a stored procedure. This procedure first uses COMMA_TO_TABLE to convert a comma-delimited list to a table, and then uses TABLE_TO_COMMA to convert the table back to a comma-delimited 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;

2. Execute the stored procedure:

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

   The procedure returns the following:

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