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

PKG_UTIL

Table 1 lists all interfaces supported by the PKG_UTIL package.

Table 1 PKG_UTIL

Interface

Description

PKG_UTIL.LOB_GET_LENGTH

Obtains the length of a LOB.

PKG_UTIL.LOB_READ

Reads a part of a LOB.

PKG_UTIL.LOB_WRITE

Writes the source object to the target object in the specified format.

PKG_UTIL.LOB_APPEND

Appends the source LOB to the target LOB.

PKG_UTIL.LOB_COMPARE

Compares two LOBs based on the specified length.

PKG_UTIL.LOB_MATCH

Returns the position of the Nth occurrence of a character string in a LOB.

PKG_UTIL.LOB_RESET

Resets the character in specified position of a LOB to a specified character.

PKG_UTIL.LOB_GET_LENGTH

Obtains and returns the specified length of a LOB.

PKG_UTIL.LOB_READ_HUGE

Reads a part of the LOB content based on the specified length and initial position offset, and returns the read LOB and length.

PKG_UTIL.LOB_WRITEAPPEND_HUGE

Reads the content of a specified length from the source BLOB or CLOB, appends the content to the target BLOB or CLOB, and returns the target object.

PKG_UTIL.LOB_APPEND_HUGE

Appends the source BLOB or CLOB to the target BLOB/CLOB and returns the target object.

PKG_UTIL.READ_BFILE_TO_BLOB

Loads the source BFILE file to the target BLOB and returns the target object.

PKG_UTIL.LOB_COPY_HUGE

Reads the content of a specified length from the specified offset position of the source BLOB or CLOB, writes the content to the specified offset position of the target BLOB or CLOB, and returns the target object.

PKG_UTIL.BLOB_RESET

Sets a segment of data in a BLOB to the specified value and returns the processed BLOB and the actually processed length.

PKG_UTIL.CLOB_RESET

Sets a segment of data in a CLOB to spaces and returns the processed CLOB and the actually processed length.

PKG_UTIL.LOADBLOBFROMFILE

Reads the content of a specified length from the specified offset position of the source BFILE object, writes the content to the specified offset position of the target BLOB, and returns the target object, read position, and write position.

PKG_UTIL.LOADCLOBFROMFILE

Reads the content of a specified length from the specified offset position of the source BFILE object, writes the content to the specified offset position of the target CLOB, and returns the target object, read position, and write position.

PKG_UTIL.LOB_CONVERTTOBLOB_HUGE

Reads the content of a specified length from the specified offset position of the source CLOB, converts the content into a BLOB, and writes the BLOB to the specified position of target LOB. amout indicates the length to be converted.

PKG_UTIL.LOB_CONVERTTOCLOB_HUGE

Reads the content of a specified length from the specified offset position of the source CLOB, converts the content into a CLOB, and writes the CLOB to the specified position of target LOB. amout indicates the length to be converted.

PKG_UTIL.BFILE_GET_LENGTH

Obtains and returns the specified length of a BFILE file.

PKG_UTIL.BFILE_OPEN

Opens a BFILE file and returns its file descriptor.

PKG_UTIL.BFILE_CLOSE

Closes a BFILE file.

PKG_UTIL.LOB_WRITE_HUGE

Reads the specified length of the source object from the start position, writes the content to the specified offset position of the target LOB, overrides the original content, and returns the target LOB.

PKG_UTIL.IO_PRINT

Displays character strings.

PKG_UTIL.RAW_GET_LENGTH

Obtains the length of RAW data.

PKG_UTIL.RAW_CAST_FROM_VARCHAR2

Converts VARCHAR2 data to RAW data.

PKG_UTIL.RAW_CAST_FROM_BINARY_INTEGER

Converts binary integers to RAW data.

PKG_UTIL.RAW_CAST_TO_BINARY_INTEGER

Converts RAW data to binary integers.

PKG_UTIL.SET_RANDOM_SEED

Sets a random seed.

PKG_UTIL.RANDOM_GET_VALUE

Returns a random value.

PKG_UTIL.FILE_SET_DIRNAME

Sets the directory to be operated.

PKG_UTIL.FILE_OPEN

Opens a file based on the specified file name and directory.

PKG_UTIL.FILE_SET_MAX_LINE_SIZE

Sets the maximum length of a line to be written to a file.

PKG_UTIL.FILE_IS_CLOSE

Checks whether a file handle is closed.

PKG_UTIL.FILE_READ

Reads data of a specified length from an open file handle.

PKG_UTIL.FILE_READLINE

Reads a line of data from an open file handle.

PKG_UTIL.FILE_WRITE

Writes the data specified in the buffer to a file.

PKG_UTIL.FILE_WRITELINE

Writes the buffer to a file and adds newline characters.

PKG_UTIL.FILE_NEWLINE

Adds a line.

PKG_UTIL.FILE_READ_RAW

Reads binary data of a specified length from an open file handle.

PKG_UTIL.FILE_WRITE_RAW

Writes binary data to a file.

PKG_UTIL.FILE_FLUSH

Writes data from a file handle to a physical file.

PKG_UTIL.FILE_CLOSE

Closes an open file handle.

PKG_UTIL.FILE_REMOVE

Deletes a physical file. To do so, you must have the corresponding permission.

PKG_UTIL.FILE_RENAME

Renames a file on the disk, similar to mv in Unix.

PKG_UTIL.FILE_SIZE

Returns the size of a file.

PKG_UTIL.FILE_BLOCK_SIZE

Returns the number of blocks contained in a file.

PKG_UTIL.FILE_EXISTS

Checks whether a file exists.

PKG_UTIL.FILE_GETPOS

Specifies the offset of a returned file, in bytes.

PKG_UTIL.FILE_SEEK

Sets the offset for file position.

PKG_UTIL.FILE_CLOSE_ALL

Closes all file handles opened in a session.

PKG_UTIL.EXCEPTION_REPORT_ERROR

Throws an exception.

PKG_UTIL.RANDOM_SET_SEED

Sets a random seed.

pkg_util.app_read_client_info

Reads the client information.

pkg_util.app_set_client_info

Sets the client information.

pkg_util.lob_converttoblob

Converts the CLOB type to the BLOB type.

pkg_util.lob_converttoclob

Converts the BLOB type to the CLOB type.

pkg_util.lob_rawtotext

Converts the raw type to the text type.

pkg_util.lob_reset

Clears data of the LOB type.

pkg_util.lob_texttoraw

Converts the text type to the raw type.

pkg_util.lob_write

