On this page

Show all

Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-2.x)/ Stored Procedures/ Advanced Packages/ Secondary Encapsulation Interfaces (Recommended)/ DBE_FILE

DBE_FILE

Updated on 2025-02-27 GMT+08:00

View PDF

DBE_FILE provides the capabilities of reading and writing OS text files for stored procedures.

API Description

Table 1 lists all APIs supported by the DBE_FILE package.

**Table 1** DBE_FILE
API	Description
DBE_FILE.OPEN	Opens a file based on the specified directory and file name.
DBE_FILE.IS_CLOSE	Checks whether a file handle is closed.
DBE_FILE.IS_OPEN	Checks whether a file handle is opened.
DBE_FILE.READ_LINE	Reads a line of a specified length from an open file handle.
DBE_FILE.WRITE	Writes data to the buffer of an open file.
DBE_FILE.NEW_LINE	Writes one or more line terminators to the buffer of an open file.
DBE_FILE.WRITE_LINE	Writes data to the buffer of an open file and automatically appends a line terminator.
DBE_FILE.FORMAT_WRITE	Writes data in a specified format to the buffer of an open file.
DBE_FILE.GET_RAW	Reads RAW data of a specified number of bytes from an open file.
DBE_FILE.PUT_RAW	Writes raw data to the buffer of an open file.
DBE_FILE.FLUSH	Writes data in the buffer to a physical file.
DBE_FILE.CLOSE	Closes an open file handle.
DBE_FILE.CLOSE_ALL	Closes all file handles opened in a session.
DBE_FILE.REMOVE	Deletes a disk file based on the specified directory and file name. You must have sufficient permissions to perform this operation.
DBE_FILE.RENAME	Renames a disk file, which is similar to the mv command of Unix.
DBE_FILE.COPY	Copies data in a continuous area to a new file. If start_line and end_line are omitted, the entire file is copied.
DBE_FILE.GET_ATTR	Reads and returns the attributes of a disk file.
DBE_FILE.SEEK	Adjusts the position of a file pointer forward or backward based on the specified number of bytes.
DBE_FILE.GET_POS	Returns the current offset of the file, in bytes.

DBE_FILE.OPEN

This function opens a file. You can specify the maximum number of characters in each line. A maximum of 50 files can be opened at a time. This function returns a handle of the INTEGER type.

The prototype of the DBE_FILE.OPEN function is as follows:

      
         DBE_FILE.OPEN (
dir             IN    VARCHAR2,
file_name       IN    VARCHAR2,
open_mode       IN    VARCHAR2,
max_line_size   IN    INTEGER DEFAULT 1024)
RETURN INTEGER;

**Table 2** DBE_FILE.OPEN API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
dir	VARCHAR2	IN	No	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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
file_name	VARCHAR2	IN	No	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	VARCHAR2	IN	No	File opening mode, including r (read text), w (write text), and a (append text). 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.
max_line_size	INTEGER	IN	Yes	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.

DBE_FILE.IS_OPEN

Checks whether a file is opened. A Boolean value is returned. If an invalid file handle is detected, the INVALID_FILEHANDLE exception is thrown.

The prototype of the DBE_FILE.IS_OPEN function is as follows:

      
         DBE_FILE.IS_OPEN(
    file IN INTEGER)
RETURN BOOLEAN;

**Table 3** DBE_FILE.IS_OPEN API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	Yes	Handle of the file to be checked. If the handle is empty, DBE_FILE.IS_OPEN returns FALSE.

DBE_FILE.IS_CLOSE

Checks whether a file handle is closed. A Boolean value is returned. If an invalid file handle is detected, the INVALID_FILEHANDLE exception is thrown.

The prototype of the DBE_FILE.IS_CLOSE function is as follows:

      
         DBE_FILE.IS_CLOSE (
file      IN     INTEGER)
RETURN BOOLEAN;

**Table 4** DBE_FILE.IS_CLOSE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	Yes	File handle to be detected.

DBE_FILE.READ_LINE

Reads data from an open file and stores the read result to the buffer. It reads data to the end of each line excluding the line terminator, to the end of the file, or to the size specified by the len parameter. The length of the data to be read cannot exceed the value of max_line_size specified when the file is opened.

The prototype of the DBE_FILE.READ_LINE function is as follows:

      
         DBE_FILE.READ_LINE (
file      IN   INTEGER,
buffer    OUT  VARCHAR2,
len       IN   INTEGER DEFAULT NULL)

