Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Stored Procedures/ Advanced Packages/ Secondary Encapsulation APIs (Recommended)/ DBE_UTILITY

Updated on 2024-05-07 GMT+08:00

View PDF

DBE_UTILITY

API Description

Table 1 provides all APIs supported by the DBE_UTILITY package.

**Table 1** DBE_UTILITY
API	Description
DBE_UTILITY.FORMAT_ERROR_BACKTRACE	Outputs the call stack of an abnormal stored procedure.
DBE_UTILITY.FORMAT_ERROR_STACK	Outputs detailed information about a stored procedure exception.
DBE_UTILITY.FORMAT_CALL_STACK	Outputs the call stack of a stored procedure.
DBE_UTILITY.GET_TIME	Outputs the current time, which is used to obtain the execution duration.
DBE_UTILITY.CANONICALIZE	Canonicalizes the character string of a table name.
DBE_UTILITY.COMMA_TO_TABLE	Converts a comma-delimited string of names into a PL/SQL table of names.
DBE_UTILITY.DB_VERSION	Returns the version number and compatibility version number of the database.
DBE_UTILITY.EXEC_DDL_STATEMENT	Executes DDL statements entered by users.
DBE_UTILITY.EXPAND_SQL_TEXT_PROC	Expands the view of the SQL query.
DBE_UTILITY.GET_CPU_TIME	Returns the measured value of the current CPU processing time.
DBE_UTILITY.GET_ENDIANNESS	Obtains the big-endian and little-endian information of the byte order on the platform where the database is located.
DBE_UTILITY.GET_HASH_VALUE	Returns the hash value of a given string.
DBE_UTILITY.GET_SQL_HASH	Outputs the hash value of a given string. This stored procedure is used when proc_outparam_override is not enabled.
DBE_UTILITY.IS_BIT_SET	Checks whether parameter n exists in r.
DBE_UTILITY.IS_CLUSTER_DATABASE	Determines whether the current database is running in database cluster mode.
DBE_UTILITY.NAME_RESOLVE	Parses the given object name, including synonym translation and necessary authorization checks.
DBE_UTILITY.NAME_TOKENIZE	Parses the name in the a [. b [. c ]][@ dblink ] format.
DBE_UTILITY.OLD_CURRENT_SCHEMA	Returns the name of the database schema in the current user environment.
DBE_UTILITY.OLD_CURRENT_USER	Returns the name of the current user.
DBE_UTILITY.TABLE_TO_COMMA	Converts a PL/SQL table of names into a comma-delimited string of names.
DBE_UTILITY.GET_SQL_HASH_FUNC	Equivalent to DBE_UTILITY.GET_SQL_HASH. This function is used when proc_outparam_override is enabled.
DBE_UTILITY.EXPAND_SQL_TEXT	This is an internal function and is not recommended.
DBE_UTILITY.CANONICALIZE_RET	This is an internal function and is not recommended.
DBE_UTILITY.COMMA_TO_TABLE_FUN	This is an internal function and is not recommended.
DBE_UTILITY.COMPILE_SCHEMA	This is an internal function and is not recommended.
DBE_UTILITY.NAME_SEPARATE	This is an internal function and is not recommended.
DBE_UTILITY.NAME_TOKENIZE_FUNC	This is an internal function and is not recommended.
DBE_UTILITY.NAME_TOKENIZE_LOWER	This is an internal function and is not recommended.
DBE_UTILITY.NAME_TOKENIZE_LOWER_FUNC	This is an internal function and is not recommended.
DBE_UTILITY.PRIVILEGE_CHECK	This is an internal function and is not recommended.
DBE_UTILITY.SEARCH_CLASS_WITH_NSPOID_ONAME_TYPE	This is an internal function and is not recommended.
DBE_UTILITY.SEARCH_OBJECTS	This is an internal function and is not recommended.
DBE_UTILITY.SEARCH_OBJECTS_SYNONYM_FILL_SECHEMA	This is an internal function and is not recommended.
DBE_UTILITY.SEARCH_PROCEDURE_WITH_NSPOID_ONAME	This is an internal function and is not recommended.
DBE_UTILITY.SEARCH_SYNONM_WITH_NSPOID_ONAME	This is an internal function and is not recommended.
DBE_UTILITY.TABLE_TO_COMMA_FUNC	This is an internal function and is not recommended.
DBE_UTILITY.USER_NAME	This is an internal function and is not recommended.

DBE_UTILITY.FORMAT_ERROR_BACKTRACE
Returns the call stack where an error occurs during execution. The prototype of the DBE_UTILITY.FORMAT_ERROR_BACKTRACE function is as follows:
1 2

