|
Category :
Database
Resources -> RDBMS Server
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
Utlfiles
plays an important role in PLSQL development and is one of the primary
method for oracle's interaction with the Operating system. This
articles is divided in to two parts. In the first part, we'll see
oracle9i's enhancement to managing UTLFILEs and the second part of the
article looks at how permissions on the utlfiles are handled by
oracle.
(1) Oracle9i enhancement
to UTLFILE management:
In versions prior to 9i,
directory locations for UTLFILE are defined using the UTL_FILE_DIR
parameter in the init file for the database instance. Anytime the
utlfile location needs to be changed or a new location needs to be
added, the parameter value needs to be changed and the instance
restarted for the values to take effect. Not anymore!
Starting 9i release2, utlfile
locations can be dynamically maintained using the CREATE DIRECTORY
feature. Here's an example:
Step 1) Connect as sys
Step 2) Create a directory:
SQL> CREATE DIRECTORY utlfileforprojectx_dir AS '/oradata/utlfile/projectx';
Directory created.
Step 3) Grant read on the directory
SQL> grant read on directory utlfileforprojectx_dir to public;
Grant succeeded.
While using the directory
that was created using the CREATE DIRECTORY method , the directory
name should be all uppercase during the call to utlfile management
packages. For example:
declare
v_output utl_file.file_type;
begin
v_output := utl_file.fopen('UTLFILEFORPROJECTX_DIR', 'test.txt', 'w');
utl_file .put_line(v_output, 'This is a test');
utl_file.fclose_all;
end;
/
(2) UTFILE file
permissions:
On Unix platforms, the default
permissions on the utlfiles that are created through the UTL_FILE
package is Read and write for the software owner, Read for the group
and read for everyone else (rw-r--r--), which can be overridden
by the following factors:
a) The Oracle instance owner's Unix umask since Oracle is the owner of
all the
server processes and it is the server process that writes the
UTL_FILE
output file.
b) For network connections, permissions are set by umask of the user
that starts
the listener process. The Listener spawns a server process
which inherits the
listener's userid.
To give some high level
information on UMASK, the value for UMASK is derived by subtracting
the permission attribute value that you want the file to have from
777. For example, If you want the files created by the executables to
have a permission of 644 (rw-r--r--) then the UMASK value is 644
subtracted from 777 (777 - 644) which will be 133.
|