**Table 5** DBE_FILE.READ_LINE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened by calling the OPEN function. The file must be opened in read mode. Otherwise, the INVALID_OPERATION exception is thrown.
buffer	VARCHAR2	OUT	No	Buffer used to receive data.
len	INTEGE	IN	Yes	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.

DBE_FILE.WRITE

Writes buffer data to the buffer corresponding to a file. The file must be opened in write mode. This operation does not write a line terminator.

The prototype of the DBE_FILE.WRITE function is as follows:

      
         DBE_FILE.WRITE (
file     IN     INTEGER,
buffer   IN     TEXT)
RETURN BOOLEAN;

**Table 6** DBE_FILE.WRITE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN. The file must be opened in write mode. This operation does not write a line terminator.
buffer	TEXT	IN	Yes	Text data to be written to a file. The maximum buffer size is 32767 bytes. If no value is specified in the open state, the default value is 1024 bytes. Before the writing is performed, the buffer occupied by WRITE 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.

DBE_FILE.NEW_LINE

Writes one or more line terminators to the buffer corresponding to a file. The line terminators are related to the platform used.

The prototype of the DBE_FILE.NEW_LINE function is as follows:

      
         DBE_FILE.NEW_LINE (
file         IN     INTEGER,
line_nums    IN     INTEGER := 1)
RETURN BOOLEAN;

**Table 7** DBE_FILE.NEW_LINE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.
line_nums	INTEGER	IN	Yes	Number of terminators written to a file. The default value is 1.

DBE_FILE.WRITE_LINE

Writes buffer data to the buffer corresponding to a file. The file must be opened in write mode. This operation automatically adds a line terminator.

The prototype of the DBE_FILE.WRITE_LINE function is as follows:

      
         DBE_FILE.WRITE_LINE(
file       IN     INTEGER,
buffer     IN     TEXT,
flush      IN     BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN;

**Table 8** DBE_FILE.WRITE_LINE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.
buffer	TEXT	IN	Yes	Text data to be written to a file. The maximum buffer size is 32767 bytes. If no value is specified during the open state, 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.
flush	BOOLEAN	IN	Yes	Specifies whether to flush data to the disk after the writing.

DBE_FILE.FORMAT_WRITE

Writes formatted data to the buffer corresponding to an open file. It is a DBE_FILE.WRITE API that allows formatting.

The prototype of the DBE_FILE.FORMAT_WRITE function is as follows:

      
         DBE_FILE.FORMAT_WRITE (
file   IN INTEGER,
format IN VARCHAR2,
arg1  IN VARCHAR2 DEFAULT NULL,
. . .
arg6 IN VARCHAR2 DEFAULT NULL])
RETURN BOOLEAN;

**Table 9** DBE_FILE.FORMAT_WRITE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.
format	TEXT	IN	Yes	A string to be formatted, containing the text and format characters \n and %s.
[arg1. . .arg6]	TEXT	IN	Yes	Six optional parameters. The parameters and the positions of characters to be formatted are in one-to-one correspondence. If the parameter corresponding to a character to be formatted is not provided, an empty string is used to replace %s.

DBE_FILE.GET_RAW

This function reads binary data from the opened file descriptor and returns the data using r.

The prototype of the DBE_FILE.GET_RAW function is as follows:

      
         DBE_FILE.GET_RAW (
file    IN   INTEGER,
r       OUT  RAW,
length  IN   INTEGER DEFAULT NULL)
RETURN RAW;

**Table 10** DBE_FILE.GET_RAW API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.
r	RAW	OUT	No	Output binary data.
length	INTEGER	IN	Yes	Length of the file to be read. The default value is NULL. All data in the file is read. The maximum length is 1 GB.

DBE_FILE.PUT_RAW

Writes raw data to the buffer corresponding to a file.