DBE_UTILITY.FORMAT_ERROR_BACKTRACE() RETURN TEXT;

DBE_UTILITY.FORMAT_ERROR_STACK
Returns the detailed information about the error location when an error occurs during the execution. The prototype of the DBE_UTILITY.FORMAT_ERROR_STACK function is as follows:
1 2

DBE_UTILITY.FORMAT_ERROR_STACK() RETURN TEXT;

DBE_UTILITY.FORMAT_CALL_STACK
Sets the call stack of the output function. The prototype of the DBE_UTILITY.FORMAT_CALL_STACK function is as follows:
1 2

DBE_UTILITY.FORMAT_CALL_STACK() RETURN TEXT;

DBE_UTILITY.GET_TIME
Sets the output time, which is usually used for difference. A separate return value is meaningless. The prototype of the DBE_UTILITY.GET_TIME function is as follows:
1 2

DBE_UTILITY.GET_TIME() RETURN BIGINT;

DBE_UTILITY.CANONICALIZE

Canonicalizes the character string of a table name. The procedure handles a single reserved word or keyword, and removes white spaces for a single identifier so that "table" becomes TABLE. The prototype of the DBE_UTILITY.CANONICALIZE function is as follows:

     
          DBE_UTILITY.CANONICALIZE(
    name       IN   VARCHAR2,
    canon_name OUT  VARCHAR2,
    canon_len  IN   BINARY_INTEGER
);

**Table 2** DBE_UTILITY.CANONICALIZE parameters
Parameter	Description
name	Character string to be canonicalized.
canon_name	Canonicalized character string.
canon_len	Length of the string to be canonicalized, in bytes. If the value of this parameter is less than the actual length (in bytes) of the character string to be standardized, the character string is truncated by byte.

DBE_UTILITY.COMMA_TO_TABLE

Converts a comma-delimited string of names into a PL/SQL table of names. The prototype of the DBE_UTILITY.COMMA_TO_TABLE function is as follows:

     
          DBE_UTILITY.COMMA_TO_TABLE ( 
    list   IN  VARCHAR2,
    tablen OUT BINARY_INTEGER,
    tab    OUT uncl_array
);

**Table 3** DBE_UTILITY.COMMA_TO_TABLE parameters
Parameter	Description
list	A comma-delimited string of names.
tablen	Number of names in the PL/SQL table.
tab	PL/SQL table which contains the string of names.

DBE_UTILITY.DB_VERSION

Returns the version number and compatibility version number of the database. The prototype of the DBE_UTILITY.DB_VERSION function is as follows:

     
          DBE_UTILITY.DB_VERSION (
    version OUT VARCHAR2
);

**Table 4** DBE_UTILITY.DB_VERSION parameters
Parameter	Description
version	Output parameter, which indicates the internal database software version. The value is a character string.

DBE_UTILITY.EXEC_DDL_STATEMENT

Executes DDL statements entered by users. The prototype of the DBE_UTILITY.EXEC_DDL_STATEMENT function is as follows:

     
          DBE_UTILITY.EXEC_DDL_STATEMENT (
    parse_string IN VARCHAR2
);

**Table 5** DBE_UTILITY.EXEC_DDL_STATEMENT parameters
Parameter	Description
parse_string	DDL statements to be executed.

DBE_UTILITY.EXPAND_SQL_TEXT_PROC

Expands the view of the SQL query. It recursively expands the view objects in the view until a table is displayed. The function prototype of DBE_UTILITY.EXPAND_SQL_TEXT_PROC is as follows:

     
          DBE_UTILITY.EXPAND_SQL_TEXT_PROC (
    input_sql_text  IN           CLOB,
    output_sql_text OUT NOCOPY CLOB
);

**Table 6** DBE_UTILITY.EXPAND_SQL_TEXT_PROC parameters
Parameter	Description
input_sql_text	Input SQL text.
output_sql_text	Output SQL text of the expanded view.

In the input_sql_text parameter entered by a user, a schema prefix must be added to the object in the SQL statement. Otherwise, the function reports an error indicating that no object is found. If set behavior_compat_options is set to bind_procedure_searchpath, you do not need to forcibly specify the schema prefix.

DBE_UTILITY.GET_CPU_TIME
Returns the measured value of the current CPU processing time, in hundredths of a second. The prototype of the DBE_UTILITY.GET_CPU_TIME function is as follows:
1 2

DBE_UTILITY.GET_CPU_TIME() RETURN NUMBER;

