Home arrow Oracle arrow Reading Text Files using Oracle PL/SQL and UTL_FILE

Reading Text Files using Oracle PL/SQL and UTL_FILE

In this series of articles, we shall concentrate on working with file input/output using Oracle PL/SQL packages. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of the most advanced techniques using file input/output with Oracle PL/SQL.

  1. Reading Text Files using Oracle PL/SQL and UTL_FILE
  2. How to read a text file from a file system and display the contents using PL/SQL
  3. How to read a text file from a file system and display the contents using PL/SQL: discussion
  4. Improving it a bit with exception handling
By: Jagadish Chatarji
Rating: starstarstarstarstar / 129
April 11, 2006

print this article



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.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: