DBE_FILE

Precautions

• DBE_FILE requires that files opened using DBE_FILE.FOPEN be encoded using the database character set. If the opened files are not encoded using the expected character set, an encoding verification error occurs when DBE_FILE.READ_LINE is used to read files. DBE_FILE requires that files opened using DBE_FILE.FOPEN_NCHAR be encoded using the UTF-8 character set. If the opened files are not encoded using the expected character set, an encoding verification error occurs when DBE_FILE.READ_LINE_NCHAR is used to read files.
• When DBE_OUTPUT.PUT_LINE is used to print the result obtained by the DBE_FILE.READ_LINE_NCHAR API, ensure that the UTF-8 character set encoding can be converted to the current database character set encoding. If the preceding conditions are met, the result can be properly output. DBE_OUTPUT.PRINT_LINE does not support this function.
• DBE_FILE requires that the character set encoding of the client be the same as that of the database.
• Assume that the database character set encoding is ASCII, the client character set encoding supports Chinese, and the client calls DBE_FILE.WRITE_NCHAR or DBE_FILE.WRITE_LINE_NCHAR to write Chinese content. If the entered content is in UTF-8 encoding format, the written content may not be encoded in UTF-8 format. An error may be reported when the DBE_FILE.READ_LINE_NCHAR is used.

Data Types

• DBE_FILE.FILE_TYPE

  Defines the representation of files in the DBE_FILE package. The fields in DBE_FILE.FILE_TYPE are private fields of the DBE_FILE package. Do not change the field value of the type defined in DBE_FILE.FILE_TYPE.

  1 2 3 4 5

  CREATE TYPE DBE_FILE.FILE_TYPE AS( id INTEGER, datatype INTEGER, byte_mode BOOLEAN );

  Table 1 DBE_FILE.FILE_TYPE columns

  Parameter	Description
  id	File handle
  datatype	File data type (CHAR, NCHAR, or binary). Currently, only CHAR and NCHAR files are supported. For a CHAR file, 1 is returned. For an NCHAR file, 2 is returned.
  byte_mode	Indicates that the file is opened in binary mode (TRUE) or text mode (FALSE).

API Description

Table 2 lists all APIs supported by the DBE_FILE package.

• DBE_FILE.OPEN/DBE_FILE.FOPEN

  Opens a file. You can specify the maximum line size. A maximum of 50 files can be opened in a session. This function returns a file handle of the INTEGER type. The function of DBE_FILE.FOPEN is similar to that of DBE_FILE.OPEN. It returns an object of the type defined in DBE_FILE.FILE_TYPE.

  The prototypes of the DBE_FILE.OPEN and DBE_FILE.FOPEN functions are:

  1 2 3 4 5 6 7 8 9 10 11 12 13

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

  Table 3 DBE_FILE.OPEN/DBE_FILE.FOPEN parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  dir	text	IN	No	Directory of a file. It is a string, indicating an object name. 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 read and write files in the file path specified by safe_data_path.
  file_name	text	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	text	IN	No	File opening mode, including: r (read text) w (write text) a (append text) rb (read byte) wb (write byte) ab (append byte) 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 bytes 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_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:

  1 2 3 4 5 6 7

  DBE_FILE.IS_CLOSE( file IN INTEGER) RETURN BOOLEAN; DBE_FILE.IS_CLOSE( file IN DBE_FILE.FILE_TYPE) RETURN BOOLEAN;

  Table 4 DBE_FILE.IS_CLOSE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	Yes	File handle or DBE_FILE.FILE_TYPE object to be checked. If the value is empty, the DBE_FILE.IS_CLOSE API returns empty.

• DBE_FILE.IS_OPEN

  Checks whether a file handle 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:

  1 2 3 4 5 6 7

  DBE_FILE.IS_OPEN( file IN INTEGER) RETURN BOOLEAN; DBE_FILE.IS_OPEN( file IN DBE_FILE.FILE_TYPE) RETURN BOOLEAN;

  Table 5 DBE_FILE.IS_OPEN parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	Yes	File handle or DBE_FILE.FILE_TYPE object to be checked. If the value is empty, the DBE_FILE.IS_OPEN API returns false.

• 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 stored procedure is as follows:

  1 2 3 4 5 6 7 8 9 10 11

  DBE_FILE.READ_LINE( file IN INTEGER, buffer OUT TEXT, len IN INTEGER DEFAULT NULL) RETURN TEXT; DBE_FILE.READ_LINE( file IN DBE_FILE.FILE_TYPE, buffer OUT TEXT, len IN INTEGER DEFAULT NULL) RETURN TEXT;

  Table 6 DBE_FILE.READ_LINE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN. The file must be opened in read mode. Otherwise, the INVALID_OPERATION exception is thrown.
  buffer	text	OUT	No	Buffer for receiving data.
  len	integer	IN	Yes	Number of bytes read from the 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:

  1 2 3 4 5 6 7 8 9

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

  Table 7 DBE_FILE.WRITE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN. The file must be opened in write mode. This operation does not write line terminators.
  buffer	text	IN	Yes	Text data to be written to the file. The accumulated write length of each line cannot be greater than or equal to the value of max_line_size specified when OPEN or FOPEN is used. Otherwise, an error is reported when the file is refreshed. If this parameter is left empty, the API directly returns an empty value. 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:

  1 2 3 4 5 6 7 8 9

  DBE_FILE.NEW_LINE( file IN INTEGER, line_nums IN INTEGER DEFAULT 1) RETURN BOOLEAN; DBE_FILE.NEW_LINE( file IN DBE_FILE.FILE_TYPE, line_nums IN INTEGER DEFAULT 1) RETURN VOID;

  Table 8 DBE_FILE.NEW_LINE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.
  line_nums	integer	IN	Yes	Number of line terminators written to a file. The default value is 1. If this parameter is left blank, line terminators are not written.

• 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:

  1 2 3 4 5 6 7 8 9 10 11

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

  Table 9 DBE_FILE.WRITE_LINE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.
  buffer	text	IN	Yes	Text data to be written to the file. The length of each line (including the newline character) cannot be greater than the value of max_line_size specified when OPEN or FOPEN is executed or the default value. Otherwise, an error is reported when the file is refreshed. 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	Determines whether to flush the data in the buffer corresponding to the file to the disk after WRITE_LINE. The default value is FALSE.

