PolarDB:DBMS_UTILITY

Variables

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

The ANALYZE_DATABASE(), ANALYZE_SCHEMA() and ANALYZE_PART_OBJECT() stored procedures are used 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.

Syntax

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 stored procedure populates the pg_statistics table or removes entries from the pg_statistics table. If you specify a method of DELETE, the ANALYZE stored procedure removes the relevant rows from pg_statistics. If you specify a method of COMPUTE or ESTIMATE, the ANALYZE stored procedure analyzes one or more tables and records the distribution information in pg_statistics. Note The COMPUTE and ESTIMATE methods are identical. 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 the 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 stored procedure supports the following features to manage an input string:

• If the string is not enclosed in double quotation marks, verifies that the string uses the characters of a valid identifier. If not, an exception is thrown. 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, strips off 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.

Syntax

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

Parameters

Examples

1. Create a CANONICALIZE stored procedure to canonicalize an input string.

   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 CANONICALIZE stored procedure.

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

   Sample result:

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

COMMA_TO_TABLE

Syntax

The COMMA_TO_TABLE stored procedure converts 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

Examples

1. Create a COMMA_TO_TABLE stored procedure to convert a comma-delimited list of names to a table of names.

   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 COMMA_TO_TABLE stored procedure.

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

   Sample result:

   polardb.dept
    polardb.emp
    polardb.jobhist

DB_VERSION

Syntax

The DB_VERSION stored procedure returns the version number of the database.

DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)

Parameters

Examples

Execute the following anonymous block to display the database version.

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;

Sample result:

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

The EXEC_DDL_STATEMENT stored procedure is used to execute DDL statements.

EXEC_DDL_STATEMENT(parse_string VARCHAR2)

Parameters

Examples

Execute the following anonymous block to create a job.

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 formatted contents of the current call stack.

DBMS_UTILITY.FORMAT_CALL_STACK return TEXT

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

FORMAT_ERROR_BACKTRACE

Syntax

The FORMAT_ERROR_BACKTRACE function returns the formatted contents of the call stack for which an error is thrown.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE return TEXT

This function can be used in a stored procedure, function, or package to return the call stack for which an error is thrown in a readable format.

FORMAT_ERROR_STACK

Syntax

The FORMAT_ERROR_STACK function returns the formatted contents of the call stack for which an error is thrown.

DBMS_UTILITY.FORMAT_ERROR_STACK return TEXT

This function can be used in a stored procedure, function, or package to return the call stack for which an error is thrown in a readable format.

GET_CPU_TIME

Syntax

The GET_CPU_TIME function returns the CPU time in hundredths of a second from some arbitrary point in time.

Parameters

Examples

Execute the following statement to obtain the CPU time in hundredths of a second.

SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

The following result is returned, which is 6.03 hundredths of a second or 0.0603 seconds.

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

GET_DEPENDENCY

Syntax

The GET_DEPENDENCY stored procedure is used to list the objects that are dependent upon the specified object. GET_DEPENDENCY does not show dependencies for functions or stored procedures.

GET_DEPENDENCY(type VARCHAR2, schema VARCHAR2,
  name VARCHAR2)

Parameters

Examples

Execute following anonymous block to retrieve the dependencies of the EMP table.

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

Sample result:

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

The GET_HASH_VALUE function computes a hash value.

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

Parameters

Examples

Execute the following anonymous block to create a hash value table that contains a maximum 1024 hash values starting from 100 based on the ename column of the emp table.

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;

Sample result:

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

The GET_PARAMETER_VALUE stored procedure is used to retrieve database initialization parameter settings.

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

Parameters

Examples

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

Sample result:

client_encoding: UTF8

GET_TIME

Syntax

The GET_TIME function is used to return the current time in hundredths of a second.

Parameters

Examples

The following example shows the calls to the GET_TIME function.

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

Sample result:

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

NAME_TOKENIZE

Syntax

The NAME_TOKENIZE stored procedure parses a name into its components. Names that are not enclosed in double quotation marks are capitalized. The double quotation marks are stripped from names that have double quotation marks.

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

Create a NAME_TOKENIZE stored procedure to parses different names into its components.

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 that is set to emp:

  BEGIN
      name_tokenize('emp');
  END;

  Sample result:

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

• Tokenize the name parameter which is set to edb.list_emp:

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

  Sample result:

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

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

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

  Sample result:

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

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

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

  Sample result:

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

Examples

1. Create a COMMA_TO_TABLE stored procedure to convert a comma-separated list to a table, and then create a TABLE_TO_COMMA stored procedure to convert the table back to the comma-separated 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 COMMA_TO_TABLE stored procedure.

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

   Sample result:

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