Reading Text Files using Oracle PL/SQL and UTL_FILE

This will be an add-on to my long PL/SQL series on this website.  If you are new to PL/SQL, I strongly suggest you go through my huge "Database interaction with PL/SQL" series.

All the examples in this article have been tested only with Oracle 10g version 2 on Windows Server 2003 standard edition.  I didn't really test the examples on any of the previous Oracle versions.  The examples will be a bit different when you work with non-Windows environments.  I suggest you refer to your DBA or manuals for the specific platform on which you are going to work.

I am confident enough that all of the examples should give no problem when worked with Oracle version 7.3 or above (on Windows).  You can drop (or post) me a line if any of them do give you problems.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any changes.

What is necessary to work with a file system using Oracle PL/SQL

Reading files from a host operating system is allowed only from Oracle 7.3 or above.  To accomplish tasks related to reading and writing files to the host operating system, Oracle includes a pre-defined package called "UTL_FILE."

"UTL_FILE" allows PL/SQL programs to both read and write to any operating system files that are accessible from the server on which your database instance is running.  This package gets automatically installed when you install Oracle database on your system. 

What do we need to do before working with "UTL_FILE"?  Earlier, we need to work with an entry "UTL_FILE_DIR" in "init.ora" file which resides in the Oracle installed folder.  That entry needs to be added to the "init.ora" with the directory you will use to access the files.  An example would be as follows:

utl_file_dir = c:sampledata

The above statement makes Oracle use "c:sampledata" for reading and writing operating system files.  Once you make any change to "init.ora", you may need to restart your database server (unless you use SPFILE).

If you want to give the permission to any folder on your host operating system, you need to modify the line something like below line:

utl_file_dir = *

Please be aware that the above line would be a very huge security risk on production servers.  It suits well only for development purposes.

There exists one more method which is quite safe and easy.  Oracle highly recommends that you work with the "CREATE DIRECTORY" method rather than using the previous method.  Another advantage to using this method is that it is dynamic (you need not restart your server at all).  So, in this article, I shall proceed with the "CREATE DIRECTORY" method.

The next section gives you a demo.