• 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:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  DBE_FILE.FORMAT_WRITE( file IN INTEGER, format IN TEXT, arg1 IN TEXT DEFAULT NULL, . . . arg6 IN TEXT DEFAULT NULL) RETURN BOOLEAN; DBE_FILE.FORMAT_WRITE( file IN DBE_FILE.FILE_TYPE, format IN TEXT, arg1 IN TEXT DEFAULT NULL, . . . arg6 IN TEXT DEFAULT NULL) RETURN VOID;

  Table 10 DBE_FILE.FORMAT_WRITE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.
  format	text	IN	Yes	Formatted string, containing the text and format characters \n and %s. If this parameter is left blank, no data is written.
  [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 or the parameter is empty, an empty string is used to replace %s.

• DBE_FILE.GET_RAW

  Reads RAW data from an open file, stores the read result in the buffer, and returns the result from r.

  The prototype of the DBE_FILE.GET_RAW stored procedure is as follows:

  1 2 3 4 5 6 7 8 9 10 11

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

  Table 11 DBE_FILE.GET_RAW parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	INTEGER or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.
  r	RAW	OUT	No	Buffer for receiving RAW data.
  length	INTEGER	IN	Yes	Number of bytes read from the file. The default value is NULL. If the value is NULL, the maximum length of the RAW type is used to specify the size.

• 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:

  1 2 3 4 5 6 7 8 9 10 11

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

  Table 12 DBE_FILE.PUT_RAW parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	INTEGER or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.
  r	RAW	IN	No	RAW data to be written to a 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.
  flush	BOOLEAN	IN	Yes	Determines whether to flush data to disks after PUT_RAW. The default value is FALSE.

• DBE_FILE.FLUSH

  Writes data in a 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:

  1 2 3 4 5 6 7

  DBE_FILE.FLUSH( file IN INTEGER) RETURN VOID; DBE_FILE.FLUSH( file IN DBE_FILE.FILE_TYPE) RETURN VOID;

  Table 13 DBE_FILE.FLUSH parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	INTEGER or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.

• DBE_FILE.CLOSE

  Closes an open file handle. When this function is called, if there is cached data to be written, exception information may be received. If the handle is normally closed, TRUE is returned.

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

  1 2 3 4 5 6 7

  DBE_FILE.CLOSE( file IN INTEGER) RETURN BOOLEAN; DBE_FILE.CLOSE( file IN DBE_FILE.FILE_TYPE) RETURN BOOLEAN;

  Table 14 DBE_FILE.CLOSE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	integer or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.

• 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:

  1 2	DBE_FILE.CLOSE_ALL() RETRUN VOID;

• 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:

  1 2 3 4

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

  Table 15 DBE_FILE.REMOVE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  dir	TEXT	IN	No	Directory of a file. It is a string, indicating an object name. 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.
  file_name	TEXT	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:

  1 2 3 4 5 6 7

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

  Table 16 DBE_FILE.RENAME parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  src_dir	TEXT	IN	No	Directory of the source file (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	TEXT	IN	No	Source file to be renamed
  dest_dir	TEXT	IN	No	Target 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	text	IN	No	New file name
  overwrite	boolean	IN	Yes	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:

  1 2 3 4 5 6 7 8

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

  Table 17 DBE_FILE.COPY parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  src_dir	TEXT	IN	No	Directory of the source file 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	TEXT	IN	No	Name of the source file to be copied.
  dest_dir	TEXT	IN	No	Directory of the destination file 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	TEXT	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	TEXT	IN	No	Number of the line where the copy starts. The default value is 1.
  end_line	TEXT	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 stored procedure is as follows:

  1 2 3 4 5 6 7

  DBE_FILE.GET_ATTR( location IN TEXT, filename IN TEXT, fexists OUT BOOLEAN, file_length OUT BIGINT, block_size OUT INTEGER) RETURN RECORD;

  Table 18 DBE_FILE.GET_ATTR parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  location	TEXT	IN	No	File directory 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.
  filename	TEXT	IN	No	File name.
  fexists	boolean	OUT	No	Whether the file exists.
  file_length	BIGINT	OUT	No	File length (unit: byte). If the file does not exist, NULL is returned.
  block_size	INTEGE	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:

  1 2 3 4 5 6 7 8 9 10 11

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

  Table 19 DBE_FILE.SEEK parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	INTEGER or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.
  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:

  1 2 3 4 5 6 7

  DBE_FILE.GET_POS( file IN INTEGER) RETURN BIGINT; DBE_FILE.GET_POS( file IN DBE_FILE.FILE_TYPE) RETURN BIGINT;

  Table 20 DBE_FILE.GET_POS parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	INTEGER or dbe_file.file_type	IN	No	File handle opened using OPEN or object of the DBE_FILE.FILE_TYPE type opened using FOPEN.

• DBE_FILE.FOPEN_NCHAR

  Opens a file. You can specify the maximum line size. A maximum of 50 files can be opened in a session. This function returns a DBE_FILE.FILE_TYPE type object that encapsulates a file handle. This function opens a file in national character set mode for input or output.

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

  1 2 3 4 5 6

  DBE_FILE.FOPEN_NCHAR( dir IN TEXT, file_name IN TEXT, open_mode IN TEXT, max_line_size IN INTEGER DEFAULT 1024) RETURN DBE_FILE.FILE_TYPE;

  Table 21 DBE_FILE.FOPEN_NCHAR parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  dir	TEXT	IN	No	Directory of a file. It is a string, indicating an object name. 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 read and write files in the file path specified by safe_data_path.
  file_name	TEXT	IN	No	File name with an extension (file type), excluding the path name. A path contained in a file name is ignored in the FOPEN_NCHAR function. In Unix, the file name cannot end with the combination of a slash and a dot (/.).
  open_mode	TEXT	IN	No	File opening mode, including: r (read text) w (write text) a (append text) rb (read byte) wb (write byte) ab (append byte) 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 bytes 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.WRITE_NCHAR

  Writes buffer data to the buffer of a file. The file must be opened in national character set or write mode. This operation does not write a line terminator. The text string is written in the UTF-8 character set format.

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

  1 2 3 4

  DBE_FILE.WRITE_NCHAR( file IN DBE_FILE.FILE_TYPE, buffer IN NVARCHAR2) RETURN VOID;

  Table 22 DBE_FILE.WRITE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	dbe_file.file_type	IN	No	Object of the DBE_FILE.FILE_TYPE type opened using FOPEN_NCHAR. The file must be opened in write mode. This operation does not write line terminators.
  buffer	NVARCHAR2	IN	Yes	Text data to be written to the file. The accumulated write length of each line cannot be greater than or equal to the value of max_line_size specified by FOPEN_NCHAR. Otherwise, an error is reported when the file is refreshed. 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.WRITE_LINE_NCHAR

  Writes buffer data to the buffer of a file. The file must be opened in national character set or write mode. This operation automatically adds a line terminator. The text string is written in the UTF8 character set format.

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

  1 2 3 4

  DBE_FILE.WRITE_LINE_NCHAR( file IN DBE_FILE.FILE_TYPE, buffer IN NVARCHAR2) RETURN VOID;

  Table 23 DBE_FILE.WRITE_LINE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	dbe_file.file_type	IN	No	Object of the DBE_FILE.FILE_TYPE type opened using FOPEN_NCHAR.
  buffer	NVARCHAR2	IN	Yes	Text data to be written to the file. The length of each line (including the newline character) cannot be greater than the value of max_line_size specified by FOPEN_NCHAR or the default value. Otherwise, an error is reported when the file is refreshed. 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.FORMAT_WRITE_NCHAR

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

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

  1 2 3 4 5 6 7

  DBE_FILE.FORMAT_WRITE_NCHAR( file IN DBE_FILE.FILE_TYPE, format IN NVARCHAR2, arg1 IN NVARCHAR2 DEFAULT NULL, . . . arg5 IN NVARCHAR2 DEFAULT NULL) RETURN VOID;

  Table 24 DBE_FILE.FORMAT_WRITE_NCHAR parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	dbe_file.file_type	IN	No	Object of the DBE_FILE.FILE_TYPE type opened using FOPEN_NCHAR.
  format	NVARCHAR2	IN	Yes	Formatted string, containing the text and format characters \n and %s.
  [arg1. . .arg5]	NVARCHAR2	IN	Yes	Five 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.READ_LINE_NCHAR

  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 by FOPEN_NCHAR.

  The prototype of the DBE_FILE.READ_LINE_NCHAR stored procedure is as follows:

  1 2 3 4 5

  DBE_FILE.READ_LINE_NCHAR( file IN DBE_FILE.FILE_TYPE, buffer OUT NVARCHAR2, len IN INTEGER DEFAULT NULL) RETURN NVARCHAR2;

  Table 25 DBE_FILE.READ_LINE parameters

  Parameter	Type	Input/Output Parameter	Can Be Empty	Description
  file	dbe_file.file_type	IN	No	Object of the DBE_FILE.FILE_TYPE type opened using FOPEN_NCHAR. The file must be opened in read mode. Otherwise, the INVALID_OPERATION exception is thrown.
  buffer	NVARCHAR2	OUT	No	Buffer for receiving data.
  len	INTEGER	IN	Yes	Number of bytes read from the file. The default value is NULL. If the default value NULL is used, max_line_size is used to specify the line size.

Examples

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

-- Add the /tmp/ directory to the PG_DIRECTORY system catalog as a system administrator.
CREATE OR REPLACE DIRECTORY dir AS '/tmp/';
-- Execution result:
CREATE DIRECTORY
-- Use the DBE_FILE advanced package.
DECLARE
    f INTEGER;
    buffer VARCHAR2;
    raw_buffer RAW;

    f1 DBE_FILE.FILE_TYPE;
    f2 DBE_FILE.FILE_TYPE;

    fexists BOOLEAN;
    file_length BIGINT;
    block_size INTEGER;
    pos BIGINT;

    nvarchar_buffer nvarchar2;
    f_nchar DBE_FILE.FILE_TYPE;
BEGIN
    -- Open a file.
    f := DBE_FILE.OPEN('dir', 'sample.txt', 'w');

    IF DBE_FILE.IS_OPEN(f) = true THEN
        DBE_OUTPUT.PRINT_LINE('file opened');
    END IF;

    -- Close the file.
    DBE_FILE.CLOSE(f);

    IF DBE_FILE.IS_CLOSE(f) = true THEN
        DBE_OUTPUT.PRINT_LINE('file closed');
    END IF;

    f := DBE_FILE.OPEN('dir', 'sample.txt', 'w');

-- Write a file.
    DBE_FILE.WRITE(f, 'A');
    DBE_FILE.NEW_LINE(f);
    DBE_FILE.WRITE(f, 'B');
    DBE_FILE.WRITE(f, 'C');
    DBE_FILE.NEW_LINE(f, 2);
    DBE_FILE.WRITE_LINE(f, 'ABC');
    DBE_FILE.FORMAT_WRITE(f, '[1 -> %s, 2 -> %s]\n', 'GaussDB', 'DBE_FILE');
    DBE_FILE.PUT_RAW(f, '414243');
    DBE_FILE.NEW_LINE(f);
    DBE_FILE.CLOSE(f);

    -- Create sample_copy.txt and copy the content of sample.txt.
    DBE_FILE.COPY('dir', 'sample.txt', 'dir', 'sample_copy.txt');

    -- Open a file in read mode.
    f := DBE_FILE.OPEN('dir', 'sample_copy.txt', 'r');
-- Read a file.
    DBE_FILE.READ_LINE(f, buffer); -- A
    DBE_OUTPUT.PRINT_LINE(buffer);
    DBE_FILE.READ_LINE(f, buffer); -- BC
    DBE_OUTPUT.PRINT_LINE(buffer);
    DBE_FILE.READ_LINE(f, buffer);
    DBE_FILE.READ_LINE(f, buffer); -- ABC
    DBE_OUTPUT.PRINT_LINE(buffer);
    DBE_FILE.READ_LINE(f, buffer); -- [1 -> GaussDB, 2 -> DBE_FILE]
    DBE_OUTPUT.PRINT_LINE(buffer);
    DBE_FILE.READ_LINE(f, buffer); -- RAW 414243 --> ABC
    DBE_OUTPUT.PRINT_LINE(buffer);

    -- Close the file.
    DBE_FILE.CLOSE(f);

    f1 := DBE_FILE.FOPEN('dir', 'sample1.txt', 'w');
    f2 := DBE_FILE.FOPEN('dir', 'sample2.txt', 'w');
    DBE_FILE.CLOSE_ALL();

    IF DBE_FILE.IS_CLOSE(f1) = true and DBE_FILE.IS_CLOSE(f2) = true THEN
        DBE_OUTPUT.PRINT_LINE('f1 and f2 all closed');
    END IF;

    -- Delete the file.
    DBE_FILE.REMOVE('dir', 'sample1.txt');
    DBE_FILE.REMOVE('dir', 'sample2.txt');
    DBE_FILE.REMOVE('dir', 'sample_copy.txt');

    -- Open a file and clear the data in sample.txt.
    f := DBE_FILE.OPEN('dir', 'sample.txt', 'w');
    DBE_FILE.WRITE_LINE(f, 'ABC');
    DBE_FILE.CLOSE(f);

    f := DBE_FILE.OPEN('dir', 'sample.txt', 'r');
    -- GET_RAW
    DBE_FILE.GET_RAW(f, raw_buffer); -- 0A of 4142430A is a newline character.
    DBE_OUTPUT.PRINT_LINE(raw_buffer);
    DBE_FILE.CLOSE(f);

    -- Obtain file attributes.
    DBE_FILE.GET_ATTR('dir', 'sample.txt', fexists, file_length, block_size);

    IF fexists = true THEN
        DBE_OUTPUT.PRINT_LINE('file length: ' || file_length);
    END IF;

    -- Change the file name.
    DBE_FILE.RENAME('dir', 'sample.txt', 'dir', 'sample_rename.txt', true);
    f1 := DBE_FILE.FOPEN('dir', 'sample_rename.txt', 'r');
    DBE_FILE.SEEK(f1, 1, null);
    pos := DBE_FILE.GET_POS(f1);
    DBE_OUTPUT.PRINT_LINE('position is: ' || pos);
    DBE_FILE.READ_LINE(f1, buffer); -- BC
    DBE_OUTPUT.PRINT_LINE(buffer);
    DBE_FILE.CLOSE(f1);

    -- FLUSH
    f1 := DBE_FILE.FOPEN('dir', 'sample_rename.txt', 'w');
    DBE_FILE.WRITE_LINE(f1, 'ABCEFG');
    DBE_FILE.FLUSH(f1);

    f2 := DBE_FILE.FOPEN('dir', 'sample_rename.txt', 'r');
    DBE_FILE.READ_LINE(f2, buffer); -- ABCEFG
    DBE_OUTPUT.PRINT_LINE(buffer);
    DBE_FILE.CLOSE(f1);
    DBE_FILE.CLOSE(f2);
    DBE_FILE.REMOVE('dir', 'sample_rename.txt');

    -- NCHAR function
    f_nchar := DBE_FILE.FOPEN_NCHAR('dir', 'sample_nchar.txt', 'w');
    DBE_FILE.WRITE_NCHAR(f_nchar, 'ABCDE');
    DBE_FILE.WRITE_LINE_NCHAR(f_nchar, 'ABCDE');
    DBE_FILE.FORMAT_WRITE_NCHAR(f_nchar, '%s, %s', 'hello', 'world');
    DBE_FILE.CLOSE(f_nchar);
    f_nchar := DBE_FILE.FOPEN_NCHAR('dir', 'sample_nchar.txt', 'r');
    DBE_FILE.READ_LINE_NCHAR(f_nchar, nvarchar_buffer); -- ABCDEABCDE
    DBE_OUTPUT.PRINT_LINE(nvarchar_buffer);
    DBE_FILE.READ_LINE_NCHAR(f_nchar, nvarchar_buffer); -- hello, world
    DBE_OUTPUT.PRINT_LINE(nvarchar_buffer);
    DBE_FILE.CLOSE(f_nchar);
    DBE_FILE.REMOVE('dir', 'sample_nchar.txt');
END;
/

-- Execution result:
file opened
file closed
A
BC
ABC
[1 -> GaussDB, 2 -> DBE_FILE]
ABC
f1 and f2 all closed
4142430A
file length: 4
position is: 1
BC
ABCEFG
ABCDEABCDE
hello, world
ANONYMOUS BLOCK EXECUTE