Definition:
Notes
In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file
operations (read and write) in conjunction with the underlying operating
system. UTL_FILE works for both server and client machine systems. A
directory has to be created on the server, which points to the target file. For
the files located on the server machine, the actual path can be given while
creating the directory. For the files which are located on the client machines,
however, the relative path is required along with the client machine name. In
addition, the relative file path must be in shared mode with read and write
access for the required users. A DBA must create the directory and then grant
Read/Write access to the required users.
In earlier versions of Oracle, the parameter UTL_FILE_DIR was used to
specify the file location path. This parameter is now deprecated and directory
creation method is recommended.
UTL_FILE
package installation:
SYSDBA
Role: Run below scripts to create
UTL_FILE package using SYSDBA privilege
$ORACLE_HOME\RDBMS\ADMIN\utlfile.sql
$ORACLE_HOME\RDBMS\ADMIN\prvtfile.plb
ORA-29280: invalid directory path:
This indicates that the Oracle engine is unable
to find the file location specified in utl_file.fopen.
Diagnosis
and fix:
1.
UTL_FILE_DIR : Set the utl_file_dir
initialization parameter and check the value
SQL> SELECT value
FROM
V$PARAMETER
WHERE NAME
= 'utl_file_dir';
VALUE
--------------------------------------------------------------------------------
Or
show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
utl_file_dir string
If value is null than fix that data
SQL> alter system set utl_file_dir='C:\TEMP'
scope = spfile;
As it is static parameter we must bounce the database:
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup open
ORACLE
instance started.
2.
DIRECTORY: In the below
example, SYSDBA creates a directory MYDIR and grants R/W access to the user HR.
The user then creates a text file in the directory and writes a text into it.
SQL> CREATE DIRECTORY MYDIR AS 'C:\TEMP';
Grant
read and write privilege to the application user (or PUBLIC) on the new
directory.
SQL> GRANT READ, WRITE ON DIRECTORY
MYDIR TO HR;
SQL> GRANT READ, WRITE ON DIRECTORY
MYDIR TO public;
Note: The
directory path can be case-sensitive on some operating systems like UNIX.
A
typical mistake is to specify a *local* machine folder as the UTL_FILE directory
when the Oracle database server is on another machine – this will not work. The
UTL_FILE directory must exist on the *server* machine.
Identify the file location:
SQL> select directory_name, directory_path from
all_directories;
Check if ‘file location’ in the
script has write permissions for the logged in user
SQL> select grantee,privilege from
all_tab_privs where table_name = 'MYDIR';
If you do not
see WRITE permission granted to PUBLIC or to the logged in user, login as SYS
and grant permissions.
SQL> select grantee from
all_tab_privs where table_name = 'UTL_FILE';
A directory
object is a database object, and database object names are in UPPERCASE by
default. Even if the ‘create directory’ command issued had the directory name
in lowercase, unless we put it within quotes, the directory name will be stored
in UPPERCASE.
Notes
Before Oracle 9i, init.ora parameter called utl_file_dir was used in place of
Oracle directories to specify the UTL_FILE
file location.
utl_file_dir has been deprecated 9i onwards. Oracle recommends using Oracle
directories which are more secure, easier to handle and do not require a
database restart when created/modified.
HR User:
declare
csv_fh utl_file.file_type;
begin
csv_fh := utl_file.fopen('MYDIR', 'data_export.csv', 'W');
for r in ( select
e.first_name,d.department_name
from hr.EMPLOYEES e
join hr.DEPARTMENTS d on ( e.department_id=d.department_id )
) loop
utl_file.put_line(csv_fh,
r.first_name||'|'||r.department_name);
end loop;
utl_file.fclose(csv_fh);
end;
/
I hope you all have enjoyed reading this article.
Comments are welcome....
