DBMS_UTILITY - PolarDB - Alibaba Cloud Documentation Center

The built-in DBMS_UTILITY package provides a variety of utility subprograms.

Subprograms

Subprogram	Description
ANALYZE_DATABASE Procedure	Analyzes a table in a database.
ANALYZE_SCHEMA Procedure	Analyzes a table in a schema.
ANALYZE_PART_OBJECT Procedure	Analyzes a single table.
CANONICALIZE Procedure	Standardize a given string.
COMMA_TO_TABLE Procedure	Converts a list of name identifiers that are separated by commas (,) into a name table.
DB_VERSION Procedure	Retrieves a database version.
EXEC_DDL_STATEMENT Procedure	Executes a DDL statement.
FORMAT_CALL_STACK Function	Formats the current call stack.
FORMAT_ERROR_BACKTRACE Function	Formats stack information from the current error point to the exception handler that catches the error.
FORMAT_ERROR_STACK Function	Formats the current error stack.
GET_CPU_TIME Function	Returns the current CPU time in hundredths of a second.
GET_DEPENDENCY Procedure	Displays the dependency of a given object.
GET_HASH_VALUE Function	Computes the hash value of a given string.
GET_PARAMETER_VALUE Function	Retrieves database initialization parameter settings.
GET_TIME Function	Returns the current clock time in hundredths of a second.
NAME_TOKENIZE Procedure	Parses a specified name into its components.
TABLE_TO_COMMA Procedure	Converts a table of name identifiers into a list in which the name identifiers are separated by commas (,).

Data types

LNAME_ARRAY

The LNAME_ARRAY data type is used to store a list of long names including fully-qualified names.

TYPE LNAME_ARRAY IS TABLE OF VARCHAR2(4000);

UNCL_ARRAY

The UNCL_ARRAY data type is used to store a list of name identifiers.

TYPE UNCL_ARRAY IS TABLE OF VARCHAR2(227);

ANALYZE_DATABASE

This stored procedure is used to analyze a table in a database.

Syntax