DBE_UTILITY.GET_ENDIANNESS
Obtains the big-endian and little-endian information of the byte order on the platform where the database is located. DBE_UTILITY. The prototype of the GET_ENDIANNESS function is as follows:
1 2

DBE_UTILITY.GET_ENDIANNESS RETURN INTEGER;

DBE_UTILITY.GET_HASH_VALUE

Returns the hash value of a given string. The prototype of the DBE_UTILITY.GET_HASH_VALUE function is as follows:

     
          DBE_UTILITY.GET_HASH_VALUE(
    name      IN VARCHAR2(n),
    base      IN INTEGER,
    hash_size IN INTEGER)
RETURN INTEGER;

**Table 7** DBE_UTILITY.GET_HASH_VALUE parameters
Parameter	Description
name	Character string to be hashed.
base	Start value of the returned hash value.
hash_size	Size of the hash table to which the hash is mapped.

DBE_UTILITY.GET_SQL_HASH

Outputs the hash value of a given character string using the MD5 algorithm. The prototype of the DBE_UTILITY.GET_SQL_HASH function is as follows:

     
          DBE_UTILITY.GET_SQL_HASH(
    name       IN  VARCHAR2,
    hash       OUT RAW,
    last4bytes OUT BIGINT
);

**Table 8** DBE_UTILITY.GET_SQL_HASH parameters
Parameter	Description
name	Character string to be hashed.
hash	Complete hexadecimal MD5 hash value.
last4bytes	Last four bytes of the MD5 hash value, which is displayed as an unsigned integer.

After setting set behavior_compat_options to a non-proc_outparam_override parameter, call the DBE_UTILITY.GET_SQL_HASH function. If DBE_UTILITY.GET_SQL_HASH_FUNC is called, the value assignment fails.

DBE_UTILITY.IS_BIT_SET

Checks whether parameter n exists in r. DBE_UTILITY. The prototype of the IS_BIT_SET function is as follows:

     
          DBE_UTILITY.IS_BIT_SET (
    r IN RAW, 
    n IN INTEGER)
RETURN INTEGER;

**Table 9** DBE_UTILITY.IS_BIT_SET parameters
Parameter	Description
r	4 bytes plus the actual hexadecimal string.
n	Determines whether the value exists in the binary system.

DBE_UTILITY.IS_CLUSTER_DATABASE
Determines whether the current database is running in database cluster mode. The prototype of the DBE_UTILITY.IS_CLUSTER_DATABASE function is as follows:
1 2

DBE_UTILITY.IS_CLUSTER_DATABASE RETURN BOOLEAN;

DBE_UTILITY.NAME_RESOLVE

Parses the given object name, including synonym translation and necessary authorization checks. The prototype of the DBE_UTILITY.NAME_RESOLVE function is as follows:

     
          DBE_UTILITY.NAME_RESOLVE (
    name          IN   VARCHAR2,
    context       IN   INTEGER,
    schema        OUT VARCHAR2,
    part1         OUT VARCHAR2,
    part2         OUT VARCHAR2,
    dblink        OUT VARCHAR2,
    part1_type    OUT INTEGER,
    object_number OUT OID
);

**Table 10** DBE_UTILITY.NAME_RESOLVE parameters
Parameter	Description
name	Name of the object to be parsed. The structure is [[a.]b.]c[@d].
context	Start value of the returned hash value.
schema	Schema of an object.
part1	First part of the name. The type of this column is specified by part1_type.
part2	If this column is not empty, the value is the subprogram name.
dblink	Database link.
part1_type	Part 1 types: 5: synonym 7: procedure (top level) 8: function (top level)
object_number	Object ID. In database ORA, object_number is of the numeric type, indicating the object ID. In GaussDB, object_number is of the OID type and does not support implicit conversion from a number to an OID.

DBE_UTILITY.NAME_TOKENIZE

Parses names in the a [. b [. c ]][@ dblink ] format. If a name contains double quotation marks, the double quotation marks are deleted. Otherwise, the name becomes uppercase letters. The prototype of the DBE_UTILITY.NAME_TOKENIZE function is as follows:

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

**Table 11** DBE_UTILITY.NAME_TOKENIZE parameters
Parameter	Description
name	Name, consisting of SQL identifiers (for example, scott.foo@dblink).
a	First token of the name.
b	Second token of the name.
c	Third token of the name.
dblink	Database link.
nextpos	Next position of a parsed character string.

DBE_UTILITY.OLD_CURRENT_SCHEMA
Returns the name of the database schema in the current user environment. The prototype of the DBE_UTILITY.OLD_CURRENT_SCHEMA function is as follows:
1 2

