WRITING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
REQUEST
RELATED
COPYING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
REMOVING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
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.
Platform : Red Hat 5.11
Database : Oracle database 12.1.0.1.0
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)