Writes data to the LOB type.

pkg_util.match_edit_distance_similarity

Calculates the difference between two character strings.

pkg_util.raw_cast_to_varchar2

Converts the raw type to the varchar2 type.

pkg_util.session_clear_context

Clears the attribute values in session_context.

pkg_util.session_search_context

Searches for an attribute value.

pkg_util.session_set_context

Sets an attribute value.

pkg_util.utility_format_call_stack

Displays the call stack of a stored procedure.

pkg_util.utility_format_error_backtrace

Displays the error stack of a stored procedure.

pkg_util.utility_format_error_stack

Displays the error information about a stored procedure.

pkg_util.utility_get_time

Displays the Unix timestamp of the system.

pkg_util.utility_compile_schema

Recompiles packages, functions and stored procedures under the specified schema. If an error is reported when compiling a PL/SQL object, the PL/SQL object is returned and the recompilation stops. This package has been discarded. pkg_util.gs_compile_schema is recommended.

pkg_util.gs_compile_schema

Recompiles packages, functions and stored procedures under the specified schema. If an error is reported when compiling a PL/SQL object, the exception is captured and the recompilation continues to compile other objects until all objects are compiled or the number of recompilation attempts reaches the upper limit.

When the advanced package is executed through JDBC, the SQLSTATE 00000 is displayed, which indicates that the operation is successful. For details, see "Standard SQL Error Codes" in Error Code Reference.

  • PKG_UTIL.LOB_GET_LENGTH

    Obtains the length of the input data.

    The prototype of the PKG_UTIL.LOB_GET_LENGTH function is as follows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    PKG_UTIL.LOB_GET_LENGTH(
    lob       IN   CLOB
    )
    RETURN INTEGER;
    
    PKG_UTIL.LOB_GET_LENGTH(
    lob       IN   BLOB
    )
    RETURN INTEGER;
    
    Table 2 PKG_UTIL.LOB_GET_LENGTH parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    clob/blob

    IN

    No

    Indicates the object whose length is to be obtained.

  • PKG_UTIL.LOB_READ

    Reads an object and returns the specified part.

    The prototype of the PKG_UTIL.LOB_READ function is as follows:
    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOB_READ(
    lob       IN   anyelement,
    len       IN   int,
    start     IN   int,
    mode      IN   int
    )
    RETURN ANYELEMENT
    
    Table 3 PKG_UTIL.LOB_READ parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    clob/blob

    IN

    No

    Specifies CLOB or BLOB data.

    len

    int

    IN

    No

    Specifies the length of the returned result.

    start

    int

    IN

    No

    Specifies the offset to the first character.

    mode

    int

    IN

    No

    Specifies the type of the read operation. 0 indicates READ, 1 indicates TRIM, and 2 indicates SUBSTR.

  • PKG_UTIL.LOB_WRITE

    Writes the source object to the target object based on the specified parameters and returns the target object.

    The prototype of the PKG_UTIL.LOB_WRITE function is as follows:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    PKG_UTIL.LOB_WRITE(
    dest_lob    INOUT   blob,
    src_lob     IN      raw
    len         IN      int,
    start_pos   IN      bigint
    )
    RETURN BLOB;
    PKG_UTIL.LOB_WRITE(
    dest_lob    INOUT   clob,
    src_lob     IN      varchar2
    len         IN      int,
    start_pos       IN      bigint
    )
    RETURN CLOB;
    
    Table 4 PKG_UTIL.LOB_WRITE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    clob/blob

    INOUT

    No

    Specifies the target object that data will be written to.

    src_lob

    clob/blob

    IN

    No

    Specifies the source object to be written.

    len

    int

    IN

    No

    Specifies the write length of the source object.

    start_pos

    bigint

    IN

    No

    Specifies the write start position of the target object.

  • PKG_UTIL.LOB_APPEND

    Appends the source object to the target BLOB/CLOB and returns the target BLOB/CLOB.

    The prototype of the PKG_UTIL.LOB_APPEND function is as follows:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    PKG_UTIL.LOB_APPEND(
    dest_lob    INOUT   blob,
    src_lob     IN      blob,
    len         IN      int default NULL
    )
    RETURN BLOB;
    
    PKG_UTIL.LOB_APPEND(
    dest_lob    INOUT   clob,
    src_lob     IN      clob,
    len         IN      int default NULL
    )
    RETURN CLOB;
    
    Table 5 PKG_UTIL.LOB_APPEND parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    blob/clob

    INOUT

    No

    Specifies the target BLOB/CLOB that data will be written to.

    src_lob

    blob/clob

    IN

    No

    Specifies the source BLOB/CLOB to be written.

    len

    int

    IN

    Yes

    Length read from src and appended to dest. The default value is null, indicating that all content of src is read and appended to dest.

  • PKG_UTIL.LOB_COMPARE

    Checks whether objects are the same based on the specified start position and size. If lob1 is larger, 1 is returned. If lob2 is larger, –1 is returned. If lob1 is equal to lob2, 0 is returned.

    The prototype of the PKG_UTIL.LOB_COMPARE function is as follows:
    1
    2
    3
    4
    5
    6
    7
    8
    PKG_UTIL.LOB_COMPARE(
    lob1        IN   anyelement,
    lob2        IN   anyelement,
    len         IN   int default 1073741771,
    start_pos1      IN   int default 1,
    start_pos2      IN   int default 1
    )
    RETURN INTEGER;
    
    Table 6 PKG_UTIL.LOB_COMPARE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob1

    clob/blob

    IN

    No

    Indicates the character string for comparison.

    lob2

    clob/blob

    IN

    No

    Indicates the character string for comparison.

    len

    int

    IN

    No

    Indicates the length to be compared.

    start_pos1

    int

    IN

    No

    Specifies the start offset of lob1.

    start_pos2

    int

    IN

    No

    Specifies the start offset of lob2.

  • PKG_UTIL.LOB_MATCH

    Returns the position where a pattern is displayed in a LOB for the match_nth time.

    The prototype of the PKG_UTIL.LOB_MATCH function is as follows:
    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOB_MATCH(
    lob          IN   anyelement,
    pattern      IN   anyelement,
    start        IN   int,
    match_nth    IN   int default 1
    )
    RETURN INTEGER;
    
    Table 7 PKG_UTIL.LOB_MATCH parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    clob/blob

    IN

    No

    Indicates the character string for comparison.

    pattern

    clob/blob

    IN

    No

    Specifies the pattern to be matched.

    start

    int

    IN

    No

    Specifies the start position for LOB comparison.

    match_nth

    int

    IN

    No

    Specifies the matching times.

  • PKG_UTIL.LOB_RESET

    Clears a character string and resets the string to the value of value.

    The prototype of the PKG_UTIL.LOB_RESET function is as follows:
    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOB_RESET(
    lob          INOUT   blob,
    len          INOUT   int,
    start        IN   int DEFAULT 1,
    value        IN   int default 0
    )
    RETURN record;
    
    Table 8 PKG_UTIL.LOB_RESET parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    blob

    IN

    No

    Indicates the character string for reset.

    len

    int

    IN

    No

    Specifies the length of the string to be reset.

    start

    int

    IN

    No

    Specifies the start position for reset.

    value

    int

    IN

    Yes

    Sets characters. Default value: '0'

  • PKG_UTIL.LOB_GET_LENGTH

    Obtains and returns the specified length of a LOB.

    The prototype of the PKG_UTIL.LOB_GET_LENGTH function is as follows:

    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOB_GET_LENGTH(
        lob IN blob)
    RETURN BIGINT;
    
    PKG_UTIL.LOB_GET_LENGTH(
        lob IN clob)
    RETURN BIGINT;
    
    Table 9 PKG_UTIL.LOB_GET_LENGTH parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    BLOB/CLOB

    IN

    No

    LOB type

    PKG_UTIL.LOB_READ_HUGE

    Reads a part of the LOB content based on the specified length and initial position offset, and returns the read LOB and length.

    The prototype of the PKG_UTIL.LOB_READ_HUGE function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    PKG_UTIL.LOB_READ_HUGE(
        lob       IN CLOB,
        len       IN BIGINT,
        start_pos IN BIGINT,
        mode      IN INTEGER)
    RETURN RECORD;
    
    PKG_UTIL.LOB_READ_HUGE(
        lob       IN BLOB,
        len       IN BIGINT,
        start_pos IN BIGINT,
        mode      IN INTEGER)
    RETURN RECORD;
    
    PKG_UTIL.LOB_READ_HUGE(
        fd        IN INTEGER,
        len       IN BIGINT,
        start_pos IN BIGINT,
        mode      IN INTEGER)
    RETURN RECORD;
    
    Table 10 PKG_UTIL.LOB_READ_HUGE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob/fd

    BLOB/CLOB/INTEGER

    IN

    No

    File descriptor of the specified LOB or BFILE file.

    len

    BIGINT

    IN

    No

    Length to read.

    start_pos

    BIGINT

    IN

    No

    Offset position from which read starts.

    mode

    INTEGER

    IN

    No

    Read mode (0: read, 1: trim, 2: substr).

  • PKG_UTIL.LOB_WRITEAPPEND_HUGE

    Reads the content of a specified length from the source BLOB or CLOB, appends the content to the target BLOB or CLOB, and returns the target object.

    The prototype of the PKG_UTIL.LOB_WRITEAPPEND_HUGE function is as follows:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    PKG_UTIL.LOB_WRITEAPPEND_HUGE(
        dest_lob INOUT CLOB,
        len      IN    INTEGER,
        src_lob  IN    VARCHAR2
    )RETURN CLOB;
    
    PKG_UTIL.LOB_WRITEAPPEND_HUGE(
        dest_lob INOUT BLOB,
        len      IN    INTEGER,
        src_lob  IN    RAW
    )RETURN BLOB;
    
    Table 11 PKG_UTIL.LOB_WRITEAPPEND_HUGE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    BLOB/CLOB

    INOUT

    No

    Target BLOB/CLOB to which data is written.

    len

    INTEGER

    IN

    Yes

    Length of the source object to be written. If the value is NULL, the entire source object is written by default.

    src_lob

    VARCHAR2/RAW

    IN

    No

    BLOB/CLOB from which data is to be written.

  • PKG_UTIL.LOB_APPEND_HUGE

    Appends the source BLOB or CLOB to the target BLOB/CLOB and returns the target object.

    The prototype of the PKG_UTIL.LOB_APPEND_HUGE function is as follows:
    1
    2
    3
    4
    PKG_UTIL.LOB_APPEND_HUGE(
        dest_lob INOUT BLOB,
        src_lob  IN    BLOB)
    RETURN BLOB;
    
    1
    2
    3
    4
    PKG_UTIL.LOB_APPEND_HUGE(
        dest_lob INOUT CLOB,
        src_lob  IN    CLOB)
    RETURN CLOB;
    
    Table 12 PKG_UTIL.LOB_APPEND_HUGE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    BLOB/CLOB

    INOUT

    No

    Target BLOB/CLOB to which data is written.

    src_lob

    BLOB/CLOB

    IN

    No

    BLOB/CLOB from which data is to be written.

  • PKG_UTIL.READ_BFILE_TO_BLOB

    Loads the source BFILE file to the target BLOB and returns the target object.

    The prototype of the PKG_UTIL.READ_BFILE_TO_BLOB function is as follows:
    1
    2
    3
    PKG_UTIL.READ_BFILE_TO_BLOB(
        fd IN INTEGER
    )RETURN BLOB;
    
    Table 13 PKG_UTIL.READ_BFILE_TO_BLOB parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    fd

    BFILE

    IN

    No

    Source BFILE file to be read.

  • PKG_UTIL.LOB_COPY_HUGE

    Reads the content of a specified length from the specified offset position of the source BLOB or CLOB, writes the content to the specified offset position of the target BLOB or CLOB, and returns the target object.

    The prototype of the PKG_UTIL.LOB_COPY_HUGE function is as follows:
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    PKG_UTIL.LOB_COPY_HUGE(
        lob_obj    INOUT BLOB,
        source_obj  IN    BLOB,
        amount      IN    BIGINT, 
        dest_offset IN    BIGINT DEFAULT 1, 
        src_offset  IN    BIGINT DEFAULT 1 
    )RETURN BLOB;
    
    PKG_UTIL.LOB_COPY_HUGE(
        lob_obj    INOUT CLOB,
        source_obj  IN    CLOB,
        amount      IN    BIGINT, 
        dest_offset IN    BIGINT DEFAULT 1, 
        src_offset  IN    BIGINT DEFAULT 1 
    )RETURN CLOB;
    
    Table 14 PKG_UTIL.LOB_COPY_HUGE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob_obj

    BLOB/CLOB

    INOUT

    No

    Target BLOB/CLOB.

    source_obj

    BLOB/CLOB

    IN

    No

    Source BLOB/CLOB.

    amount

    BIGINT

    IN

    No

    Length of the data to be copied (in bytes for BLOBs or in characters for CLOBs).

    dest_offset

    BIGINT

    IN

    No

    Offset position of the target LOB to which the data is loaded.

    src_offset

    BIGINT

    IN

    No

    Offset position of the source LOB from which the data is read.

  • PKG_UTIL.BLOB_RESET

    Sets a segment of data in a BLOB to the specified value and returns the processed BLOB and the actually processed length.

    The prototype of the PKG_UTIL.BLOB_RESET function is as follows:
    1
    2
    3
    4
    5
    6
    PKG_UTIL.BLOB_RESET(
        lob       INOUT BLOB,
        len       INOUT BIGINT,
        start_pos IN    BIGINT DEFAULT 1,
        value     IN    INTEGER DEFAULT 0
    )RETURN RECORD;
    
    Table 15 PKG_UTIL.BLOB_RESET parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    BLOB

    INOUT

    No

    LOB to be reset.

    len

    INTEGER

    INOUT

    No

    Length to reset, in bytes.

    start

    INTEGER

    IN

    No

    Specifies the start position for reset.

    value

    INTEGER

    IN

    Yes

    Sets characters. Default value: '0'.

  • PKG_UTIL.CLOB_RESET

    Sets a piece of data to spaces.

    The prototype of the PKG_UTIL.CLOB_RESET function is as follows:
    1
    2
    3
    4
    5
    PKG_UTIL.CLOB_RESET(
        lob       INOUT CLOB,
        len       INOUT BIGINT,
        start_pos IN    BIGINT DEFAULT 1
    )RETURN RECORD;
    
    Table 16 PKG_UTIL.CLOB_RESET parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    lob

    CLOB

    INOUT

    No

    LOB to be reset.

    len

    INTEGER

    INOUT

    No

    Length to reset, in characters.

    start

    INTEGER

    IN

    No

    Reset start position. The default value is 1.

  • PKG_UTIL.LOADBLOBFROMFILE

    Reads the content of a specified length from the specified offset position of the source BFILE object, writes the content to the specified offset position of the target BLOB, and returns the target object, read position, and write position.

    The prototype of the PKG_UTIL.LOADBLOBFROMFILE function is as follows:
    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOADBLOBFROMFILE(
        dest_lob     INOUT  BLOB,
        fd           IN     INTEGER,
        amount       IN     BIGINT,
        dest_offset  INOUT  BIGINT,
        file_offset  INOUT  BIGINT
    )RETURN RECORD;
    
    Table 17 PKG_UTIL.LOADBLOBFROMFILE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    BLOB

    INOUT

    No

    Target BLOB as the INOUT parameter.

    fd

    INTEGER

    IN

    No

    File descriptor of the source BFILE object.

    amount

    BIGINT

    IN

    No

    Length of the data to be copied (in bytes for BLOBs or in characters for CLOBs).

    dest_offset

    BIGINT

    INOUT

    No

    Offset position of the target LOB to which the data is written as the INOUT parameter.

    src_offset

    BIGINT

    INOUT

    No

    Offset position of the source BFILE file from which the data is read as the INOUT parameter.

  • PKG_UTIL.LOADCLOBFROMFILE

    Reads the content of a specified length from the specified offset position of the source BFILE object, writes the content to the specified offset position of the target CLOB, and returns the target object, read position, and write position.

    The prototype of the PKG_UTIL.LOADCLOBFROMFILE function is as follows:
    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOADCLOBFROMFILE(
        dest_lob     INOUT  CLOB,
        fd           IN     INTEGER,
        amount       IN     BIGINT,
        dest_offset  INOUT  BIGINT,
        file_offset  INOUT  BIGINT
    )RETURN RECORD;
    
    Table 18 PKG_UTIL.LOADCLOBFROMFILE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    CLOB

    INOUT

    No

    Target CLOB as the INOUT parameter.

    fd

    INTEGER

    IN

    No

    File descriptor of the source BFILE object.

    amount

    BIGINT

    IN

    No

    Length to copy (in characters for CLOBs).

    dest_offset

    BIGINT

    INOUT

    No

    Offset position of the target LOB to which the data is written as the INOUT parameter.

    src_offset

    BIGINT

    INOUT

    No

    Offset position of the source BFILE file from which the data is read as the INOUT parameter.

  • PKG_UTIL.LOB_CONVERTTOBLOB_HUGE

    Reads the content of a specified length from the specified offset position of the source CLOB, converts the content into a BLOB, and writes the BLOB to the specified position of target LOB. amout indicates the length to be converted.

    The prototype of the PKG_UTIL.LOB_CONVERTTOBLOB_HUGE function is as follows:

    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOB_CONVERTTOBLOB_HUGE(
        dest_lob    INOUT  BLOB,
        src_clob    IN     CLOB,
        amount      IN     BIGINT,
        dest_offset INOUT  BIGINT,
        src_offset  INOUT  BIGINT)
    )RETURN RECORD;
    
    Table 19 PKG_UTIL.LOB_CONVERTTOBLOB_HUGE parameters

    Parameter

    Description

    dest_lob

    Target LOB.

    src_clob

    CLOB to be converted.

    amount

    Length to convert, in characters.

    dest_offset

    Offset position of the target LOB to which the data is written as the INOUT parameter.

    src_offset

    Offset position of the source CLOB from which the data is read as the INOUT parameter.

  • PKG_UTIL.LOB_CONVERTTOCLOB_HUGE

    Reads the content of a specified length from the specified offset position of the source CLOB, converts the content into a CLOB, and writes the CLOB to the specified position of target LOB. amout indicates the length to be converted.

    The prototype of the PKG_UTIL.LOB_CONVERTTOCLOB_HUGE function is as follows:

    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.LOB_CONVERTTOCLOB_HUGE(
        dest_lob    INOUT  CLOB,
        src_blob    IN     BLOB,
        amount      IN     BIGINT,
        dest_offset INOUT  BIGINT,
        src_offset  INOUT  BIGINT)
    )RETURN RECORD;
    
    Table 20 PKG_UTIL.LOB_CONVERTTOCLOB_HUGE parameters

    Parameter

    Description

    dest_lob

    Target LOB.

    src_blob

    BLOB to be converted.

    amount

    Length to convert, in bytes.

    dest_offset

    Offset position of the target LOB to which the data is written as the INOUT parameter.

    src_offset

    Offset position of the source CLOB from which the data is read as the INOUT parameter.

  • PKG_UTIL.BFILE_GET_LENGTH

    Obtains and returns the specified length of a BFILE file.

    The prototype of the PKG_UTIL.BFILE_GET_LENGTH function is as follows:

    1
    2
    3
    PKG_UTIL.BFILE_GET_LENGTH(
        fd INTEGER
    )RETURN BIGINT;
    
    Table 21 PKG_UTIL.LOB_GET_LENGTH parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    fd

    INTEGER

    IN

    No

    File descriptor of the specified BFILE file.

  • PKG_UTIL.BFILE_OPEN

    Opens a BFILE file and returns its file descriptor.

    The prototype of the PKG_UTIL.BFILE_OPEN function is as follows:

    1
    2
    3
    4
    PKG_UTIL.BFILE_OPEN(
        file_name TEXT,
        open_mode TEXT)
    RETURN INTEGER;
    
    Table 22 PKG_UTIL.BFILE_OPEN parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    file_name

    TEXT

    IN

    No

    Name of the specified BFILE file.

    open_mode

    TEXT

    IN

    No

    Open mode, which can only be set to r, that is, the read mode.

  • PKG_UTIL.BFILE_CLOSE

    Closes a BFILE file.

    The prototype of the PKG_UTIL.BFILE_CLOSE function is as follows:

    1
    2
    3
    PKG_UTIL.BFILE_CLOSE(
        fd INTEGER)
    RETURN bool;
    
    Table 23 PKG_UTIL.BFILE_CLOSE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    fd

    INTEGER

    IN

    No

    File descriptor of the specified BFILE file.

  • PKG_UTIL.LOB_WRITE_HUGE

    Reads the specified length of the source object from the start position, writes the content to the specified offset position of the target LOB, overrides the original content, and returns the target LOB.

    The prototype of the PKG_UTIL.LOB_WRITE_HUGE function is as follows:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    PKG_UTIL.LOB_WRITE_HUGE(
        dest_lob  INOUT BLOB,
        len       IN INTEGER,
        start_pos IN BIGINT,
        src_lob   IN RAW
    )RETURN BLOB;
    
    PKG_UTIL.LOB_WRITE_HUGE(
        dest_lob  INOUT CLOB,
        len       IN INTEGER,
        start_pos IN BIGINT,
        src_lob   IN VARCHAR2
    )RETURN CLOB;
    
    Table 24 PKG_UTIL.LOB_WRITE_HUGE parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    dest_lob

    BLOB/CLOB

    INOUT

    No

    Target LOB as the INOUT parameter, to which the content is to be written.

    len

    INTEGER

    IN

    No

    Length of the data to be written (in bytes for BLOBs or in characters for CLOBs)

    start_pos

    BIGINT

    IN

    No

    Offset position for writing data to dest_lob

    src_lob

    RAW/VARCHAR2

    IN

    No

    Source LOB.

  • PKG_UTIL.IO_PRINT

    Outputs a string.

    The prototype of the PKG_UTIL.IO_PRINT function is as follows:
    1
    2
    3
    4
    5
    PKG_UTIL.IO_PRINT(
    format       IN   text,
    is_one_line  IN   boolean
    )
    RETURN void;
    
    Table 25 PKG_UTIL.IO_PRINT parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    format

    text

    IN

    No

    Indicates the character string to be output.

    is_one_line

    boolean

    IN

    No

    Indicates whether to output the string as a line.

  • PKG_UTIL.RAW_GET_LENGTH

    Obtains the length of RAW data.

    The prototype of the PKG_UTIL.RAW_GET_LENGTH function is as follows:
    1
    2
    3
    4
    PKG_UTIL.RAW_GET_LENGTH(
    value       IN   raw
    )
    RETURN integer;
    
    Table 26 PKG_UTIL.RAW_GET_LENGTH parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    raw

    raw

    IN

    No

    Indicates the object whose length is to be obtained.

  • PKG_UTIL.RAW_CAST_FROM_VARCHAR2

    Converts VARCHAR2 data to RAW data.

    The prototype of the PKG_UTIL.RAW_CAST_FROM_VARCHAR2 function is as follows:
    1
    2
    3
    4
    PKG_UTIL.RAW_CAST_FROM_VARCHAR2(
    str       IN   varchar2
    )
    RETURN raw;
    
    Table 27 PKG_UTIL.RAW_CAST_FROM_VARCHAR2 parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    str

    varchar2

    IN

    No

    Indicates the source data to be converted

  • PKG_UTIL.RANDOM_SET_SEED

    Sets a random seed.

    The prototype of the PKG_UTIL.RANDOM_SET_SEED function is as follows:
    1
    2
    3
    4
    PKG_UTIL.RANDOM_SET_SEED(
    seed         IN   int
    )
    RETURN integer;
    
    Table 28 PKG_UTIL.RANDOM_SET_SEED parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    seed

    int

    IN

    No

    Sets a random seed.

  • PKG_UTIL.RANDOM_GET_VALUE

    Returns a 15-digit random number ranging from 0 to 1.

    The prototype of the PKG_UTIL.RANDOM_GET_VALUE function is as follows:
    1
    2
    3
    PKG_UTIL.RANDOM_GET_VALUE(
    )
    RETURN numeric;
    
  • PKG_UTIL.FILE_SET_DIRNAME

    Sets the directory to be operated. It must be called to set directory for each operation involving a single directory.

    The prototype of the PKG_UTIL.FILE_SET_DIRNAME function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_SET_DIRNAME(
    dir  IN  text
    )
    RETURN bool
    
    Table 29 PKG_UTIL.FILE_SET_DIRNAME parameters

    Parameter

    Description

    dirname

    Directory of a file. It is a string, indicating an object name.

    NOTE:

    File directories need to be added to the system catalog PG_DIRECTORY. If the input path does not match the path in PG_DIRECTORY, an error indicating that the path does not exist will be reported. Functions that involve location as parameters also comply with this rule.

  • PKG_UTIL.FILE_OPEN

    Opens a file. A maximum of 50 files can be opened at a time. This function returns a handle of the INTEGER type.

    The prototype of the PKG_UTIL.FILE_OPEN function is as follows:

    1
    2
    3
    PKG_UTIL.FILE_OPEN(
    file_name    IN  text, 
    open_mode    IN  integer)
    
    Table 30 PKG_UTIL.FILE_OPEN parameters

    Parameter

    Description

    file_name

    File name with an extension (file type), excluding the path name. A path contained in a file name is ignored in the OPEN function. In Unix, the file name cannot end with the combination of a slash and a dot (/.).

    open_mode

    File opening mode, including r (read), w (write), and a (append).

    NOTE:

    For the write operation, the system checks the file type. If the ELF file is written, an error is reported and the system exits.

  • PKG_UTIL.FILE_SET_MAX_LINE_SIZE

    Sets the maximum length of a line to be written to a file.

    The prototype of the PKG_UTIL.FILE_SET_MAX_LINE_SIZE function is as follows:

    1
    2
    3
    PKG_UTIL.FILE_SET_MAX_LINE_SIZE(
    max_line_size in integer)
    RETURN BOOL
    
    Table 31 PKG_UTIL.FILE_SET_MAX_LINE_SIZE parameters

    Parameter

    Description

    max_line_size

    Maximum number of characters in each line, including newline characters. The minimum value is 1 and the maximum is 32767. If this parameter is not specified, the default value 1024 is used.

  • PKG_UTIL.FILE_IS_CLOSE

    Checks whether a file handle is closed.

    The prototype of the PKG_UTIL.FILE_IS_CLOSE function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_IS_CLOSE(
    file in integer
    )
    RETURN BOOL
    
    Table 32 PKG_UTIL.FILE_IS_CLOSE parameters

    Parameter

    Description

    file

    Opened file handle

  • PKG_UTIL.FILE_READ

    Reads a line of data from an open file handle based on the specified length.

    The prototype of the PKG_UTIL.FILE_READ function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_READ(
    file     IN   integer,
    buffer   OUT  text,
    len      IN   bigint default 1024)
    
    Table 33 PKG_UTIL.FILE_READ parameters

    Parameter

    Description

    file

    File handle opened by calling the OPEN function. The file must be opened in read mode. Otherwise, the INVALID_OPERATION exception is thrown.

    buffer

    Buffer used to receive data

    len

    Number of bytes read from a file

  • PKG_UTIL.FILE_READLINE

    Reads a line of data from an open file handle based on the specified length.

    The prototype of the PKG_UTIL.FILE_READLINE function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_READLINE(
    file    IN  integer,
    buffer  OUT text,
    len     IN  integer default NULL)
    
    Table 34 PKG_UTIL.FILE_READLINE parameters

    Parameter

    Description

    file

    File handle opened by calling the OPEN function. The file must be opened in read mode. Otherwise, the INVALID_OPERATION exception is thrown.

    buffer

    Buffer used to receive data

    len

    Number of bytes read from a file. The default value is NULL. If the default value NULL is used, max_line_size is used to specify the line size.

  • PKG_UTIL.FILE_WRITE

    Writes the data specified in the buffer to a file.

    The prototype of the PKG_UTIL.FILE_WRITE function is as follows:

    1
    2
    3
    4
    5
    PKG_UTIL.FILE_WRITE(
    file in integer,
    buffer in text
    )
    RETURN BOOL
    
    Table 35 PKG_UTIL.FILE_WRITE parameters

    Parameter

    Description

    file

    Opened file handle

    buffer

    Text data to be written to a file. The maximum buffer size is 32767 bytes. If no value is specified, the default value is 1024 bytes. Before the writing is performed, the buffer occupied by PUT operations cannot exceed 32767 bytes.

    NOTE:

    For the write operation, the system checks the file type. If the ELF file is written, an error is reported and the system exits.

  • PKG_UTIL.FILE_NEWLINE

    Writes a line terminator to an open file. The line terminator is related to the platform.

    The prototype of the PKG_UTIL.FILE_NEWLINE function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_NEWLINE(
    file in integer
    )
    RETURN BOOL
    
    Table 36 PKG_UTIL.FILE_NEWLINE parameters

    Parameter

    Description

    file

    Opened file handle

  • PKG_UTIL.FILE_WRITELINE

    Writes a line to a file.

    The prototype of the PKG_UTIL.FILE_WRITELINE function is as follows:

    1
    2
    3
    4
    5
    PKG_UTIL.FILE_WRITELINE(
    file in integer,
    buffer in text
    )
    RETURN BOOL
    
    Table 37 PKG_UTIL.FILE_WRITELINE parameters

    Parameter

    Description

    file

    Opened file handle.

    buffer

    Content to be written.

  • PKG_UTIL.FILE_READ_RAW

    Reads binary data of a specified length from an open file handle and returns the read binary data. The return type is RAW.

    The prototype of the PKG_UTIL.FILE_READ_RAW function is as follows:

    1
    2
    3
    4
    5
    PKG_UTIL.FILE_READ_RAW(
    file      in integer,
    length    in integer default NULL
    )
    RETURN raw
    
    Table 38 PKG_UTIL.FILE_READ_RAW parameters

    Parameter

    Description

    file

    Opened file handle

    length

    Length of the data to be read. The default value is NULL. By default, all data in the file is read. The maximum size is 1 GB.

  • PKG_UTIL.FILE_WRITE_RAW

    Writes the input binary object of the RAW type to an open file. If the insertion is successful, true is returned.

    The prototype of the PKG_UTIL.FILE_WRITE_RAW function is as follows:

    1
    2
    3
    4
    5
    PKG_UTIL.FILE_WRITE_RAW(
    file in integer,
    r    in raw
    )
    RETURN BOOL
    
    Table 39 PKG_UTIL.FILE_NEWLINE parameters

    Parameter

    Description

    file

    Opened file handle

    r

    Data to be written to the file

    NOTE:

    For the write operation, the system checks the file type. If the ELF file is written, an error is reported and the system exits.

  • PKG_UTIL.FILE_FLUSH

    Data in a file handle must be written into a physical file. Data in the buffer must have a line terminator. Refresh is important if a file must be read when it is opened. For example, debugging information can be refreshed to a file so that it can be read immediately.

    The prototype of the PKG_UTIL.FILE_FLUSH function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_FLUSH (
    file in integer
    )
    RETURN VOID
    
    Table 40 PKG_UTIL.FILE_FLUSH parameters

    Parameter

    Description

    file

    Opened file handle

  • PKG_UTIL.FILE_CLOSE

    Closes an open file handle.

    The prototype of the PKG_UTIL.FILE_CLOSE function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_CLOSE (
    file in integer
    )
    RETURN BOOL
    
    Table 41 PKG_UTIL.FILE_CLOSE parameters

    Parameter

    Description

    file

    Opened file handle

  • PKG_UTIL.FILE_REMOVE

    Deletes a disk file. To perform this operation, you must have required permissions.

    The prototype of the PKG_UTIL.FILE_REMOVE function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_REMOVE(
    file_name in text
    )
    RETURN VOID 
    
    Table 42 PKG_UTIL.FILE_REMOVE parameters

    Parameter

    Description

    filen_ame

    Name of the file to be deleted

  • PKG_UTIL.FILE_RENAME

    Renames a file on the disk, similar to mv in Unix.

    The prototype of the PKG_UTIL.FILE_RENAME function is as follows:

    1
    2
    3
    4
    5
    6
    PKG_UTIL.FILE_RENAME(
    src_dir in text, 
    src_file_name in text, 
    dest_dir in text, 
    dest_file_name in text, 
    overwrite boolean default false)
    
    Table 43 PKG_UTIL.FILE_RENAME parameters

    Parameter

    Description

    src_dir

    Source file directory (case-sensitive)

    NOTE:
    • Location of the file directory, which needs to be added to system catalog PG_DIRECTORY. If the input path does not match the path in PG_DIRECTORY, an error indicating that the path does not exist is reported.
    • When the GUC parameter safe_data_path is enabled, you can only use an advanced package to operate files in the file path specified by safe_data_path.

    src_file_name

    Source file name

    dest_dir

    Target file directory (case-sensitive)

    NOTE:
    • Location of the file directory, which needs to be added to system catalog PG_DIRECTORY. If the input path does not match the path in PG_DIRECTORY, an error indicating that the path does not exist is reported.
    • When the GUC parameter safe_data_path is enabled, you can only use an advanced package to operate files in the file path specified by safe_data_path.

    dest_file_name

    Target file name

    overwrite

    The default value is false. If a file with the same name exists in the destination directory, the file will not be rewritten.

  • PKG_UTIL.FILE_SIZE

    Returns the size of a specified file.

    The prototype of the PKG_UTIL.FILE_SIZE function is as follows:

    1
    2
    3
    bigint PKG_UTIL.FILE_SIZE(
    file_name in text
    )return bigint
    
    Table 44 PKG_UTIL.FILE_SIZE parameters

    Parameter

    Description

    file_name

    File name

  • PKG_UTIL.FILE_BLOCK_SIZE

    Returns the number of blocks contained in a specified file.

    The prototype of the PKG_UTIL.FILE_BLOCK_SIZE function is as follows:

    1
    2
    3
    bigint PKG_UTIL.FILE_BLOCK_SIZE(
    file_name in text
    )return bigint
    
    Table 45 PKG_UTIL.FILE_BLOCK_SIZE parameters

    Parameter

    Description

    file_name

    File name

  • PKG_UTIL.FILE_EXISTS

    Checks whether a file exists.

    The prototype of the PKG_UTIL.FILE_EXISTS function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_EXISTS(
    file_name in text
    )
    RETURN BOOL
    
    Table 46 PKG_UTIL.FILE_EXISTS parameters

    Parameter

    Description

    file_name

    File name

  • PKG_UTIL.FILE_GETPOS

    Specifies the offset of a returned file, in bytes.

    The prototype of the PKG_UTIL.FILE_GETPOS function is as follows:

    1
    2
    3
    4
    PKG_UTIL.FILE_GETPOS(
    file in integer 
    )
    RETURN BIGINT
    
    Table 47 PKG_UTIL.FILE_GETPOS parameters

    Parameter

    Description

    file

    Opened file handle

  • PKG_UTIL.FILE_SEEK

    Adjusts the position of a file pointer forward or backward based on the specified number of bytes.

    The prototype of the PKG_UTIL.FILE_SEEK function is as follows:

    1
    2
    3
    4
    5
    void PKG_UTIL.FILE_SEEK(
    file in integer,
    start in bigint
    )
    RETURN VOID
    
    Table 48 PKG_UTIL.FILE_SEEK parameters

    Parameter

    Description

    file

    Opened file handle

    start

    File offset, in bytes.

  • PKG_UTIL.FILE_CLOSE_ALL

    Closes all file handles opened in a session.

    The prototype of the PKG_UTIL.FILE_CLOSE_ALL function is as follows:

    PKG_UTIL.FILE_CLOSE_ALL(
    )
    RETURN VOID
    Table 49 PKG_UTIL.FILE_CLOSE_ALL parameters

    Parameter

    Description

    None

    None

  • PKG_UTIL.EXCEPTION_REPORT_ERROR

    Throws an exception.

    The prototype of the PKG_UTIL.EXCEPTION_REPORT_ERROR function is as follows:

    1
    2
    3
    4
    5
    6
    PKG_UTIL.EXCEPTION_REPORT_ERROR(
    code integer,
    log text,
    flag boolean DEFAULT false
    )
    RETURN INTEGER
    
    Table 50 PKG_UTIL.EXCEPTION_REPORT_ERROR parameters

    Parameter

    Description

    code

    Error code displayed when an exception occurs.

    log

    Log information displayed when an exception occurs.

    flag

    Reserved. The default value is false.

  • PKG_UTIL.app_read_client_info

    Reads the client information.

    The prototype of the PKG_UTIL.app_read_client_info function is as follows:

    1
    2
    3
    PKG_UTIL.app_read_client_info(
    OUT buffer text
    )return text
    
    Table 51 PKG_UTIL.app_read_client_info parameters

    Parameter

    Description

    buffer

    Client information returned.

  • PKG_UTIL.app_set_client_info

    Sets the client information.

    The prototype of the PKG_UTIL.app_set_client_info function is as follows:

    1
    2
    3
    PKG_UTIL.app_set_client_info(
    str text
    )
    
    Table 52 PKG_UTIL.app_set_client_info parameters

    Parameter

    Description

    str

    Client information to be set.

  • PKG_UTIL.lob_converttoblob

    Converts a CLOB to a BLOB. amout indicates the conversion length.

    The prototype of the PKG_UTIL.lob_converttoblob function is as follows:

    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.lob_converttoblob(
    dest_lob blob, 
    src_clob clob, 
    amount integer, 
    dest_offset integer, 
    src_offset integer
    )return raw
    
    Table 53 PKG_UTIL.lob_converttoblob parameters

    Parameter

    Description

    dest_lob

    Target LOB.

    src_clob

    CLOB to be converted.

    amount

    Conversion length.

    dest_offset

    Start position of the target LOB.

    src_offset

    Start position of the source CLOB.

  • PKG_UTIL.lob_converttoclob

    Converts a BLOB to a CLOB. amout indicates the conversion length.

    The prototype of the PKG_UTIL.lob_converttoclob function is as follows:

    1
    2
    3
    4
    5
    6
    7
    PKG_UTIL.lob_converttoclob(
    dest_lob clob, 
    src_blob blob, 
    amount integer, 
    dest_offset integer, 
    src_offset integer
    )return text
    
    Table 54 PKG_UTIL.lob_converttoclob parameters

    Parameter

    Description

    dest_lob

    Target LOB.

    src_blob

    BLOB to be converted.

    amount

    Conversion length.

    dest_offset

    Start position of the target LOB.

    src_offset

    Start position of the source CLOB.

  • PKG_UTIL.lob_texttoraw

    Converts from the text type to the raw type.

    The prototype of the PKG_UTIL.lob_texttoraw function is as follows:

    1
    2
    3
    4
    PKG_UTIL.lob_texttoraw(
    src_lob clob
    )
    RETURN raw
    
    Table 55 PKG_UTIL.lob_texttoraw parameters

    Parameter

    Description

    src_lob

    LOB to be converted.

  • PKG_UTIL.match_edit_distance_similarity

    Calculates the difference between two character strings.

    The prototype of the PKG_UTIL.match_edit_distance_similarity function is as follows:

    1
    2
    3
    4
    5
    PKG_UTIL.match_edit_distance_similarity(
    str1 text, 
    str2 text
    )
    RETURN INTEGER
    
    Table 56 PKG_UTIL.match_edit_distance_similarity parameters

    Parameter

    Description

    str1

    First character string.

    str2

    Second character string.

  • PKG_UTIL.raw_cast_to_varchar2

    Converts from the raw type to the varchar2 type.

    The prototype of the PKG_UTIL.raw_cast_to_varchar2 function is as follows:

    1
    2
    3
    4
    PKG_UTIL.raw_cast_to_varchar2(
    str raw 
    )
    RETURN varchar2
    
    Table 57 PKG_UTIL.raw_cast_to_varchar2 parameters

    Parameter

    Description

    str

    Hexadecimal string

  • PKG_UTIL.session_clear_context

    Clears the session context.

    The prototype of the PKG_UTIL.session_clear_context function is as follows:

    1
    2
    3
    4
    5
    6
    PKG_UTIL.session_clear_context(
    namespace text, 
    client_identifier text, 
    attribute text
    )
    RETURN INTEGER
    
    Table 58 PKG_UTIL.session_clear_context parameters

    Parameter

    Description

    namespace

    Namespace of an attribute.

    client_identifier

    Usually the value of this parameter is the same as that of namespace. If this parameter is set to null, all namespaces are modified by default.

    attribute

    Attribute to be cleared.

  • PKG_UTIL.session_search_context

    Searches for an attribute value.

    The prototype of the PKG_UTIL.session_clear_context function is as follows:

    1
    2
    3
    4
    5
    PKG_UTIL.session_clear_context(
    namespace text, 
    attribute text
    )
    RETURN INTEGER
    
    Table 59 PKG_UTIL.session_clear_context parameters

    Parameter

    Description

    namespace

    Namespace of an attribute.

    attribute

    Attribute to be cleared.

  • PKG_UTIL.session_set_context

    Sets the attribute value.

    The prototype of the PKG_UTIL.session_set_context function is as follows:

    1
    2
    3
    4
    5
    6
    PKG_UTIL.session_set_context(
    namespace text, 
    attribute text,
    value text
    )
    RETURN INTEGER
    
    Table 60 PKG_UTIL.session_set_context parameters

    Parameter

    Description

    namespace

    Namespace of an attribute.

    attribute

    Attribute to be set.

    value

    Attribute value.

  • PKG_UTIL.utility_get_time

    Prints the Unix timestamp.

    The prototype of the PKG_UTIL.utility_get_time function is as follows:

    1
    2
    PKG_UTIL.utility_get_time()
    RETURN bigint
    
  • PKG_UTIL.utility_format_error_backtrace

    Displays the error stack of a stored procedure.

    The prototype of the PKG_UTIL.utility_format_error_backtrace function is as follows:

    1
    2
    PKG_UTIL.utility_format_error_backtrace()
    RETURN text
    
  • PKG_UTIL.utility_format_error_stack

    Displays the error information of a stored procedure.

    The prototype of the PKG_UTIL.utility_format_error_stack function is as follows:

    1
    2
    PKG_UTIL.utility_format_error_stack()
    RETURN text
    
  • PKG_UTIL.utility_compile_schema

    Recompiles packages, functions and stored procedures under the specified schema.

    The prototype of the PKG_UTIL.utility_compile_schema function is as follows:

    pkg_util.utility_compile_schema (
    schema in varchar2,
    compile_all in boolean default true,
    reuse_settings in boolean default false
    )
    returns void
  • PKG_UTIL.gs_compile_schema

    Recompiles packages, functions and stored procedures under the specified schema.

    The prototype of the PKG_UTIL.gs_compile_schema stored procedure is as follows:

    pkg_util.gs_compile_schema (
    schema_name in varchar2 default null,
    compile_all in boolean default false,
    retry_times in int default 10
    )
    Table 61 PKG_UTIL.gs_compile_schema parameters

    Parameter

    Description

    schema_name

    Specifies the name of the namespace.

    compile_all

    Specifies to compile all objects.

    • false: Compile the packages, functions, and stored procedures whose status is false in the pg_object table.
    • true: Compile all packages, functions, and stored procedures in the pg_object table.

    retry_times

    Specifies the number of retry times.

  • PKG_UTIL.utility_format_call_stack

    Displays the call stack of a stored procedure.

    The prototype of the PKG_UTIL.utility_format_call_stack function is as follows:

    1
    2
    PKG_UTIL.utility_format_call_stack()
    RETURN text
    

Examples

-- Delete a schema.
drop schema if exists pkg_var_test cascade;
-- Create a schema named pkg_var_test.
create schema pkg_var_test;
-- Set the schema and parameters.
set current_schema = pkg_var_test;
set behavior_compat_options ='plpgsql_dependency';
-- Create a package.
create or replace package test_pkg as
    referenced_var int;
    unreferenced_var int;
end test_pkg;
/
-- Create a function.
create or replace function test_func return int
is
begin
  return 1;
end;
/
-- Create a stored procedure.
create or replace procedure test_proc
is
  proc_var int;
begin
  proc_var := 1;
end;
/
-- Recompile package functions and stored procedures under pkg_var_test.
call pkg_util.utility_compile_schema('pkg_var_test');
or
call pkg_util.gs_compile_schema(' ',false,2);
-- Delete pkg_var_test.
drop schema if exists pkg_var_test cascade;