PolarDB:DBMS_UTILITY

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

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.

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

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

Parameters

Examples

The following procedure applies the CANONICALIZE stored 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

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

The following procedure uses the COMMA_TO_TABLE stored procedure to convert a list of names to a table. The table entries are then displayed.

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

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

DB_VERSION(version OUT VARCHAR2, compatibility OUT VARCHAR2)

Parameters

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

The EXEC_DDL_STATEMENT stored procedure is used to execute DDL statements.

EXEC_DDL_STATEMENT(parse_string VARCHAR2)

Parameters

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 for PostgreSQL(Compatible with Oracle) databases compatible with Oracle differs from that of Oracle. Oracle supports the invalid parse_string and no error message is returned.

FORMAT_CALL_STACK

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. This function is helpful in debugging.

FORMAT_ERROR_BACKTRACE

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. This function is helpful in debugging.

FORMAT_ERROR_STACK

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. This function is helpful in debugging.

GET_CPU_TIME

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

Parameters

Examples

The following SELECT statement 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

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

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

The GET_HASH_VALUE function is used to compute a hash value for a specified string.

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

Parameters

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

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

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

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;

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

SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

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

NAME_TOKENIZE

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

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

BEGIN
    name_tokenize('emp');
END;

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

Tokenize the name parameter which is 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 which is 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 which is 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

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

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

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