Oracle : Create File using PLSQL (UTL_FILE)

Definition:

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....