Advanced Server Oracle Compatibility Developer's Guide
The UTL_FILE package provides the capability to read from, and write to files on the operating system’s file system. Non-superusers must be granted EXECUTE privilege on the UTL_FILE package by a superuser before using any of the functions or procedures in the package. For example the following command grants the privilege to user mary:
GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO mary;
Also, the operating system username, enterprisedb, must have the appropriate read and/or write permissions on the directories and files to be accessed using the UTL_FILE functions and procedures. If the required file permissions are not in place, an exception is thrown in the UTL_FILE function or procedure.
A handle to the file to be written to, or read from is used to reference the file. The file handle is defined by a public variable in the UTL_FILE package named, UTL_FILE.FILE_TYPE. A variable of type FILE_TYPE must be declared to receive the file handle returned by calling the FOPEN function. The file handle is then used for all subsequent operations on the file.
References to directories on the file system are done using the directory name or alias that is assigned to the directory using the CREATE DIRECTORY command. The procedures and functions available in the UTL_FILE package are listed in the following table.
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory containing the file to be opened.
Name of the file to be opened.
Mode in which the file will be opened. Modes are: a - append to file; r - read from file; w - write to file.
Maximum size of a line in characters. In read mode, an exception is thrown if an attempt is made to read a line exceeding max_linesize. In write and append modes, an exception is thrown if an attempt is made to write a line exceeding max_linesize. The end-of-line character(s) are not included in determining if the maximum line size is exceeded. This behavior is not Oracle compatible - Oracle does count the end-of-line character(s).
Variable of type FILE_TYPE containing the file handle of the opened file.
The FREMOVE procedure removes a file from the system.
FREMOVE(location VARCHAR2, filename VARCHAR2)
An exception is thrown if the file to be removed does not exist.
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory containing the file to be removed.
The PUTF procedure writes a formatted string to the given file.
PUTF(file FILE_TYPE, format VARCHAR2 [, arg1 VARCHAR2]
Variable of type FILE_TYPE containing the file handle of the file to which the formatted line is to be written.
String to format the text written to the file. The special character sequence, %s, is substituted by the value of arg. The special character sequence, \n, indicates a new line. Note, however, in Postgres Plus Advanced Server, a new line character must be specified with two consecutive backslashes instead of one - \\n. This characteristic is not Oracle compatible.
Up to five arguments, arg1,...arg5, to be substituted in the format string for each occurrence of %s. The first arg is substituted for the first occurrence of %s, the second arg is substituted for the second occurrence of %s, etc.
The following anonymous block produces formatted output containing data from the emp table. Note the use of the E literal syntax and double backslashes for the new line character sequence in the format string which are not Oracle compatible.
v_directory VARCHAR2(50) := 'empdir';
v_filename VARCHAR2(20) := 'empfile.csv';
CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
v_format := E'%s %s, %s\\nSalary: $%s Commission: $%s\\n\\n';
v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
FOR i IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
Created file: empfile.csv
The following is the contents of empfile.csv created above: