WRITING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK

 
REQUEST

 
RELATED

 
TESTCASE

With the UTL_FILE package, it’s possible to read and write operating system text files. A file can be written both through the utl_file_dir initialization parameter and a database directory object. Both approaches are the same with slightly difference between them. By using the utl_file_dir you must restart a database everytime when you modify that parameter. Unlike the utl_file_dir a database directory object can be maintained dynamically. Regardless of an approach you choose Oracle highly recommends to use a directory object feature instead of the utl_file_dir.

UTL_FILE_DIR

Create a filesystem directory and assign it to the utl_file_dir parameter. By default, this parameter has a null value.

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

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

SQL> ALTER SYSTEM SET utl_file_dir="/tmp/utl_file_dir" SCOPE=SPFILE;

After modifying the utl_file_dir parameter restart a database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

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

Now let’s create a file

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 test of utl_file_dir parameter and 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 is created and contains the specified text.

oracle@dbpilot.net: ls -lh /tmp/utl_file_dir --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 55  my_file.txt

oracle@dbpilot.net: cat /tmp/utl_file_dir/my_file.txt
A test of utl_file_dir parameter and UTL_FILE package.

DATABASE DIRECTORY OBJECT

Create a filesystem directory and assign it to a directory object.

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

SQL> CREATE OR REPLACE DIRECTORY DROPZONE AS '/tmp/directory_object';

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

Execute PL/SQL block to create a file

DECLARE
   FILE_NAME VARCHAR2(100); 
   FILE_BODY VARCHAR2(100);
   FILE UTL_FILE.FILE_TYPE;
BEGIN
   FILE_NAME := 'my_file.txt';
   FILE_BODY := 'A test of the UTL_FILE package with a directory object.';
	
   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 is created and contains the specified text

oracle@dbpilot.net: ls -lh /tmp/directory_object/ --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 56  my_file.txt

oracle@dbpilot.net: cat /tmp/directory_object/my_file.txt
A test of the UTL_FILE package with a directory object.

The difference in both approaches is how I specify the directory, explicitly by filesystem full path or by the directory object name.

READ and WRITE priveliges

What happens if I try to do the same under another user? Will the PL/SQL block execute successfully or not?

CREATE USER UTL_USER IDENTIFIED BY UTL_USER;
GRANT CONNECT, RESOURCE TO UTL_USER;
CONNECT UTL_USER/UTL_USER

SQL> sho user
USER is "UTL_USER"

DECLARE
   FILE_NAME VARCHAR2(100); 
   FILE_BODY VARCHAR2(100);
   FILE_DIR  VARCHAR2(100);
   FILE UTL_FILE.FILE_TYPE;
BEGIN
   FILE_NAME := 'utl_user.txt';
   FILE_BODY := 'If you see this text that simply means that user has write privelige on a filesystem directory.';
   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 is created and contains the specified text

oracle@dbpilot.net: ls -lh /tmp/utl_file_dir --time-style='+'
total 8.0K
-rw-r--r-- 1 oracle oinstall 55  my_file.txt
-rw-r--r-- 1 oracle oinstall 94  utl_user.txt

oracle@dbpilot.net: cat /tmp/utl_file_dir/utl_user.txt
If you see this text that simply means that user has write privelige on a filesystem directory.

For the database directory object

DECLARE
   FILE_NAME VARCHAR2(100); 
   FILE_BODY VARCHAR2(100);
   FILE UTL_FILE.FILE_TYPE;
BEGIN
   FILE_NAME := 'utl_user.txt';
   FILE_BODY := 'If you see this text that simply means that user has write privelige on a directory object.';
	
   FILE := UTL_FILE.FOPEN('DROPZONE', FILE_NAME, 'W');
   UTL_FILE.PUT_LINE(FILE, FILE_BODY);
   UTL_FILE.FFLUSH(FILE);
   UTL_FILE.FCLOSE(FILE);
END;
/

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

When a user does not have any write privelige on database directory you get error as above.
The solution is simple, grant appropriate permissions under SYS user. The owner is always SYS user.

GRANT WRITE ON DIRECTORY SYS.DROPZONE TO UTL_USER;

and try again

CONNECT UTL_USER/UTL_USER

SQL> sho user
USER is "UTL_USER"

oracle@dbpilot.net: ls -lh /tmp/directory_object/ --time-style='+'
total 8.0K
-rw-r--r-- 1 oracle oinstall 56  my_file.txt
-rw-r--r-- 1 oracle oinstall 92  utl_user.txt

oracle@dbpilot.net: cat /tmp/directory_object/utl_user.txt
If you see this text that simply means that user has write privelige on a directory object.

For more details refer to the official documentation.