Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE.
The older way is to set the INIT.ORA parameter UTL_FILE_DIR. We have to restart the database for a change to take affect. The value can like any other PATH variable; it accepts wildcards. Using this approach means passing the directory path…
UTL_FILE.FOPEN('c:\temp', 'vineet.txt', 'W');
The alternative approach is to declare a directory object.
create or replace directory temp_dir as 'C:\temp' / grant read, write on directory temp_dir to vineet /
Directory objects require the exact file path, and don’t accept wildcards. In this approach we pass the directory object name…
UTL_FILE.FOPEN('TEMP_DIR', 'vineet.txt', 'W');
The UTL_FILE_DIR is deprecated because it is inherently insecure – all users have access to all the OS directories specified in the path, whereas read and write privileges can de granted discretely to individual users. Also, with Directory objects we can be add, remove or change directories without bouncing the database.
In either case, the oracle OS user must have read and/or write privileges on the OS directory. In case it isn’t obvious, this means the directory must be visible from the database server. So we cannot use either approach to expose a directory on our local PC to a process running on a remote database server. Files must be uploaded to the database server, or a shared network drive.
If the oracle OS user does not have the appropriate privileges on the OS directory, or if the path specified in the database does not match to an actual path, the program will hurl this exception:
ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at line 7
The OERR text for this error is pretty clear:
29283 - "invalid file operation"
*Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.
*Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.