DBMS_UTILITY.ANALYZE_DATABASE (
   method             IN  VARCHAR2,
   estimate_rows      IN  INTEGER DEFAULT NULL,
   estimate_percent   IN  INTEGER DEFAULT NULL,
   method_opt         IN  VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Description
method	The method that is used to execute the ANALYZE statement. Valid values: DELETE COMPUTE ESTIMATE The COMPUTE and ESTIMATE methods have no differences. These methods are used to execute the ANALYZE statement. Other parameters are provided for the compatibility with Oracle and ignored during execution.
estimate_rows	(Optional) The number of rows based on which you estimate statistics.
estimate_percent	(Optional) The percentage of rows based on which you estimate statistics.
method_opt	(Optional) The type of the object that you want to analyze.

Example

CALL DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');

ANALYZE_SCHEMA

This stored procedure is used to analyze a table in a schema.

Syntax

DBMS_UTILITY.ANALYZE_SCHEMA (
   schema             IN  VARCHAR2,
   method             IN  VARCHAR2,
   estimate_rows      IN  INTEGER DEFAULT NULL,
   estimate_percent   IN  INTEGER DEFAULT NULL,
   method_opt         IN  VARCHAR2 DEFAULT NULL);

Parameters

Parameter	Description
schema	The name of the schema.
method	The method that is used to execute the ANALYZE statement. Valid values: DELETE COMPUTE ESTIMATE The COMPUTE and ESTIMATE methods have no differences. These methods are used to execute the ANALYZE statement. Parameters other than schema and method are provided for the compatibility with Oracle and ignored during execution.
estimate_rows	(Optional) The number of rows based on which you estimate statistics.
estimate_percent	(Optional) The percentage of rows based on which you estimate statistics.
method_opt	(Optional) The type of the object that you want to analyze.

Example

CALL DBMS_UTILITY.ANALYZE_SCHEMA('public', 'compute');

ANALYZE_PART_OBJECT

This stored procedure is used to analyze a single table.

Syntax

DBMS_UTILITY.ANALYZE_PART_OBJECT (
   schema        IN VARCHAR2 DEFAULT NULL,
   object_name   IN VARCHAR2 DEFAULT NULL,
   object_type   IN CHAR     DEFAULT 'T',
   command_type  IN CHAR     DEFAULT 'E',
   command_opt   IN VARCHAR2 DEFAULT NULL,
   sample_clause IN VARCHAR2 DEFAULT 'sample 5 percent ');

Parameters

Parameter	Description
schema	The schema name of the object that you want to analyze.
object_name	The object that you want to analyze.
object_type	(Optional) The type of the object that you want to analyze.
command_type	(Optional) The type of the analysis command that you want to run.
command_opt	(Optional parameter) The option available for the command_type parameter.
sample_clause	(Optional) The sample clause that is used when the command_type parameter is set to `'E'`.

Note

Parameters other than schema and object_name are provided for the compatibility with Oracle and ignored during execution.

Example

CALL DBMS_UTILITY.analyze_part_object('public', 'table2');

CANONICALIZE

This stored procedure is used to standardize a given string.

Syntax

DBMS_UTILITY.CANONICALIZE(
   name           IN    VARCHAR2,
   canon          OUT   VARCHAR2,
   canon_len      IN    INTEGER);

Parameters

Parameter	Description
name	The string that you want to standardize.
canon	The string that is standardized.
canon_len	The number of bytes that you want to standardize from the first character in the string specified by the name parameter.

Example

The following example shows how to standardize a given string:

DECLARE
  name varchar2 default  'aBc."dEf"."ghi"';
  length integer default 50;
  canon  varchar2;
BEGIN
    DBMS_UTILITY.CANONICALIZE(name,canon,length);
    DBMS_OUTPUT.PUT_LINE(canon);
END;
-- "ABC"."dEf"."ghi"

COMMA_TO_TABLE

This stored procedure is used to convert a list of name identifiers that are separated by commas (,) into a name table.

Syntax

DBMS_UTILITY.COMMA_TO_TABLE ( 
   list   IN  VARCHAR2,
   tablen OUT INTEGER,
   tab    OUT UNCL_ARRAY);

Parameters

Parameter	Description
list	The list of name identifiers that are separated by commas (,).
tablen	The number of entries in the table specified by the tab parameter.
tab	The table that contains the names of name identifiers specified by the list parameter.

Example

The following example shows how to convert a list of name identifiers that are separated by commas (,) into a name table:

DECLARE
    lname    DBMS_UTILITY.LNAME_ARRAY;
    length   integer;
    list     varchar2;
BEGIN
    list := 'a.b.c.d , b , c , select1';
    DBMS_UTILITY.COMMA_TO_TABLE(list,length,lname);
    FOR i IN 1..length LOOP
        DBMS_OUTPUT.PUT_LINE('-> ' || lname(i) || '<- ' || length(lname(i)));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('--  finished  --');
END;

-> a.b.c.d <- 8
-> b <- 3
-> c <- 3
-> select1 <- 8
--  finished  --

DB_VERSION

This stored procedure is used to retrieve the information of a database version.

Syntax

DBMS_UTILITY.DB_VERSION (
   version       OUT VARCHAR2,
   compatibility OUT VARCHAR2);

Parameters

Parameter	Description
version	The database version.
compatibility	This parameter is provided for the compatibility with Oracle and can be ignored.

Example

The following example shows how to retrieve the information of a database version:

DECLARE
    version varchar2(100);
    compatibility varchar2(100);
BEGIN
    DBMS_UTILITY.DB_VERSION(version, compatibility); 
    DBMS_OUTPUT.PUT_LINE(version);
    DBMS_OUTPUT.PUT_LINE(compatibility);
END;
-- PostgreSQL 14.8 (PolarDB 14.8.10.0 build 0d3bf26c debug)
-- PostgreSQL 14.8 (PolarDB 14.8.10.0 build 0d3bf26c debug)

EXEC_DDL_STATEMENT

This stored procedure is used to execute a DDL statement.

Syntax

DBMS_UTILITY.EXEC_DDL_STATEMENT (
   parse_string IN VARCHAR2);

Parameters

Parameter	Description
parse_string	The DDL statement that you want to execute.

Example

The following example shows how to use the EXEC_DDL_STATEMENT stored procedure to execute a DDL statement:

CALL DBMS_UTILITY.EXEC_DDL_STATEMENT('CREATE TABLE test(a int, b varchar2(20))');

SELECT * FROM test;
 a | b
---+---
(0 rows)

FORMAT_CALL_STACK

This function is used to format the current call stack.

Syntax

DBMS_UTILITY.FORMAT_CALL_STACK();
  RETURN VARCHAR2;

Return values

Return value	Description
VARCHAR2	The information of the formatted call stack.

Example

The following example shows how to format the current call stack:

CREATE FUNCTION func() RETURNS varchar2
IS
BEGIN
     return dbms_utility.format_call_stack();
END;

select * from func();
                       func
--------------------------------------------------
 ----- PL/SQL Call Stack -----                   +
   object    line     object                     +
   handle    number    name                      +
        0        2   function format_call_stack()+
    17089        2   function func()
(1 row)

FORMAT_ERROR_BACKTRACE

This function is used to format stack information from the current error point to the exception handler that catches the error.

Syntax

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
  RETURN VARCHAR2;

Return values

Return value	Description
VARCHAR2	The information of the backtracked stack.

Example

The following example creates a calling chain for a function. In the example, the called function throws an exception, and the caller caches the exception and uses the FORMAT_ERROR_BACKTRACE function to display the information of the error stack.

CREATE FUNCTION inner(a integer) RETURNS integer
IS
DECLARE
  res integer;
BEGIN
  res = a/0;
  RETURN res;
EXCEPTION
  WHEN others THEN
    RAISE EXCEPTION 'expected exception';
END;

CREATE FUNCTION outer() RETURNS integer
IS
BEGIN
  return inner(100);
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
    return -1;
END;

SELECT outer() FROM dual;

----- Error Stack -----
ERR-33816706: division by zero
PL/SQL function "inner"(integer) line 4 at assignment
PL/SQL function "outer"() line 2 at RETURN
ERR-16777248: expected exception
PL/SQL function "inner"(integer) line 8 at RAISE
PL/SQL function "outer"() line 2 at RETURN

FORMAT_ERROR_STACK

This function is used to format the current error stack.

Syntax

DBMS_UTILITY.FORMAT_ERROR_STACK 
  RETURN VARCHAR2;

Return values

Return value	Description
VARCHAR2	The information of the formatted error call stack.

Example

The following example creates a calling chain for a function to be called where the called function throws an exception, and the caller caches the exception and uses the FORMAT_ERROR_STACK function to display the information of the error stack:

CREATE FUNCTION inner(a integer) RETURNS integer
IS
DECLARE
  res integer;
BEGIN
  res = a/0;
  RETURN res;
EXCEPTION
  WHEN others THEN
    RAISE EXCEPTION 'expected exception';
END;

CREATE FUNCTION outer() RETURNS integer
IS
BEGIN
  return inner(100);
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK());
    return -1;
