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.

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.

{mospagebreak title=How to read a text file from a file system and display the contents using PL/SQL}

In this section, we shall look at reading a text file from a host operating system.  Please understand that I am giving examples based on Windows only.  The paths would be a bit different if you use any non-Windows environment.

The first step we need to perform is nothing but letting the Oracle database know on which directory we are working (reading or writing files only from that directory).  This can be achieved using the following command:

CREATE DIRECTORY sampledata AS ‘c:sampledata’;

The above command creates a new directory entry into the Oracle database with a logical name to the directory, “SAMPLEDATA” (pointing to the path “c:sampledata”).  To execute the above command, you need to have certain privileges.  I suggest you log in using “SYSTEM” user (or contact your database administrator on this issue).  If you wanted to list all the directories registered, you can use the following command:

SELECT * FROM ALL_DIRECTORIES;

The second step we need to perform is providing necessary permissions to the user to work with that directory.  This can be achieved with something like the following:

GRANT read, write ON DIRECTORY sampledata TO PUBLIC;

The above step makes sure that the directory “sampledata” (or “c:sampledata”) is accessible to every user (PUBLIC).  It is not at all a good idea to provide grants to the PUBLIC.  I suggest you provide the grants only to the required users (who really need them).

The third step we need to perform is simply creating a new text file within the folder “c:sampledata”.  I hope you can do it yourself.

The last and final step is trying to read the file using PL/SQL.  Open your SQL*PLUS environment and copy the following code:

declare
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen(‘SAMPLEDATA’,’sample1.txt’,’R’);
utl_file.get_line(f,s);
utl_file.fclose(f);
dbms_output.put_line(s);
end;
/

Before executing the above program, make sure that you execute the statement “SET SERVEROUTPUT ON” (so that the result gets displayed on the screen).  Once you complete all of the above steps, it should run successfully, retrieving the first line of the text file. 

The next section gives you the explanation of the previous PL/SQL program in detail.

{mospagebreak title=How to read a text file from a file system and display the contents using PL/SQL: discussion}

In this section, I shall explain the PL/SQL program given in the previous section.  Let us go through it bit by bit:

f utl_file.file_type;

The above statement creates a simple variable “f” (can also be called the file handle), which is based on the data type “utl_file.file_type”.  This is not a simple data type like integer or varchar.  It has its own in-depth meaning. 

“UTL_FILE” is a built-in package, which comes with the Oracle database installation.  Any package in Oracle (either user-defined or built-in) contains one or more members in it.  These members (together as a unit) are generally used to constitute a complete system to facilitate a particular feature.  Similarly, “UTL_FILE” is also a package which has several members to facilitate use of the features of accessing file system.

“utl_file.file_type” is a different data type which points to a particular file specified by the user.  Once it successfully points, we can proceed with other operations like reading, writing, and so on.  Further proceeding, we have the following:

s varchar2(200);

I declared a simple variable “s” to simply store a line (max. 200 chars) of information from the text file.  Further we have the following:

f := utl_file.fopen(‘SAMPLEDATA’,’sample1.txt’,’R’);

The above statement is the key to the whole program.  It simply opens the file “sample1.txt” available in the “SAMPLEDATA” directory in readonly (“R”) mode.  Once PL/SQL opens the file, the handle to that file gets stored in the variable “f”.  Be aware that “SAMPLEDATA” is the logical directory name as discussed in the previous section (and not the physical directory existing in your operating system).  Further proceeding we have the following:

utl_file.get_line(f,s);

The above line simply tries to read the first line available in the file (where “f” is pointing to) and places the line in “s” (declared previously).  Once we finish all the operations with a file, we need to close it and release any memory resources (like file handles and more).  The same is achieved using the following:

utl_file.fclose(f);

It is highly recommended (in fact compulsory) that you close any file after finishing our operations (even though it doesn’t issue any error when not closed).  And finally, we display the output back to screen using the following statement:

dbms_output.put_line(s);

{mospagebreak title=Improving it a bit with exception handling}

In the previous sections, we worked by reading a single line from the text file.  Let us consider the following situations:

  • The file you are trying to access doesn’t exist.
  • The file you are trying to access got corrupted.
  • You don’t have the permission to access the file.
  • No lines present in that file.
  • Too many chars (say more than 200) present on the first line itself.

And so on.  How do we solve such problems?  Even though we will not be able to address every possible error, there exist some pre-defined exceptions to handle some of the scenarios.  Following are the pre-defined exceptions most frequently used when working with UTL_FILE package:

INVALID_PATH
INVALID_MODE
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
WRITE_ERROR
INTERNAL_ERROR
NO_DATA_FOUND
VALUE_ERROR
INVALID_MAXLINESIZE

For a complete list of exceptions, I suggest you go through the Oracle documentation for the respective version you are using.

If you are new to exception handling, I suggest you read my contributions at

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Predefined-Exceptions/

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-User-
defined-Exceptions-Nested-Blocks/

My upcoming article will address further issues like “working with a larger number of lines”,  “better exception handling”, “writing to files” and so on. I may also introduce you to some of the professional ways to deal the same issues.  Don’t forget to keep an eye on this website for the next article (or simply sign up for a newsletter).  Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye