This topic describes how to use the DBMS_UTILITY package.
The DBMS_UTILITY package supports the following utility programs.
Function or stored procedure | Type | Return value type | Description |
---|---|---|---|
ANALYZE_DATABASE(method [, estimate_rows [, estimate_percent [, method_opt ]]]) | Stored procedure | N/A | Analyzes database tables. |
ANALYZE_PART_OBJECT(schema, object_name [, object_type [, command_type [, command_opt [, sample_clause ]]]]) | Stored procedure | N/A | Analyzes a partitioned table. |
ANALYZE_SCHEMA(schema, method [, estimate_rows [, estimate_percent [, method_opt ]]]) | Stored procedure | N/A | Analyzes schema tables. |
CANONICALIZE(name, canon_name OUT, canon_len) | Stored procedure | N/A | Canonicalizes a string by using a method, such as stripping off spaces. |
COMMA_TO_TABLE(list, tablen OUT, tab OUT) | Stored procedure | N/A | Converts a comma-delimited list of names to a table of names. |
DB_VERSION(version OUT, compatibility OUT) | Stored procedure | N/A | Retrieves a database version. |
EXEC_DDL_STATEMENT(parse_string) | Stored procedure | N/A | Executes a DDL statement. |
FORMAT_CALL_STACK | Function | TEXT | Returns the formatted contents of the current call stack. |
FORMAT_ERROR_STACK | Function | TEXT | Returns the formatted contents of the call stack for which an error is thrown. |
FORMAT_ERROR_BACKTRACE | Function | TEXT | Returns the formatted contents of the call stack for which an error is thrown. |
GET_CPU_TIME | Function | NUMBER | Retrieves the current CPU time. |
GET_DEPENDENCY(type, schema, name) | Stored procedure | N/A | Retrieves objects that are dependent upon the specified object. |
GET_HASH_VALUE(name, base, hash_size) | Function | NUMBER | Computes a hash value. |
GET_PARAMETER_VALUE(parnam, intval OUT, strval OUT) | Stored procedure | BINARY_INTEGER | Retrieves database initialization parameter settings. |
GET_TIME | Function | NUMBER | Obtains the current time. |
NAME_TOKENIZE(name, a OUT, b OUT, c OUT, dblink OUT, nextpos OUT) | Stored procedure | N/A | Parses the specified name into its components. |
TABLE_TO_COMMA(tab, tablen OUT, list OUT) | Stored procedure | N/A | Converts a table of names to a comma-delimited list. |
The implementation of DBMS_UTILITY in PolarDB for PostgreSQL(Compatible with Oracle) is a partial implementation, which is different from native Oracle. Only the functions and stored procedures listed in the preceding table are supported.
The following table lists the public variables available in the DBMS_UTILITY package.
Public variable | Data type | Value | Description |
---|---|---|---|
inv_error_on_restrictions | PLS_INTEGER | 1 | Used by the INVALIDATE stored procedure. |
lname_array | TABLE | - | Lists long names. |
uncl_array | TABLE | - | Lists users and names. |
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
Parameter | Description |
---|---|
name | The string to be canonicalized. |
canon_name | The canonicalized string. |
canon_len | The number of bytes in a name to be canonicalized starting from the first character. |
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
Parameter | Description |
---|---|
list | The comma-delimited list of names from the tab parameter. |
tablen | The number of entries in a list. |
tab | The table that contains the listed names. |
LNAME_ARRAY | DBMS_UTILITY LNAME_ARRAY. For more information, see LNAME_ARRAY. |
UNCL_ARRAY | DBMS_UTILITY UNCL_ARRAY. For more information, see UNCL_ARRAY. |
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
Parameter | Description |
---|---|
version | The engine version of the instance. |
compatibility | The compatibility of the database. The meaning is defined by implementation. |
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
Parameter | Description |
---|---|
parse_string | The DDL statement to be executed. |
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
Parameter | Description |
---|---|
cputime | The number of hundredths of a second of CPU time. |
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
Parameter | Description |
---|---|
type | The type of the name object. Valid values: INDEX, PACKAGE, PACKAGE BODY, SEQUENCE, TABLE, TRIGGER, TYPE, and VIEW. |
schema | The name of the schema in which the name object exists. |
name | The name of the object for which dependencies are to be retrieved. |
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
Parameter | Description |
---|---|
name | The string for which a hash value is computed. |
base | The value starting from which hash values are generated. |
hash_size | The number of hash values for the expected hash table. |
hash | The hash value that is generated. |
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
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. |
strval | The value of a string parameter. |
status | Returns 0 if the parameter value is INTEGER or BOOLEAN. Returns 1 if the parameter value is a string. |
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
Parameter | Description |
---|---|
time | The number of hundredths of a second elapsed since the program is started. |
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
Parameter | Description |
---|---|
name | The string that contains a name in the following format:
|
a | Returns the leftmost component. |
b | Returns the second component if the component exists. |
c | Returns the third component if the component exists. |
dblink | Returns the database link name. |
nextpos | Position of the last character parsed in the name. |
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
Parameter | Description |
---|---|
tab | The table that contains names. |
LNAME_ARRAY | DBMS_UTILITY LNAME_ARRAY. For more information, see LNAME_ARRAY. |
UNCL_ARRAY | DBMS_UTILITY UNCL_ARRAY. For more information, see UNCL_ARRAY. |
tablen | The number of entries in the list. |
list | The comma-delimited list of names specified by the tab parameter. |
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