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

DBE_UTILITY

Interface Description

Table 1 provides all interfaces supported by the DBE_UTILITY package.

Table 1 DBE_UTILITY

Interface

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

Recompiles the PL/SQL packages and functions (except the packages and functions provided by the system) under a specified schema. This package has been discarded. pkg_util.gs_compile_schema is recommended.

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

    Recompiles the PL/SQL packages and functions (except the packages and functions provided by the system) under a specified schema. The prototype of the DBE_UTILITY.COMPILE_SCHEMA function is as follows:

    DBE_UTILITY.COMPILE_SCHEMA (
    SCHEMA IN VARCHAR2,
    COMPILE_ALL IN BOOLEAN DEFAULT TRUE,
    REUSE_SETTINGS IN BOOLEAN DEFAULT FALSE
    )
    RETURNS VOID;
    For details about the example, see the usage of the pkg_util.utility_compile_schema function in 11.12.1.2. To call the schema, run the following command:
    call DBE_UTILITY.compile_schema('pkg_var_test');
  • 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:
    1
    2
    3
    4
    5
    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:
    1
    2
    3
    4
    5
    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:
    1
    2
    3
    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. DBE_UTILITY. The prototype of the EXEC_DDL_STATEMENT function is as follows:
    1
    2
    3
    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:
    1
    2
    3
    4
    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. The prototype of the DBE_UTILITY.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:
    1
    2
    3
    4
    5
    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:
    1
    2
    3
    4
    5
    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 value other than proc_outparam_override, 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. The prototype of the DBE_UTILITY.IS_BIT_SET function is as follows:
    1
    2
    3
    4
    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:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    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)
    • 9: package

    object_number

    Object ID. In database A, 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:
    1
    2
    3
    4
    5
    6
    7
    8
    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:
    1
    2
    3
    4
    5
    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:
    1
    2
    3
    4
    5
    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', invoke the DBE_UTILITY.GET_SQL_HASH_FUNC function. If you invoke the DBE_UTILITY.GET_SQL_HASH function, a parameter mismatch error is reported.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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;