Reading Text Files using Oracle PL/SQL and UTL_FILE (Page 1 of 4 )
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.
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.
Next: How to read a text file from a file system and display the contents using PL/SQL >>
More Oracle Articles
More By Jagadish Chatarji