DBE_UTILITY.OLD_CURRENT_SCHEMA() RETURN VARCHAR;

DBE_UTILITY.OLD_CURRENT_USER
Returns the name of the current user. The prototype of the DBE_UTILITY.OLD_CURRENT_USER function is as follows:
1 2

DBE_UTILITY.OLD_CURRENT_USER() RETURN VARCHAR2;

DBE_UTILITY.TABLE_TO_COMMA

Converts a PL/SQL table of names into a comma-delimited string of names. The prototype of the DBE_UTILITY.TABLE_TO_COMMA function is as follows:

     
          DBE_UTILITY.TABLE_TO_COMMA ( 
    tab    IN  UNCL_ARRAY, 
    tablen OUT BINARY_INTEGER,
    list   OUT VARCHAR2
);

**Table 12** DBE_UTILITY.TABLE_TO_COMMA parameters
Parameter	Description
tab	PL/SQL table which contains the string of names.
tablen	Number of names in the PL/SQL table.
list	A comma-delimited string of names.

DBE_UTILITY.GET_SQL_HASH_FUNC

Uses the MD5 algorithm to output the hash value of a given character string. The function prototype of DBE_UTILITY.GET_SQL_HASH_FUNC is:

      
           DBE_UTILITY.GET_SQL_HASH_FUNC(
    name       IN  VARCHAR2,
    hash       OUT RAW,
    last4bytes OUT BIGINT
);

**Table 13** DBE_UTILITY.GET_SQL_HASH_FUNC parameters
Parameter	Description
name	Character string to be hashed.
hash	Complete hexadecimal MD5 hash value.
last4bytes	Last four bytes of the MD5 hash value, which is displayed as an unsigned integer.

After setting set behavior_compat_options to 'proc_outparam_override', call the DBE_UTILITY.GET_SQL_HASH_FUNC function. If you call the DBE_UTILITY.GET_SQL_HASH function, a parameter mismatch error is reported.

Examples

    
     
       
       
         CREATE OR REPLACE PROCEDURE test_get_time1() 
AS
declare
    start_time  bigint;
    end_time  bigint;
BEGIN
    start_time:= dbe_utility.get_time ();
    pg_sleep(1);
    end_time:=dbe_utility.get_time ();
    dbe_output.print_line(end_time - start_time); 
END;
/
-- Canonicalize the character string of a table name.
declare
    cname varchar2(50);
begin
    dbe_utility.canonicalize('seg1', cname, 50);
    dbe_output.put_line(cname);
end;
/
-- Convert the input character string into an array of table names.
DECLARE
 tab_list VARCHAR2(100) := 't1,t2';
 len BINARY_INTEGER;
 tab DBMS_UTILITY.LNAME_ARRAY;
BEGIN
 dbe_output.put_line('table list is :' || tab_list);
 dbe_utility.comma_to_table(tab_list, len, tab);
END;
/
-- Check the version number and compatibility version number of the database.
declare
    v_version varchar2;
begin
    dbe_utility.db_version(v_version);
    v_version:=left(v_version, 8);
    dbe_output.print_line('version:' || v_version); 
end;
/
-- Check the measured value of the current CPU processing time.
DECLARE
    cputime NUMBER;
BEGIN
    cputime := dbe_utility.get_cpu_time;
    dbe_output.put_line('cpu time:' || cputime);
END;
/
-- Obtain the big-endian and little-endian information of the byte order on the platform where the database is located.
BEGIN
    dbe_output.PUT_LINE(dbe_utility.GET_ENDIANNESS);
END;
/
-- Obtain the hash value of a given string.
DECLARE
    result NUMBER(28);
BEGIN
    result := dbe_utility.get_hash_value('hello',10,10);
    dbe_output.put_line(result);
END;
/
-- Check whether the current database is in cluster mode.
DECLARE
    is_cluster BOOLEAN;
BEGIN
    is_cluster := dbe_utility.IS_CLUSTER_DATABASE;
    dbe_output.put_line('CLUSTER DATABASE: ' || CASE WHEN is_cluster THEN 'TRUE' ELSE 'FALSE' END);
END;
/
-- Obtain the name of the database schema in the current user environment.
DECLARE
    schm varchar2(100);
BEGIN
    schm := dbe_utility.old_current_schema;
    dbe_output.put_line('current schema: ' || schm);
END;
/
-- Obtain the current username.
select dbe_utility.old_current_user from sys_dummy;

        

      

    
   