END;

SELECT outer() FROM dual;

----- Error Stack -----
ERR-33816706: division by zero
PL/SQL function "inner"(integer) line 4 at assignment
PL/SQL function "outer"() line 2 at RETURN
ERR-16777248: expected exception
PL/SQL function "inner"(integer) line 8 at RAISE
PL/SQL function "outer"() line 2 at RETURN

GET_CPU_TIME

This function is used to return the current CPU time.

Syntax

DBMS_UTILITY.GET_CPU_TIME
   RETURN INTEGER;

Return values

Return value	Description
INTEGER	The CPU time at any point in time in hundredths of a second.

Example

The following example shows how to return two CPU time values before and after the program sleeps. The difference between the two CPU time values is much smaller than the sleeping time of the program.

BEGIN
    DBMS_OUTPUT.PUT_LINE('start cpu time:' || DBMS_UTILITY.GET_CPU_TIME());
-- start cpu time:11
    DBMS_SESSION.SLEEP(5);
    DBMS_OUTPUT.PUT_LINE('end cpu time:' || DBMS_UTILITY.GET_CPU_TIME());
-- end cpu time:11
END;

GET_DEPENDENCY

This stored procedure is used to display the dependency of a given object.

Syntax

DBMS_UTILITY.GET_DEPENDENCY
   type      IN     VARCHAR2,
   schema    IN     VARCHAR2,
   name      IN     VARCHAR2);

Parameters

Parameter	Description
type	The type of the object specified by the name parameter.
schema	The name of the schema in which the object specified by the name parameter exists.
name	The name of the object whose dependency you want to retrieve.

Example

The following example creates a table and a view for the table. This example shows how to use the GET_DEPENDENCY stored procedure to view the dependency of the view:

CREATE TABLE t(a int, b int);
CREATE VIEW v AS SELECT a, b FROM t;
CALL DBMS_UTILITY.GET_DEPENDENCY('view','public','v');
-- TABLE T

GET_HASH_VALUE

This function is used to calculate the hash value of a given string.

Syntax

DBMS_UTILITY.GET_HASH_VALUE (
   name      VARCHAR2, 
   base      INTEGER, 
   hash_size INTEGER)
  RETURN INTEGER;

Parameters

Parameter	Description
name	The string for which you want to calculate the hash value.
base	The starting base value of the returned hash value.
hash_size	The range of the returned hash value.

Return values

Return value	Description
INTEGER	The generated hash value.

Example

The following example shows how to calculate the hash value of a given string. The hash value calculated by this example ranges from 1 to 99.

SELECT DBMS_UTILITY.GET_HASH_VALUE('PolarDB', 1, 100) FROM dual;

 get_hash_value 
----------------
             67
(1 row)

GET_PARAMETER_VALUE

This function is used to retrieve the database initialization parameter settings.

Syntax

