HOW TO WRITE TO A FILE FROM PL/SQL BLOCK
Writing data into an operating system text file from a database using the UTL_FILE package.
HOW TO READ A FILE FROM PL/SQL BLOCK
HOW TO COPY AN OPERATING SYSTEM FILE FROM PL/SQL BLOCK
HOW TO REMOVE AN OPERATING SYSTEM FILE FROM PL/SQL BLOCK
The UTL_FILE package can read and write operating system text files. You can access a directory where a file is located with the UTL_FILE_DIR symbolic link path or a DIRECTORY object.
Starting with Oracle Database 18c the UTL_FILE_DIR symbolic link path is desupported.
You cannot use UTL_FILE with symbolic links. Use directory objects instead.
The UTL_FILE_DIR symbolic link path is desupported in Oracle Database 18c and later releases.
After an upgrade if applications address the database using symbolic links through UTL_FILE,
then these links fail. Oracle recommends that you use directory objects. If necessary, you can
create real files that are the targets of file names in UTL_FILE.
This desupport can affect any feature from an earlier release using symbolic links, including
(but not restricted to) Oracle Data Pump, BFILEs, and External Tables. If you attempt to use
an affected feature after upgrade, where that feature used symbolic links, you encounter
ORA-29283: invalid file operation: path traverses a symlink. Before upgrade, to help you to identify
symbolic link that you need to correct, use the Preupgrade Information Tool (preupgrade.jar).
Oracle recommends that you instead use directory objects in place of symbolic links.
The UTL_FILE_DIR is desupported in 18c and higher. So, this approach works in 12c and lower.
Create a directory on a server and specify its path in the UTL_FILE_DIR parameter. By default, this parameter has a null value.
## A directory for a file
oracle@dbpilot.net: mkdir /tmp/utl_file_dir
oracle@dbpilot.net: ls -lhd /tmp/utl_file_dir --time-style='+'
drwxr-xr-x 2 oracle oinstall 4.0K /tmp/utl_file_dir
oracle@dbpilot.net: ls -lh /tmp/utl_file_dir
total 0
-- To display current value of the parameter
COL NAME FOR A15
COL DISPLAY_VALUE FOR A15
COL DEFAULT_VALUE FOR A15
SELECT NAME, DISPLAY_VALUE, ISDEFAULT FROM V$PARAMETER WHERE NAME LIKE '%utl_file_dir%';
NAME DISPLAY_VALUE ISDEFAULT
--------------- --------------- ---------------------------
utl_file_dir TRUE
-- Set a new value
SQL> ALTER SYSTEM SET utl_file_dir="/tmp/utl_file_dir" SCOPE=SPFILE;
After modification of the UTL_FILE_DIR initialization parameter, restart the database
-- Bounce the database
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
-- Ensure the parameter is set successfully
COL NAME FOR A15
COL DISPLAY_VALUE FOR A20
COL DEFAULT_VALUE FOR A15
SELECT NAME, DISPLAY_VALUE, ISDEFAULT FROM V$PARAMETER WHERE NAME LIKE '%utl_file_dir%';
NAME DISPLAY_VALUE ISDEFAULT
--------------- -------------------- ---------------------------
utl_file_dir /tmp/utl_file_dir FALSE
With the following PL/SQL block, create a file and write some dummy text to it.
DECLARE
FILE_NAME VARCHAR2(100);
FILE_BODY VARCHAR2(100);
FILE_DIR VARCHAR2(100);
FILE UTL_FILE.FILE_TYPE;
BEGIN
FILE_NAME := 'my_file.txt';
FILE_BODY := 'A dummy text written by UTL_FILE package.';
FILE_DIR := '/tmp/utl_file_dir';
FILE := UTL_FILE.FOPEN(FILE_DIR, FILE_NAME, 'W');
UTL_FILE.PUT_LINE(FILE, FILE_BODY);
UTL_FILE.FFLUSH(FILE);
UTL_FILE.FCLOSE(FILE);
END;
Confirm that the file was created and contains the specified text.
## The file
oracle@dbpilot.net: ls -lh /tmp/utl_file_dir --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 55 my_file.txt
## The file content
oracle@dbpilot.net: cat /tmp/utl_file_dir/my_file.txt
A dummy text written by UTL_FILE package.
Create a directory on a server and specify its path to a directory object
## A directory for a file
oracle@dbpilot.net: mkdir /tmp/directory_object
oracle@dbpilot.net: ls -ld /tmp/directory_object
drwxr-xr-x 2 oracle oinstall 4096 Dec 6 12:40 /tmp/directory_object
oracle@dbpilot.net: ls -l /tmp/directory_object
total 0
-- Create a new directory object
SQL> CREATE OR REPLACE DIRECTORY DROPZONE AS '/tmp/directory_object';
-- Verify the object is created
COL OWNER FOR A5
COL DIRECTORY_NAME FOR A15
COL DIRECTORY_PATH FOR A30
SELECT OWNER,DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DROPZONE';
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- --------------- ------------------------------
SYS DROPZONE /tmp/directory_object
With the following PL/SQL block, create a file and write some dummy text to it.
DECLARE
FILE_NAME VARCHAR2(100);
FILE_BODY VARCHAR2(100);
FILE UTL_FILE.FILE_TYPE;
BEGIN
FILE_NAME := 'my_file.txt';
FILE_BODY := 'A dummy text written by UTL_FILE package.';
FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'W');
UTL_FILE.PUT_LINE(FILE, FILE_BODY);
UTL_FILE.FFLUSH(FILE);
UTL_FILE.FCLOSE(FILE);
END;
/
Confirm that the file was created and contains the specified text.
## The file
oracle@dbpilot.net: ls -lh /tmp/directory_object/ --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 56 my_file.txt
## The file content
oracle@dbpilot.net: cat /tmp/directory_object/my_file.txt
A dummy text written by UTL_FILE package.
The difference between the two examples is how the directory was specified: by its full pathname(utl_file_dir) or by its alias (directory object).
To READ/WRITE a file, you must have appropriate permission on a directory. For instance, when there is no write permission on a directory object the following error occurs.
ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 9
To grant READ/WRITE permissions on a directory object to a user, execute the following
GRANT WRITE ON DIRECTORY SYS.YOUR_DIRECTORY TO YOUR_USER;
GRANT READ ON DIRECTORY SYS.YOUR_DIRECTORY TO YOUR_USER;
