PKG_UTIL

Table 1 lists all interfaces supported by the PKG_UTIL package.

• 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

-- 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;