The prototype of the DBE_FILE.PUT_RAW function is as follows:

      
         DBE_FILE.PUT_RAW (
file    IN  INTEGER,
r       IN  RAW,
flush   IN  BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN;

**Table 11** DBE_FILE.PUT_RAW API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.
r	RAW	IN	No	Output binary data. 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.
flush	BOOLEAN	IN	Yes	Specifies whether to flush data to a file. The default value is false.

DBE_FILE.FLUSH

Writes data in the buffer to a physical file. The buffer data must have a line terminator. This function can write the data in the buffer to the corresponding physical file in time.

The prototype of the DBE_FILE.FLUSH function is as follows:

      
         DBE_FILE.FLUSH (
file     IN     INTEGER)
RETURN VOID;

**Table 12** DBE_FILE.FLUSH API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	Opened file handle

DBE_FILE.CLOSE

Closes an open file. When this function is called, if there is cached data to be written, exception information may be received.

The prototype of the DBE_FILE.CLOSE function is as follows:

      
         DBE_FILE.CLOSE (
file IN INTEGER
)RETURN INTEGER;

**Table 13** DBE_FILE.CLOSE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.

DBE_FILE.CLOSE_ALL

Closes all file handles opened in a session. This function can be used for emergency cleanup.

The prototype of the DBE_FILE.CLOSE_ALL function is as follows:

      
         DBE_FILE.CLOSE_ALL()
RETRUN VOID;

**Table 14** DBE_FILE.CLOSE_ALL API parameters
Parameter	Description
None.	None.

DBE_FILE.REMOVE

Deletes a disk file. To use this function, you must have the required permission.

The prototype of the DBE_FILE.REMOVE function is as follows:

      
         DBE_FILE.REMOVE (
dir           IN     VARCHAR2,
file_name     IN     VARCHAR2)
RETURN VOID;

**Table 15** DBE_FILE.REMOVE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
dir	VARCHAR2	IN	No	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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
file_name	VARCHAR2	IN	No	File name.

DBE_FILE.RENAME

Renames a disk file. This function is similar to the mv command of Unix.

The prototype of the DBE_FILE.RENAME function is as follows:

      
         DBE_FILE.RENAME (
src_dir        IN   VARCHAR2,
src_file_name  IN   VARCHAR2,
dest_dir       IN   VARCHAR2,
dest_file_name IN   VARCHAR2,
overwrite      IN   BOOLEAN DEFAULT FALSE)
RETURN VOID;

**Table 16** DBE_FILE.RENAME API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
src_dir	VARCHAR2	IN	No	Directory of the source file (case-sensitive). 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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
src_file_name	VARCHAR2	IN	No	Source file to be renamed.
dest_dir	VARCHAR2	IN	No	Target directory (case-sensitive). 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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
dest_file_name	VARCHAR2	IN	No	New file name.
overwrite	BOOLEAN	IN	Yes	Specifies whether to overwrite the file. If the parameter is left empty or not specified, the parameter is not overwritten. If no rewriting is performed and a file with the same name already exists in the destination directory, an error is reported.

DBE_FILE.COPY

Copies data in a continuous area to a new file. If start_line and end_line are omitted, the entire file is copied.

The prototype of the DBE_FILE.COPY function is as follows:

      
         DBE_FILE.COPY (
src_dir           IN     VARCHAR2,
src_file_name     IN     VARCHAR2,
dest_dir          IN     VARCHAR2,
dest_file_name    IN     VARCHAR2,
start_line        IN     INTEGER DEFAULT 1,
end_line          IN     INTEGER DEFAULT NULL)
RETURN VOID;

**Table 17** DBE_FILE.COPY API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
src_dir	VARCHAR2	IN	No	Directory of the source file. 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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
src_file_name	VARCHAR2	IN	No	File to be copied.
dest_dir	VARCHAR2	IN	No	Directory of the destination file. 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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
dest_file_name	VARCHAR2	IN	No	Target 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.
start_line	INTEGER	IN	No	Number of the line where the copy starts. The default value is 1.
end_line	INTEGER	IN	Yes	Number of the line where the copy ends. The default value is NULL, indicating the end of the file.

DBE_FILE.GET_ATTR

Reads and returns the attributes of a disk file.

The prototype of the DBE_FILE.GET_ATTR function is as follows:

      
         DBE_FILE.GET_ATTR(
location IN text,
filename IN text,
OUT fexists boolean,
OUT file_length bigint,
OUT block_size integer);

**Table 18** DBE_FILE.GET_ATTR API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
location	TEXT	IN	No	File directory. 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. When the GUC parameter safe_data_path is enabled, you can only use an advanced package to read and write files in the file path specified by safe_data_path.
filename	TEXT	IN	No	File name.
fexists	BOOLEAN	OUT	No	Specifies whether the file exists.
file_length	BIGINT	OUT	No	File length (unit: byte). If the file does not exist, NULL is returned.
block_size	INTEGER	OUT	No	Block size of the file system (unit: byte). If the file does not exist, NULL is returned.

DBE_FILE.SEEK

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

The prototype of the DBE_FILE.SEEK function is as follows:

      
         DBE_FILE.SEEK (
file            IN INTEGER,
absolute_start  IN     BIGINT DEFAULT NULL,
relative_start  IN     BIGINT DEFAULT NULL)
RETURN VOID;

**Table 19** DBE_FILE.SEEK API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.
absolute_start	BIGINT	IN	Yes	Absolute offset of a file. The default value is NULL.
relative_start	BIGINT	IN	Yes	Relative offset of a file. A positive number indicates forward offset and a negative number indicates backward offset. The default value is NULL. If both absolute_start and this parameter are specified, the absolute_start parameter is used.

DBE_FILE.GET_POS

Returns the current offset of the file, in bytes.

The prototype of the DBE_FILE.FGETPOS function is as follows:

      
         DBE_FILE.GET_POS (
file    IN     INTEGER)
RETURN BIGINT;

**Table 20** DBE_FILE.GET_POS API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
file	INTEGER	IN	No	File handle opened using OPEN.

Examples

    
     
       
       -- Add the /temp/ directory to the PG_DIRECTORY system catalog as a SYSADMIN.
CREATE OR REPLACE DIRECTORY dir AS '/tmp/';
-- Expected result:
CREATE DIRECTORY

-- Open a file and write data into the file.
DECLARE
  f integer;
  dir text := 'dir';
BEGIN
  f := dbe_file.open(dir, 'sample.txt', 'w');
  PERFORM dbe_file.write_line(f, 'ABC');
  PERFORM dbe_file.write_line(f, '123'::numeric);
  PERFORM dbe_file.write_line(f, '-----');
  PERFORM dbe_file.new_line(f);
  PERFORM dbe_file.write_line(f, '*******');
  PERFORM dbe_file.new_line(f, 0);
  PERFORM dbe_file.write_line(f, '++++++++');
  PERFORM dbe_file.new_line(f, 2);
  PERFORM dbe_file.write_line(f, '#######');
  PERFORM dbe_file.write(f, 'A');
  PERFORM dbe_file.write(f, 'B');
  PERFORM dbe_file.new_line(f);
  PERFORM dbe_file.format_write(f, '[1 -> %s, 2 -> %s, 3 -> %s, 4 -> %s, 5 -> %s]', 'gaussdb', 'dbe', 'file', 'get', 'line');
  PERFORM dbe_file.new_line(f);
  PERFORM dbe_file.write_line(f, '1234567890');
  f := dbe_file.close(f);
END;
/
-- Expected result:
ANONYMOUS BLOCK EXECUTE

-- Read data from the file mentioned above.
DECLARE
  f integer;
  dir text := 'dir';
BEGIN
  f := dbe_file.open(dir, 'sample.txt', 'r');
  FOR i IN 1..11 LOOP
       RAISE INFO '[%] : %', i, dbe_file.read_line(f);
  END LOOP;
END;
/
-- Expected result:
INFO:  [1] : ABC
INFO:  [2] : 123
INFO:  [3] : -----
INFO:  [4] : 
INFO:  [5] : *******
INFO:  [6] : ++++++++
INFO:  [7] : 
INFO:  [8] : 
INFO:  [9] : #######
INFO:  [10] : AB
INFO:  [11] : [1 -> gaussdb, 2 -> dbe, 3 -> file, 4 -> get, 5 -> line]
ANONYMOUS BLOCK EXECUTE

-- Offset the file handle and obtain the current file location.
DECLARE
      l_file   integer;
      l_buffer VARCHAR2(32767);
      dir text := 'dir';
      abs_offset number := 100;
      rel_offset number := NULL;
BEGIN
      l_file := dbe_file.open(dir  => dir,  file_name  => 'sample.txt',open_mode => 'R');
      dbe_output.print_line('before seek: current position is ' || dbe_file.get_pos(file => l_file)); -- before seek: current position is 0
      dbe_file.seek(file => l_file, absolute_start=>abs_offset, relative_start=>rel_offset);
      dbe_output.print_line('fseek: current position is ' || dbe_file.get_pos(file => l_file)); -- seek: current position is 100
      l_file := dbe_file.close(file => l_file);
END;
/
-- Expected result:
before seek: current position is 0
fseek: current position is 100
ANONYMOUS BLOCK EXECUTE

      

    
   