DBMS_UTILITY.GET_PARAMETER_VALUE (
   parnam     IN        VARCHAR2,
   intval     OUT       INTEGER,
   strval     OUT       VARCHAR2)
  RETURN INTEGER;

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 value.
strval	The value of a string parameter.

Return values

Return value	Description
INTEGER	If the parameter is set to INTEGER, the value 0 is returned. Otherwise, the value 1 is returned.

Example

The following example shows how to use the GET_PARAMETER_VALUE function to retrieve the parameters of string and integer types:

DECLARE
    intval     INTEGER;
    strval     VARCHAR2(80);
    ret        INTEGER;
BEGIN
    ret := DBMS_UTILITY.GET_PARAMETER_VALUE('client_encoding', intval, strval);
    DBMS_OUTPUT.PUT_LINE('ret is: ' || ret || '; intval is: ' || intval || '; strval is: ' || strval);
    ret := DBMS_UTILITY.GET_PARAMETER_VALUE('block_size', intval, strval);
    DBMS_OUTPUT.PUT_LINE('ret is: ' || ret || '; intval is: ' || intval || '; strval is: ' || strval);
END;
ret is: 1; intval is: 4; strval is: UTF8
ret is: 0; intval is: 8192; strval is:

GET_TIME

This function is used to return the current time.

Syntax

DBMS_UTILITY.GET_TIME
  RETURN INTEGER;

Return values

Return value	Description
INTEGER	The clock time at any point in time in hundredths of a second.

Example

The following example shows how to use the GET_TIME function to retrieve the time difference before and after a program is executed:

DECLARE
  start_time integer;
  end_time integer;
BEGIN
    start_time := DBMS_UTILITY.GET_TIME;
    DBMS_LOCK.SLEEP(5);
    end_time := DBMS_UTILITY.GET_TIME;
    DBMS_OUTPUT.PUT_LINE('total time is:' || end_time - start_time);
END;

NAME_TOKENIZE

This stored procedure is used to parse a given name into its components.

Syntax

DBMS_UTILITY.NAME_TOKENIZE ( 
   name    IN  VARCHAR2,
   a       OUT VARCHAR2,
   b       OUT VARCHAR2,
   c       OUT VARCHAR2,
   dblink  OUT VARCHAR2, 
   nextpos OUT INTEGER);

Parameters

Parameter	Description
name	The input name. The name consists of SQL identifiers in the format of a [ . b [ . c ] ] [ @ dblink ].
a	The first identifier in the name parameter value.
b	The second identifier in the name parameter value.
c	The third identifier in the name parameter value.
dblink	The dblink field of the name parameter value.
nextpos	The next character position after the input name is parsed.

Example

The following example shows how to use the NAME_TOKENIZE stored procedure to parse a given name:

DECLARE
  a             varchar2;
  b             varchar2;
  c             varchar2;
  dblink        varchar2;
  nextpos       integer;
  name          varchar2;
BEGIN
  name := 'sch.tbl@dblink';
  DBMS_UTILITY.NAME_TOKENIZE(name, a, b, c, dblink, nextpos);
  DBMS_OUTPUT.PUT_LINE('name   : ' || name);
  DBMS_OUTPUT.PUT_LINE('a      : ' || a);
  DBMS_OUTPUT.PUT_LINE('b      : ' || b);
  DBMS_OUTPUT.PUT_LINE('c      : ' || c);
  DBMS_OUTPUT.PUT_LINE('dblink : ' || dblink);
  DBMS_OUTPUT.PUT_LINE('nextpos: ' || nextpos);
END;
name   : sch.tbl@dblink
a      : SCH
b      : TBL
c      :
dblink : DBLINK
nextpos: 14

TABLE_TO_COMMA

This stored procedure is used to convert a table of name identifiers into a list in which the name identifiers are separated by commas (,).

Syntax

DBMS_UTILITY.TABLE_TO_COMMA ( 
   tab    IN  UNCL_ARRAY, 
   tablen OUT INTEGER,
   list   OUT VARCHAR2);

Parameters

Parameter	Description
tab	The table that contains name identifiers.
tablen	The number of entries in the table specified by the tab parameter.
list	The list of name identifiers that are separated by commas (,).

Example

The following example shows how to convert a table of name identifiers into a list in which the name identifiers are separated by commas (,):

DECLARE
    result   varchar2;
    length   integer;
    list     dbms_utility.lname_array;
BEGIN
    list := dbms_utility.lname_array('name1', 'name2', 'name3');
    DBMS_UTILITY.TABLE_TO_COMMA(list, length, result);
    DBMS_OUTPUT.PUT_LINE('result is: ' || result || '; length is: ' || length );
END;
-- result is: name1,name2,name3; length is: 3