Introduced in Oracle 7.3, UTL_FILE PL/SQL package provides a functionality of reading and writing text file into operating system files. That means you can load external data from other source to database or can generate report from your PL/SQL code and write to text file on operating system.
UTL_FILE package provides access to text file on the OS of the database server. You can use one of the following approaches to restrict access to text file from database server.
- Use the UTL_FILE_DIR parameter in init.ora initialization file to specify the accessible directory for the UTL_FILE functions to read or write operation
- Use the CREATE DIRECTORY statement [ORACLE 9i or above]to associate an operating system directory with alias to provide read/write operation
CREATE DIRECTORY my_demo_dir AS 'E:\app\madhurendra\wr_file'; GRANT READ, WRITE ON DIRECTORY my_demo_dir TO user$plsql;
If want to grant read and write privilege to public, replace user$plsql with public.
The main difference between two option is with first directory is accessible to all, since parameter in init.ora file is valid for all users in database and with directory object feature you can restrict access to the directory. The latter option is recommended by ORACLE but you must have a CREATE ANY DIRECTRY privilege to create a directory.
Followings are some procedures, functions and exceptions of the UTL_FILE package.
FILE_TYPE Data type for a file-handle
PROCEDURES & FUCTIONS
IS_OPEN Checks whether file is open or not. Returns TRUE if open and FALSE if close. FOPEN Opens a file for read or write. FCLOSE Closes an open file FCLOSE_ALL Closes all open file for the current session NEW_LINE Insert a new line terminator to a file PUT_LINE Write strings of text followed by a new line terminator PUT Same as PUT_LINE but don’t place a new line terminator. PUTF Writes formatted text into file GET_LINE Read a line from file. The maximum output FFLUSH Writes all the buffered data into file
INVALID_PATH File location is not valid INVALID_MODE OPEN_FILE mode is not valid. [Specific to FOPEN] INVALID_FILENAME Filename is not valid READ_ERROR Operating system error occurred during the read operation. WRITE_ERROR Operating system error occurred during the write operation. FILE_OPEN Operation failed because the file is already open.
These exceptions must be prefixed with UTL_FILE. Apart from that UTL_FILE can also raise pre-defined exceptions like NO_DATA_FOUND and VALUE_ERROR.
Followings are a couple of very simple example of UTL_FILE package:
In the following anonymous block, data is written by UTL_FILE package to a text file ‘my_f_file.txt’.
DECLARE v_file UTL_FILE.FILE_TYPE; BEGIN v_file := UTL_FILE.FOPEN('MY_DEMO_DIR', 'my_f_file.txt', 'w'); UTL_FILE.PUT_LINE(v_file, 'Hello World!'); UTL_FILE.PUT_LINE(v_file, 'I stared using UTL_FILE'); UTL_FILE.FCLOSE(v_file); END; /
FOPEN takes the following parameters:
- location: name of a directory defined by a CREATE DIRECTORY statement, or an existing directory path specified by using the utl_file_dir database parameter
- filename: Specifies the name of the file with extension and without path information
- open_mode: Specifies how the file is to be opened. Values are:
- ‘R‘ for reading text
- ‘W‘ for writing text
- ‘A‘ for appending text
- max_linesize: Maximum number of characters for each line, including the newline character. If unspecified, Oracle supplies a default value of 1024.
Procedure file_read reads data from the file and prints them using dbms_output. It also has exception section which has one pre-defined PL/SQL exception NO_DATA_FOUND and ACCESS_DENIED is specific to UTL_FILE package. Since we are reading a file in a loop, the NO_DATA_FOUND exception is used to exit the loop when there nothing to read.
CREATE OR REPLACE PROCEDURE file_read AS v_file UTL_FILE.FILE_TYPE; v_line VARCHAR2(70); BEGIN v_file := UTL_FILE.FOPEN ('MY_DEMO_DIR', 'my_f_file.txt', 'r'); LOOP UTL_FILE.GET_LINE (v_file, v_line); DBMS_OUTPUT.PUT_LINE(v_line); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(CHR(10)); DBMS_OUTPUT.PUT_LINE('READING OPERATION FINISHED....'); UTL_FILE.FCLOSE(v_file); WHEN UTL_FILE.ACCESS_DENIED THEN DBMS_OUTPUT.PUT_LINE(CHR(10)); DBMS_OUTPUT.PUT_LINE('DIRECTORY ACCESS DENIED....'); END; user$plsql@O11R2>exec file_read Hello World! I stared using UTL_FILE READING OPERATION FINISHED.... PL/SQL procedure successfully completed.
Revoke read privilege on directory my_demo_dir from user user$plsql.
dbadmin@O11R2>REVOKE READ ON DIRECTORY my_demo_dir FROM hr; Revoke succeeded. user$plsql@O11R2>exec file_read DIRECTORY ACCESS DENIED.... PL/SQL procedure successfully completed.