HOW TO READ A FILE FROM PL/SQL BLOCK
Reading an operating system text file from a database using the UTL_FILE package.
HOW TO WRITE TO 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.
All files for this DEMO were created in HOW TO WRITE TO A FILE FROM PL/SQL BLOCK
With the following PL/SQL block read the file
SET SERVEROUTPUT ON
DECLARE
FILE_DIR VARCHAR2(100);
FILE_NAME VARCHAR2(100);
FILE_CONTENT VARCHAR2(4000);
FILE UTL_FILE.FILE_TYPE;
BEGIN
FILE_DIR := '/tmp/utl_file_dir';
FILE_NAME := 'my_file.txt';
BEGIN
FILE := UTL_FILE.FOPEN(FILE_DIR, FILE_NAME, 'R', 32767);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Open_file_error : Unable to open the file.');
END;
BEGIN
LOOP
BEGIN
UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);
-- End of the file or Others error
EXCEPTION
WHEN OTHERS THEN EXIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : Unable to read the file.');
END;
UTL_FILE.FCLOSE(FILE);
END;
/
A dummy text written by UTL_FILE package.
PL/SQL procedure successfully completed.
All files for this DEMO were created in HOW TO WRITE TO A FILE FROM PL/SQL BLOCK
With the following PL/SQL block read the file
SET SERVEROUTPUT ON
DECLARE
FILE_NAME VARCHAR2(100);
FILE_CONTENT VARCHAR2(4000);
FILE UTL_FILE.FILE_TYPE;
BEGIN
FILE_NAME := 'my_file.txt';
BEGIN
FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'R', 32767);
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Open_file_error : Unable to open the file.');
END;
BEGIN
LOOP
BEGIN
UTL_FILE.GET_LINE(FILE, FILE_CONTENT);
DBMS_OUTPUT.PUT_LINE(FILE_CONTENT);
-- End of the file or Others error
EXCEPTION
WHEN OTHERS THEN EXIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : Unable to read the file.');
END;
UTL_FILE.FCLOSE(FILE);
END;
/
A dummy text written by UTL_FILE package.
PL/SQL procedure successfully completed.
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;
