6.2.1.1 UTL_FILE programs

Table 6-2 shows the UTL_FILE program names and descriptions.

Table 6.2: UTL_FILE Programs

Name	Description	Use in SQL
FCLOSE	Closes the specified files	No
FCLOSE_ALL	Closes all open files	No
FFLUSH	Flushes all the data from the UTL_FILE buffer	No
FOPEN	Opens the specified file	No
GET_LINE	Gets the next line from the file	No
IS_OPEN	Returns TRUE if the file is already open	No
NEW_LINE	Inserts a newline mark in the file at the end of the current line	No
PUT	Puts text into the buffer	No
PUT_LINE	Puts a line of text into the file	No
PUTF	Puts formatted text into the buffer	No

6.2.1.2 Trying out UTL_FILE

Just getting to the point where your first call to UTL_FILE's FOPEN function works can actually be a pretty frustrating experience. Here's how it usually goes.

You read about UTL_FILE and you are excited. So you dash headlong into writing some code like this:

DECLARE
   config_file UTL_FILE.FILE_TYPE;
BEGIN
   config_file := UTL_FILE.FOPEN ('/tmp', 'newdata.txt', 'W');

   ... lots of write operations ...

   ... and no exception section ...
END;
/

and then this is all you get from your "quick and dirty script" in SQL*Plus:

SQL> @writefile.sql
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 91
ORA-06512: at "SYS.UTL_FILE", line 146
ORA-06512: at line 4

What is going wrong? This error message certainly provides little or no useful information. So you go back to the documentation, thoroughly chastened, and (over time) discover the following:

• You need to modify the INIT.ORA parameter initialization file of your instance. You will have to contact your database administrator and have him or her make the changes (if willing) and then "bounce" the database.

• You need to get the format of the parameter entries correct. That alone used to take me days!

• You need to add exception sections to your programs to give yourself a fighting chance at figuring out what is going on.

I hope that the information in this chapter will help you avoid most, if not all, of these frustrations and gotchas. But don't give up! This package is well worth the effort.

6.2.1.3 File security

UTL_FILE lets you read and write files accessible from the server on which your database is running. So you could theoretically use UTL_FILE to write right over your tablespace data files, control files, and so on. That is of course a very bad idea. Server security requires the ability to place restrictions on where you can read and write your files.

UTL_FILE implements this security by limiting access to files that reside in one of the directories specified in the INIT.ORA file for the database instance on which UTL_FILE is running.

When you call FOPEN to open a file, you must specify both the location and the name of the file, in separate arguments. This file location is then checked against the list of accessible directories.

Here's the format of the parameter for file access in the INIT.ORA file:

utl_file_dir = <directory>

Include a parameter for utl_file_dir for each directory you want to make accessible for UTL_FILE operations. The following entries, for example, enable four different directories in UNIX:

utl_file_dir = /tmp
utl_file_dir = /ora_apps/hr/time_reporting
utl_file_dir = /ora_apps/hr/time_reporting/log
utl_file_dir = /users/test_area

To bypass server security and allow read/write access to all directories, you can use this special syntax:

utl_file_dir = *

You should not use this option on production systems. In a development system, this entry certainly makes it easier for developers to get up and running on UTL_FILE and test their code. However, you should allow access to only a few specific directories when you move the application to production.

Some observations on working with and setting up accessible directories with UTL_FILE follow:

• Access is not recursive through subdirectories. If the following lines were in your INIT.ORA file, for example,

  	utl_file_dir = c:\group\dev1
  	utl_file_dir = c:\group\prod\oe
  	utl_file_dir = c:\group\prod\ar

  then you would not be able to open a file in the c:\group\prod\oe\reports subdirectory.

• Do not include the following entry in UNIX systems:

  	utl_file_dir = .

  This would allow you to read/write on the current directory in the operating system.

• Do not enclose the directory names within single or double quotes.

• In the UNIX environment, a file created by FOPEN has as its owner the shadow process running the Oracle instance. This is usually the "oracle" owner. If you try to access these files outside of UTL_FILE, you will need the correct privileges (or be logged in as "oracle") to access or change these files.

• You should not end your directory name with a delimiter, such as the forward slash in UNIX. The following specification of a directory will result in problems when trying to read from or write to the directory:

  	utl_file_dir = /tmp/orafiles/

6.2.1.4 Specifying file locations

The location of the file is an operating system-specific string that specifies the directory or area in which to open the file. The location you provide must have been listed as an accessible directory in the INIT.ORA file for the database instance.

The INIT.ORA location is a valid directory or area specification, as shown in these examples:

• In Windows NT:

  	'k:\common\debug'

• In UNIX:

  	'/usr/od2000/admin'

Notice that in Windows NT, the backslash character (\) is used as a delimiter. In UNIX, the forward slash (/) is the delimiter. When you pass the location in the call to UTL_FILE.FOPEN, you provide the location specification as it appears in the INIT.ORA file (unless you just provided * for all directories in the initialization file). And remember that in case-sensitive operating systems, the case of the location specification in the initialization file must match that used in the call to UTL_FILE.FOPEN.

Here are some examples:

• In Windows NT:

  	file_id := UTL_FILE.FOPEN ('k:\common\debug', 'trace.lis', 'R');

• In UNIX:

  	file_id := UTL_FILE.FOPEN ('/usr/od2000/admin', 'trace.lis', 'W');

Your location must be an explicit, complete path to the file. You cannot use operating system-specific parameters such as environment variables in UNIX to specify file locations.

6.2.1.5 UTL_FILE exceptions

The package specification of UTL_FILE defines seven exceptions. The cause behind a UTL_FILE exception can often be difficult to understand. Here are the explanations Oracle provides for each of the exceptions:

NOTE: As a result of the way these exceptions are declared (as "user-defined exceptions"), there is no error number associated with any of the exceptions. Thus you must include explicit exception handlers in programs that call UTL_FILE if you wish to find out which error was raised. See the section Section 6.2.6.1, "Handling file I/O errors" " for more details on this process.

INVALID_PATH

The file location or the filename is invalid. Perhaps the directory is not listed as a utl_file_dir parameter in the INIT.ORA file (or doesn't exist as all), or you are trying to read a file and it does not exist.

INVALID_MODE

The value you provided for the open_mode parameter in UTL_FILE.FOPEN was invalid. It must be "A," "R," or "W."

INVALID_FILEHANDLE

The file handle you passed to a UTL_FILE program was invalid. You must call UTL_FILE.FOPEN to obtain a valid file handle.

INVALID_OPERATION

UTL_FILE could not open or operate on the file as requested. For example, if you try to write to a read-only file, you will raise this exception.

READ_ERROR

The operating system returned an error when you tried to read from the file. (This does not occur very often.)

WRITE_ERROR

The operating system returned an error when you tried to write to the file. (This does not occur very often.)

INTERNAL_ERROR

Uh-oh. Something went wrong and the PL/SQL runtime engine couldn't assign blame to any of the previous exceptions. Better call Oracle Support!

Programs in UTL_FILE may also raise the following standard system exceptions:

NO_DATA_FOUND

Raised when you read past the end of the file with UTL_FILE.GET_LINE.

VALUE_ERROR

Raised when you try to read or write lines in the file which are too long. The current implementation of UTL_FILE limits the size of a line read by UTL_FILE.GET_LINE to 1022 bytes.

INVALID_MAXLINESIZE

Oracle 8.0 and above: raised when you try to open a file with a maximum linesize outside of the valid range (between 1 through 32767).

In the following descriptions of the UTL_FILE programs, I list the exceptions that can be raised by each individual program.

6.2.1.6 UTL_FILE nonprogram elements

When you open a file, PL/SQL returns a handle to that file for use within your program. This handle has a datatype of UTL_FILE.FILE_TYPE currently defined as the following:

TYPE UTL_FILE.FILE_TYPE IS RECORD (id BINARY_INTEGER);

As you can see, UTL_FILE.FILE_TYPE is actually a PL/SQL record whose fields contain all the information about the file needed by UTL_FILE. However, this information is for use only by the UTL_FILE package. You will reference the handle, but not any of the individual fields of the handle. (The fields of this record may expand over time as UTL_FILE becomes more sophisticated.)

Here is an example of how to declare a local file handle based on this type:

DECLARE
   file_handle UTL_FILE.FILE_TYPE;
BEGIN
   ...

6.2.1.7 UTL_FILE restrictions and limitations

While UTL_FILE certainly extends the usefulness of PL/SQL, it does have its drawbacks, including:

• Prior to Oracle 8.0, you cannot read or write a line of text with more than 1023 bytes. In Oracle 8.0 and above, you can specify a maximum line size of up to 32767 when you open a file..

• You cannot delete files through UTL_FILE. The best you can do is empty a file, but it will still be present on the disk.

• You cannot rename files. The best you can do is copy the contents of the file to another file with that new name.

• You do not have random access to lines in a file. If you want to read the 55th line, you must read through the first 54 lines. If you want to insert a line of text between the 1,267th and 1,268th lines, you will have to (a) read those 1,267 lines, (b) write them to a new file, (c) write the inserted line of text, and (d) read/write the remainder of the file. Ugh.

• You cannot change the security on files through UTL_FILE.

• You cannot access mapped files. Generally, you will need to supply real directory locations for files if you want to read from or write to them.

You are probably getting the idea. UTL_FILE is a basic facility for reading and writing server-side files. Working with UTL_FILE is not always pretty, but you can usually get what you need done with a little or a lot of code.

6.2.1.8 The UTL_FILE process flow

The following sections describe each of the UTL_FILE programs, following the process flow for working with files. That flow is described for both writing and reading files.

In order to write to a file you will (in most cases) perform the following steps:

1. Declare a file handle. This handle serves as a pointer to the file for subsequent calls to programs in the UTL_FILE package to manipulate the contents of this file.

2. Open the file with a call to FOPEN, which returns a file handle to the file. You can open a file to read, replace, or append text.

3. Write data to the file using the PUT, PUTF, or PUT_LINE procedures.

4. Close the file with a call to FCLOSE. This releases resources associated with the file.

To read data from a file you will (in most cases) perform the following steps:

1. Declare a file handle.

2. Declare a VARCHAR2 string buffer that will receive the line of data from the file. You can also read directly from a file into a numeric or date buffer. In this case, the data in the file will be converted implicitly, and so it must be compatible with the datatype of the buffer.

3. Open the file using FOPEN in read mode.

4. Use the GET_LINE procedure to read data from the file and into the buffer. To read all the lines from a file, you would execute GET_LINE in a loop.

5. Close the file with a call to FCLOSE.

6.2.2.1 The UTL_FILE.FOPEN function

The FOPEN function opens the specified file and returns a file handle that you can then use to manipulate the file. Here's the header for the function:

 All PL/SQL versions:           Oracle 8.0 and above only:
 FUNCTION UTL_FILE.FOPEN (      FUNCTION UTL_FILE.FOPEN (
    location     IN VARCHAR2,      location     IN VARCHAR2,
    filename     IN VARCHAR2,      filename     IN VARCHAR2,
    open_mode    IN VARCHAR2)      open_mode    IN VARCHAR2,
 RETURN file_type;                 max_linesize IN BINARY_INTEGER)
                                RETURN file_type;

Parameters are summarized in the following table.

Parameter	Description
location	Location of the file
filename	Name of the file
openmode	Mode in which the file is to be opened (see the following modes)
max_linesize	The maximum number of characters per line, including the newline character, for this file. Minimum is 1, maximum is 32767

You can open the file in one of three modes:

R

Open the file read-only. If you use this mode, use UTL_FILE's GET_LINE procedure to read from the file.

W

Open the file to read and write in replace mode. When you open in replace mode, all existing lines in the file are removed. If you use this mode, then you can use any of the following UTL_FILE programs to modify the file: PUT, PUT_LINE, NEW_LINE, PUTF, and FFLUSH.

A

Open the file to read and write in append mode. When you open in append mode, all existing lines in the file are kept intact. New lines will be appended after the last line in the file. If you use this mode, then you can use any of the following UTL_FILE programs to modify the file: PUT, PUT_LINE, NEW_LINE, PUTF, and fFFLUSH.

Keep the following points in mind as you attempt to open files:

• The file location and the filename joined together must represent a legal filename on your operating system.

• The file location specified must be accessible and must already exist; FOPEN will not create a directory or subdirectory for you in order to write a new file, for example.

• If you want to open a file for read access, the file must already exist. If you want to open a file for write access, the file will either be created, if it does not exist, or emptied of all its contents, if it does exist.

• If you try to open with append, the file must already exist. UTL_FILE will not treat your append request like a write access request. If the file is not present, UTL_FILE will raise the INVALID_OPERATION exception.

6.2.2.2 The UTL_FILE.IS_OPEN function

The IS_OPEN function returns TRUE if the specified handle points to a file that is already open. Otherwise, it returns false. The header for the function is:

FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;

where file is the file to be checked.

Within the context of UTL_FILE, it is important to know what this means. The IS_OPEN function does not perform any operating system checks on the status of the file. In actuality, it merely checks to see if the id field of the file handle record is not NULL. If you don't play around with these records and their contents, then this id field is only set to a non-NULL value when you call FOPEN. It is set back to NULL when you call FCLOSE.

6.2.3.1 The UTL_FILE.GET_LINE procedure

The GET_LINE procedure reads a line of data from the specified file, if it is open, into the provided line buffer. Here's the header for the procedure:

PROCEDURE UTL_FILE.GET_LINE 
   (file IN UTL_FILE.FILE_TYPE, 
    buffer OUT VARCHAR2);

Parameters are summarized in the following table.

Parameter	Description
file	The file handle returned by a call to FOPEN
buffer	The buffer into which the line of data is read

The variable specified for the buffer parameter must be large enough to hold all the data up to the next carriage return or end-of-file condition in the file. If not, PL/SQL will raise the VALUE_ERROR exception. The line terminator character is not included in the string passed into the buffer.

6.2.4.1 The UTL_FILE.PUT procedure

The PUT procedure puts data out to the specified open file. Here's the header for this procedure:

PROCEDURE UTL_FILE.PUT 
    (file IN UTL_FILE.FILE_TYPE, 
    buffer OUT VARCHAR2);

Parameters are summarized in the following table.

Parameter	Description
file	The file handle returned by a call to FOPEN
buffer	The buffer containing the text to be written to the file; maximum size allowed is 32K for Oracle 8.0.3 and above; for earlier versions, it is 1023 bytes

The PUT procedure adds the data to the current line in the opened file, but does not append a line terminator. You must use the NEW_LINE procedure to terminate the current line or use PUT_LINE to write out a complete line with a line termination character.

6.2.4.2 The UTL_FILE.NEW_LINE procedure

The NEW_LINE procedure inserts one or more newline characters in the specified file. Here's the header for the procedure:

PROCEDURE UTL_FILE.NEW_LINE 
   (file IN UTL_FILE.FILE_TYPE, 
    lines IN NATURAL := 1); 

Parameters are summarized in the following table.

Parameter	Description
file	The file handle returned by a call to FOPEN
lines	Number of lines to be inserted into the file

If you do not specify a number of lines, NEW_LINE uses the default value of 1, which places a newline character ( carriage return) at the end of the current line. So if you want to insert a blank line in your file, execute the following call to NEW_LINE:

UTL_FILE.NEW_LINE (my_file, 2);

If you pass 0 or a negative number for lines, nothing is written into the file.

6.2.4.3 The UTL_FILE. PUT_LINE procedure

This procedure writes data to a file and then immediately appends a newline character after the text. Here's the header for PUT_LINE:

PROCEDURE UTL_FILE.PUT_LINE 
    (file IN UTL_FILE.FILE_TYPE, 
    buffer IN VARCHAR2);

Parameters are summarized in the following table.

Parameter	Description
file	The file handle returned by a call to FOPEN
buffer	Text to be written to the file; maximum size allowed is 32K for Oracle 8.0. 3 and above; for earlier versions, it is 1023 bytes

Before you can call UTL_FILE.PUT_LINE, you must have already opened the file.

6.2.4.4 The UTL_FILE.PUTF procedure

Like PUT, PUTF puts data into a file, but it uses a message format (hence, the "F" in "PUTF") to interpret the different elements to be placed in the file. You can pass between one and five different items of data to PUTF. Here's the specification:

PROCEDURE UTL_FILE.PUTF
    (file IN FILE_TYPE
    ,format IN VARCHAR2
    ,arg1 IN VARCHAR2 DEFAULT NULL
    ,arg2 IN VARCHAR2 DEFAULT NULL
    ,arg3 IN VARCHAR2 DEFAULT NULL
    ,arg4 IN VARCHAR2 DEFAULT NULL
    ,arg5 IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

Parameter	Description
file	The file handle returned by a call to FOPEN
format	The string that determines the format of the items in the file; see the following options
argN	An optional argument string; up to five may be specified

The format string allows you to substitute the argN values directly into the text written to the file. In addition to "boilerplate" or literal text, the format string may contain the following patterns:

%s

Directs PUTF to put the corresponding item in the file. You can have up to five %s patterns in the format string, since PUTF will take up to five items.

\n

Directs PUTF to put a newline character in the file. There is no limit to the number of \n patterns you may include in a format string.

The %s formatters are replaced by the argument strings in the order provided. If you do not pass in enough values to replace all of the formatters, then the %s is simply removed from the string before writing it to the file.

6.2.4.5 The UTL_FILE. FFLUSH procedure

This procedure makes sure that all pending data for the specified file is written physically out to a file. The header for FFLUSH is,

PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);

where file is the file handle.

Your operating system probably buffers physical I/O to improve performance. As a consequence, your program may have called one of the "put" procedures, but when you look at the file, you won't see your data. UTL_FILE.FFLUSH comes in handy when you want to read the contents of a file before you have closed that file. Typical scenarios include analyzing execution trace and debugging logs.

6.2.5.1 The UTL_FILE. FCLOSE procedure

Use FCLOSE to close an open file. The header for this procedure is,

PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);

where file is the file handle.

Notice that the argument to UTL_FILE.FCLOSE is an IN OUT parameter, because the procedure sets the id field of the record to NULL after the file is closed.

If there is buffered data that has not yet been written to the file when you try to close it, UTL_FILE will raise the WRITE_ERROR exception.

6.2.5.2 The UTL_FILE. FCLOSE_ALL procedure

FCLOSE_ALL closes all of the opened files. The header for this procedure follows:

PROCEDURE UTL_FILE.FCLOSE_ALL;

This procedure will come in handy when you have opened a variety of files and want to make sure that none of them are left open when your program terminates.

In programs in which files have been opened, you should also call FCLOSE_ALL in exception handlers in programs. If there is an abnormal termination of the program, files will then still be closed.

EXCEPTION
   WHEN OTHERS
   
THEN
      UTL_FILE.FCLOSE_ALL;
      ... other clean up activities ...
END;

NOTE: When you close your files with the FCLOSE_ALL procedure, none of your file handles will be marked as closed (the id field, in other words, will still be non-NULL). The result is that any calls to IS_OPEN for those file handles will still return TRUE. You will not, however, be able to perform any read or write operations on those files (unless you reopen them).

6.2.6.1 Handling file I/O errors

You may encounter a number of difficulties (and therefore raise exceptions) when working with operating system files. The good news is that Oracle has predefined a set of exceptions specific to the UTL_FILE package, such as UTL_FILE.INVALID_FILEHANDLE. The bad news is that these are all "user-defined exceptions," meaning that if you call SQLCODE to see what the error is, you get a value of 1, regardless of the exception. And a call to SQLERRM returns the less-than-useful string "User-Defined Exception."

To understand the problems this causes, consider the following program:

PROCEDURE file_action 
IS
   fileID UTL_FILE.FILE_TYPE;
BEGIN
   fileID := UTL_FILE.FOPEN ('c:/tmp', 'lotsa.stf', 'R');
   UTL_FILE.PUT_LINE (fileID, 'just the beginning');
   UTL_FILE.FCLOSE (fileID);
END;

It is filled with errors, as you can see when I try to execute the program:

SQL> exec file_action
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 91
ORA-06512: at "SYS.UTL_FILE", line 146
ORA-06512: at line 4

But what error or errors? Notice that the only information you get is that it was an "unhandled user-defined exception" -- even though Oracle defined the exception!

The bottom line is that if you want to get more information out of the UTL_FILE-related errors in your code, you need to add exception handlers designed explicitly to trap UTL_FILE exceptions and tell you which one was raised. The following template exception section offers that capability. It includes an exception handler for each UTL_FILE exception. The handler writes out the name of the exception and then reraises the exception.

/* Filename on companion disk: 

fileexc.sql */*
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH
   THEN 
       DBMS_OUTPUT.PUT_LINE ('invalid_path'); RAISE;

   WHEN UTL_FILE.INVALID_MODE
   THEN 
       DBMS_OUTPUT.PUT_LINE ('invalid_mode'); RAISE;

   WHEN UTL_FILE.INVALID_FILEHANDLE
   THEN 
       DBMS_OUTPUT.PUT_LINE ('invalid_filehandle'); RAISE;

   WHEN UTL_FILE.INVALID_OPERATION
   THEN 
       DBMS_OUTPUT.PUT_LINE ('invalid_operation'); RAISE;

   WHEN UTL_FILE.READ_ERROR
   THEN  
       DBMS_OUTPUT.PUT_LINE ('read_error'); RAISE;

   WHEN UTL_FILE.WRITE_ERROR
   THEN 
      DBMS_OUTPUT.PUT_LINE ('write_error'); RAISE;

   WHEN UTL_FILE.INTERNAL_ERROR
   THEN 
      DBMS_OUTPUT.PUT_LINE ('internal_error'); RAISE;
END;

If I add this exception section to my file_action procedure, I get this message,

SQL> @temp
invalid_operation
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception

which helps me realize that I am trying to write to a read-only file. So I change the file mode to "W" and try again, only to receive the same error again! Additional analysis reveals that my file location is not valid. It should be "C:\temp" instead of "C:/tmp". So why didn't I get a UTL_FILE.INVALID_PATH exception? Who is to say? With those two changes made, file_action then ran without error.

I suggest that whenever you work with UTL_FILE programs, you include either all or the relevant part of fileexc.sql . (See each program description earlier in this chapter to find out which exceptions each program might raise.) Of course, you might want to change my template. You may not want to reraise the exception. You may want to display other information. Change whatever you need to change -- just remember the basic rule that if you don't handle the UTL_FILE exception by name in the block in which the error was raised, you won't be able to tell what went wrong.

6.2.6.2 Closing unclosed files

As a corollary to the last section on handling I/O errors, you must be very careful to close files when you are done working with them, or when errors occur in your program. If not, you may sometimes have to resort to UTL_FILE.FCLOSE_ALL to close all your files before you can get your programs to work properly.

Suppose you open a file (and get a handle to that file) and then your program hits an error and fails. Suppose further that you do not have an exception section, so the program simply fails. So let's say that you fix the bug and rerun the program. Now it fails with UTL_FILE.INVALID_OPERATION. The problem is that your file is still open -- and you have lost the handle to the file, so you cannot explicitly close just that one file.

Instead, you must now issue this command (here, from SQL*Plus):

SQL> exec UTL_FILE.FCLOSE_ALL

With any luck, you won't close files that you wanted to be left open in your session. As a consequence, I recommend that you always include calls to UTL_FILE.FCLOSE in each of your exception sections to avoid the need to call FCLOSE_ALL and to minimize extraneous INVALID_OPERATION exceptions.

Here is the kind of exception section you should consider including in your programs. (I use the PLVexc.recNstop handler from PL/Vision as an example of a high-level program to handle exceptions, in this case requesting that the program "record and then stop.")

EXCEPTION
   WHEN OTHRES
   THEN
      UTL_FILE.FCLOSE (ini_fileID);
      UTL_FILE.FCLOSE (new_fileID);
      PLVexc.recNstop;
END;

In other words, I close the two files I've been working with, and then handle the exception.

6.2.6.3 Combining locations and filenames

I wonder if anyone else out there in the PL/SQL world finds UTL_FILE as frustrating as I do. I am happy that Oracle built the package, but I sure wish they'd given us more to work with. I am bothered by these things:

• The need to separate my filename from the location. Most of the time when I work with files, those two pieces are stuck together. With UTL_FILE, I have to split them apart.

• The lack of support for paths. It would be nice to not have to provide a file location and just let UTL_FILE find my file for me.

This section shows you how to enhance UTL_FILE to allow you to pass in a "combo" filename: location and name joined together, as we so often encounter them. The next section explains the steps for adding path support to your manipulation of files with UTL_FILE.

If you are going to specify your file specification (location and name) in one string, what is the minimum information needed in order to separate these two elements to pass to FOPEN? The delimiter used to separate directories from filenames. In DOS (and Windows) that delimiter is "\". In UNIX it is "/". In VAX/VMS it is "]". Seems to me that I just have to find the last occurrence of this delimiter in your string and that will tell me where to break apart the string.

So to allow you to get around splitting up your file specification in your call to FOPEN, I can do the following:

• Give you a way to tell me in advance the operating system delimiter for directories -- and store that value for use in future attempts to open files.

• Offer you a substitute FOPEN procedure that uses that delimiter.

Since I want to store that value for your entire session, I will need a package. (You can also use a database table so that you do not have to specify this value each time you start up your application.) Here is the specification:

/* Filename on companion disk: 

onestring.spp */*
CREATE OR REPLACE PACKAGE 

fileIO
IS
   PROCEDURE setsepchar (str IN VARCHAR2);
   FUNCTION sepchar RETURN VARCHAR2;

   FUNCTION open (file IN VARCHAR2, filemode IN VARCHAR2)
      RETURN UTL_FILE.FILE_TYPE;
END;
/

In other words, I set the separation character or delimiter with a call to fileIO.setsepchar, and I can retrieve the current value with a call to the fileIO.sepchar function. Once I have that value, I can call fileIO.open to open a file without having to split apart the location and name. I show an example of this program in use here:

DECLARE
   fid UTL_FILE.FILE_TYPE;
BEGIN
   fileIO.setsepchar ('\');
   fid := fileio.open ('c:\temp\newone.txt', 'w'));
END;
/

The body of this package is quite straightforward:

CREATE OR REPLACE PACKAGE BODY fileIO
IS
   g_sepchar CHAR(1) := '/'; /* Unix is, after all, dominant. */

   PROCEDURE 

setsepchar (str IN VARCHAR2)
   IS
   BEGIN
      g_sepchar := NVL (str, '/');
   END;

   FUNCTION

 sepchar RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_sepchar;
   END;

   FUNCTION 

open (file IN VARCHAR2, filemode IN VARCHAR2)
      RETURN UTL_FILE.FILE_TYPE
   IS
      v_loc PLS_INTEGER := INSTR (file, g_sepchar, -1);
      retval UTL_FILE.FILE_TYPE;
   BEGIN
      RETURN UTL_FILE.FOPEN 
         (SUBSTR (file, 1, v_loc-1),
          SUBSTR (file, v_loc+1),
          filemode);
   END;
END;
/

Notice that when I call INSTR I pass -1 for the third argument. This negative value tells the built-in to scan from the end of string backwards to the first occurrence of the specified character.

6.2.6.4 Adding support for paths

Why should I have to provide the directory name for my file each time I call FOPEN to read that file? It would be so much easier to specify a path, a list of possible directories, and then just let UTL_FILE scan the different directories in the specified order until the file is found.

Even though the notion of a path is not built into UTL_FILE, it is easy to add this feature. The structure of the implementation is very similar to the package built to combine file locations and names. I will need a package to receive and store the path, or list of directories. I will need an alternative open procedure that uses the path instead of a provided location. Here is the package specification:

/* Filename on companion disk:

 filepath.spp */*
CREATE OR REPLACE PACKAGE

 fileIO
IS
   c_delim CHAR(1) := ';';

   PROCEDURE setpath (str IN VARCHAR2);
   FUNCTION path RETURN VARCHAR2;

   FUNCTION open (file IN VARCHAR2, filemode IN VARCHAR2) 
      RETURN UTL_FILE.FILE_TYPE;
END;
/

I define the path delimiter as a constant so that a user of the package can see what he should use to separate different directories in his path. I provide a procedure to set the path and a function to get the path -- but the variable containing the path is hidden away in the package body to protect its integrity.

Before exploring the implementation of this package, let's see how you would use these programs. The following test script sets a path with two directories and then displays the first line of code in the file containing the previous package:

/* Filename on companion disk:

 filepath.tst */*
DECLARE
   fID UTL_FILE.FILE_TYPE;
   v_line VARCHAR2(2000);
BEGIN
   fileio.setpath ('c:\temp;d:\oreilly\builtins\code');
   fID := fileIO.open ('filepath.spp');
   UTL_FILE.GET_LINE (fID, v_line);
   DBMS_OUTPUT.PUT_LINE (v_line);
   UTL_FILE.FCLOSE (fID);
END;
/

I include a trace message in the package (commented out on the companion disk) so that we can watch the path-based open doing its work:

SQL> @filepath.tst
...looking in c:\temp
...looking in d:\oreilly\builtins\code
CREATE OR REPLACE PACKAGE fileIO

It's nice having programs do your work for you, isn't it? Here is the implementation of the fileIO package with path usage:

/* Filename on companion disk:

 filepath.spp */*
CREATE OR REPLACE PACKAGE BODY



 fileIO
IS
   g_path VARCHAR2(2000);

   PROCEDURE 

setpath (str IN VARCHAR2)
   IS 
   BEGIN
      g_path := str;
   END;

   FUNCTION 

path RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_path;
   END;

   FUNCTION

 open (file IN VARCHAR2, filemode IN VARCHAR2) 
      RETURN UTL_FILE.FILE_TYPE
   IS
      /* Location of next path separator */
      v_lastsep PLS_INTEGER := 1;
      v_sep PLS_INTEGER := INSTR (g_path, c_delim);
      v_dir VARCHAR2(500);
      retval UTL_FILE.FILE_TYPE;
   BEGIN     
      /* For each directory in the path, attempt to open the file. */ 
      LOOP
         BEGIN
            IF v_sep = 0
            THEN
               v_dir := SUBSTR (g_path, v_lastsep);
            ELSE
               v_dir := SUBSTR (g_path, v_lastsep, v_sep - v_lastsep);
            END IF;
            retval := UTL_FILE.FOPEN (v_dir, file, 'R');
            EXIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               IF v_sep = 0
               THEN
                  RAISE;
               ELSE
                  v_lastsep := v_sep + 1;
                  v_sep := INSTR (g_path, c_delim, v_sep+1); 
               END IF;
         END;
      END LOOP;
      RETURN retval;
   END;
END;
/

The logic in this fileio.open is a little bit complicated, because I need to parse the semicolon-delimited list. The v_sep variable contains the location in the path of the next delimiter. The v_lastsep variable contains the location of the last delimiter. I have to include special handling for recognizing when I am at the last directory in the path (v_sep equals 0). Notice that I do not hard-code the semi-colon into this program. Instead, I reference the c_delim constant.

The most important implementation detail is that I place the call to FOPEN inside a loop . With each iteration of the loop body, I extract a directory from the path. Once I have the next directory to search, I call the FOPEN function to see if I can read the file. If I am able to do so successfully, I will reach the next line of code inside my loop, which is an EXIT statement: I am done and can leave. This drops me down to the RETURN statement to send back the handle to the file.

If I am unable to read the file in that directory, UTL_FILE raises an exception. Notice that I have placed the entire body of my loop inside its own anonymous block. This allows me to trap the open failure and process it. If I am on my last directory (no more delimiters, as in v_sep equals 0), I will simply reraise the exception from UTL_FILE. This will cause the loop to terminate, and then end the function execution as well. Since the fileIO.open does not have its own exception section, the error will be propagated out of the function unhandled. Even with a path, I was unable to locate the file. If, however, there are more directories, I set my start and end points for the next SUBSTR from the path and go back to the top of the loop so that FOPEN can try again.

If you do decide to use utilities like the path-based open shown previously, you should consider the following:

• Combine the logic in filepath.spp with onestring.spp (a version of open that lets you pass the location and name in a single string). I should be able to override the path by providing a location; the version shown in this section assumes that the filename never has a location in it.

• Allow users to add a directory to the path without having to concatenate it to a string with a semicolon between them. Why not build a procedure called fileIO.adddir that does the work for the user and allows an application to modify the path at runtime?

6.2.6.5 You closed what?

You might run into some interesting behavior with the IS_OPEN function if you treat your file handles as variables. You are not likely to do this, but I did, so I thought I would pass on my findings to you.

In the following script, I define two file handles. I then open a file, assigning the handle record generated by FOPEN to fileID1. I immediately assign that record to fileID2. They now both have the same record contents. I then close the file by passing fileID2 to FCLOSE and check the status of the file afterwards. Finally, I assign a value of NULL to the id field of fileID1 and call IS_OPEN again.

DECLARE
   fileID1 UTL_FILE.FILE_TYPE;
   fileID2 UTL_FILE.FILE_TYPE;
BEGIN
   fileID1 := UTL_FILE.FOPEN ('c:\temp', 'newdata.txt', 'W');
   fileID2 := fileID1;
   UTL_FILE.FCLOSE (fileID2);

   IF UTL_FILE.IS_OPEN (fileid1)
   THEN
      DBMS_OUTPUT.PUT_LINE ('still open');
   END IF;

   fileid1.id := NULL;
   IF NOT UTL_FILE.IS_OPEN (fileid1)
   THEN
      DBMS_OUTPUT.PUT_LINE ('now closed');
   END IF;
END;
/

Let's run the script and check out the results:

SQL> @temp
still open
now closed

We can conclude from this test that the IS_OPEN function returns TRUE if the id field of a UTL_FILE.FILE_TYPE record is NULL. It doesn't check the status of the file with the operating system. It is a check totally internal to UTL_FILE.

This will not cause any problems as long as (a) you don't muck around with the id field of your file handle records and (b) you are consistent with your use of file handles. In other words, if you assign one file record to another, use that new record for all operations. Don't go back to using the original.

6.2.7.1 Enhancing UTL_FILE.GET_LINE

The GET_LINE procedure is simple and straightforward. It gets the next line from the file. If the pointer to the file is already located at the last line of the file, UTL_FILE.GET_LINE does not return data, but instead raises the NO_DATA_FOUND exception. Whenever you write programs using GET_LINE, you will therefore need to handle this exception. Let's explore the different ways you can do this.

The following example uses a loop to read the contents of a file into a PL/SQL table (whose type definition, tabpkg.names_tabtype, has been declared previously):

/* Filename on companion disk: 

file2tab.sp */*
CREATE OR REPLACE PACKAGE 



tabpkg 
IS
   TYPE names_tabtype IS TABLE OF VARCHAR2(100)
      INDEX BY BINARY_INTEGER;
END;
/
CREATE OR REPLACE PROCEDURE 

file_to_table 
   (loc_in IN VARCHAR2, file_in IN VARCHAR2, 
    table_in IN OUT tabpkg.names_tabtype)
IS
	/* Open file and get handle right in declaration */
	names_file UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc_in, file_in, 'R');
	/* Counter used to store the Nth name. */
	line_counter INTEGER := 1;
BEGIN
	LOOP
		UTL_FILE.GET_LINE (names_file, table_in(line_counter));
		line_counter := line_counter + 1;
	END LOOP;
EXCEPTION
	WHEN NO_DATA_FOUND
	THEN
		UTL_FILE.FCLOSE (names_file);
END;
/

The file_to_table procedure uses an infinite loop to read through the contents of the file. Notice that there is no EXIT statement within the loop to cause the loop to terminate. Instead I rely on the fact that the UTL_FILE package raises a NO_DATA_FOUND exception once it goes past the end-of-file marker and short-circuits the loop by transferring control to the exception section. The exception handler then traps that exception and closes the file.

I am not entirely comfortable with this approach. I don't like to code infinite loops without an EXIT statement; the termination condition is not structured into the loop itself. Furthermore, the end-of-file condition is not really an exception; every file, after all, must end at some point.

I believe that a better approach to handling the end-of-file condition is to build a layer of code around GET_LINE that immediately checks for end-of-file and returns a Boolean value (TRUE or FALSE). The get_nextline procedure shown here embodies this principle.

/* Filename on companion disk: g

etnext.sp */*
PROCEDURE get_nextline 
   (file_in IN UTL_FILE.FILE_TYPE, 
    line_out OUT VARCHAR2, 
    eof_out OUT BOOLEAN)
IS
BEGIN
   UTL_FILE.GET_LINE (file_in, line_out);
   eof_out := FALSE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      line_out := NULL;
      eof_out  := TRUE;
END;

The get_nextline procedure accepts an already assigned file handle and returns two pieces of information: the line of text (if there is one) and a Boolean flag (set to TRUE if the end-of-file is reached, FALSE otherwise). Using get_nextline, I can now read through a file with a loop that has an EXIT statement.

My file_to_table procedure will look like the following after adding get_nextline:

/* Filename on companion disk: f

il2tab2.sp */*
PROCEDURE 

file_to_table 
   (loc_in IN VARCHAR2, file_in IN VARCHAR2, 
   table_in IN OUT names_tabtype)
IS
   /* Open file and get handle right in declaration */
   names_file CONSTANT UTL_FILE.FILE_TYPE := 
      UTL_FILE.FOPEN (loc_in, file_in, 'R');

   /* counter used to create the Nth name. */
   line_counter INTEGER := 1;

   end_of_file BOOLEAN := FALSE;
BEGIN
   WHILE NOT end_of_file
   LOOP
      get_nextline (names_file, table_in(line_counter), end_of_file);
      line_counter := line_counter + 1;
   END LOOP;
   UTL_FILE.FCLOSE (names_file);
END;

With get_nextline, I no longer treat end-of-file as an exception. I read a line from the file until I am done, and then I close the file and exit. This is, I believe, a more straightforward and easily understood program.

6.2.7.2 Creating a file

A common way to use files does not involve the contents of the file as much as a confirmation that the file does in fact exist. You can use the two modules defined next to create a file and then check to see if that file exists. Notice that when I create a file in this type of situation, I do not even bother to return the handle to the file. The purpose of the first program, create_file, is simply to make sure that a file with the specified name (and optional line of text) is out there on disk.

/* Filename on companion disk:

 crefile.sp */*
PROCEDURE 

create_file 
   (loc_in IN VARCHAR2, file_in IN VARCHAR2, line_in IN VARCHAR2 := NULL)
IS
   file_handle UTL_FILE.FILE_TYPE;
BEGIN
   /* 
   || Open the file, write a single line and close the file.
   */
   file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'W');
   IF line_in IS NOT NULL
   THEN
      UTL_FILE.PUT_LINE (file_handle, line_in);
   ELSE
      UTL_FILE.PUT_LINE 
         (file_handle, 'I make my disk light blink, therefore I am.');
   END IF;
   UTL_FILE.FCLOSE (file_handle);
END;

6.2.7.3 Testing for a file's existence

The second program checks to see if a file exists. Notice that it creates a local procedure to handle the close logic (which is called both in the body of the function and in the exception section).

/* Filename on companon disk: 

filexist.sf */*
CCREATE OR REPLACE FUNCTION

 file_exists 
   (loc_in IN VARCHAR2, 
    file_in IN VARCHAR2,
    close_in IN BOOLEAN := FALSE)
   RETURN BOOLEAN
IS
   file_handle UTL_FILE.FILE_TYPE;
   retval BOOLEAN;

   PROCEDURE closeif IS
   BEGIN
      IF close_in AND UTL_FILE.IS_OPEN (file_handle) 
      THEN
         UTL_FILE.FCLOSE (file_handle);
      END IF;
   END;
BEGIN
   /* Open the file. */
   file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'R');

   /* Return the result of a check with IS_OPEN. */
   retval := UTL_FILE.IS_OPEN (file_handle);

   closeif;

   RETURN retval;
EXCEPTION
   WHEN OTHERS 
   THEN
      closeif;
      



RETURN FALSE;
 END;
/

6.2.7.4 Searching a file for a string

Because I found the INSTR function to be so useful, I figured that this same kind of operation would also really come in handy with operating system files. The line_with_text function coming up shortly returns the line number in a file containing the specified text. The simplest version of such a function would have a specification like this:

FUNCTION 

line_with_text 
   (loc_in IN VARCHAR2, file_in IN VARCHAR2, text_in IN VARCHAR2)
RETURN INTEGER

In other words, given a location, a filename, and a chunk of text, find the first line in the file that contains the text. You could call this function as follows:

IF line_with_text ('h:\pers', 'names.vp', 'Hanubi') > 0
THEN
   MESSAGE ('Josephine Hanubi is a vice president!');
END IF;

The problem with this version of line_with_text is its total lack of vision. What if I want to find the second occurrence in the file? What if I need to start my search from the tenth line? What if I want to perform a case-insensitive search? None of these variations are supported.

I urge you strongly to think through all the different ways a utility like line_with_text might be used before you build it. Don't just build for today's requirement. Anticipate what you will need tomorrow and next week as well.

For line_with_text, a broader vision would yield a specification like this:

FUNCTION line_with_text 
   (loc_in IN VARCHAR2,
    file_in IN VARCHAR2, 
    text_in IN VARCHAR2, 
    occurrence_in IN INTEGER := 1,
    start_line_in IN INTEGER := 1,
    end_line_in IN INTEGER := 0,
    ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER

Wow! That's a lot more parameter passing. Let's take a look at the kind of flexibility we gain from these additional arguments. First, the following table provides a description of each parameter.

Parameter	Description
loc_in	The location of the file on the operating system
file_in	The name of the file to be opened
text_in	The chunk of text to be searched for in each line of the file
occurrence_in	The number of times the text should be found in distinct lines in the file before the function returns the line number
srart_line_in	The first line in the file from which the function should start its search
end_line_in	The last line in the file to which the function should continue its search; if zero, then search through end of file
ignore_case_in	Indicates whether the case of the file contents and text_in should be ignored when checking for its presence in the line

Notice that all the new parameters, occurrence_in through ignore_case_in, have default values, so I can call this function in precisely the same way and with the same results as the first, limited version:

IF line_with_text ('names.vp', 'Hanubi') > 0
THEN
   MESSAGE ('Josephine Hanubi is a vice president!');
END IF;

Now, however, I can also do so much more:

• Confirm that the role assigned to this user is SUPERVISOR:

  	line_with_text ('c:\temp', 'config.usr', 'ROLE=SUPERVISOR')

• Find the second occurrence of DELETE starting with the fifth line:

  	line_with_text ('/tmp', 'commands.dat', 'delete', 2, 5)

• Verify that the third line contains a terminal type specification:

  	line_with_text ('g:\apps\user\', 'setup.cfg', 'termtype=', 1, 3, 3)

Here is the code for the line_with_text function:

/* Filename on companion disk:

 linetext.sf */*
CREATE OR REPLACE FUNCTION line_with_text 
   (loc_in IN VARCHAR2,
    file_in IN VARCHAR2, 
    text_in IN VARCHAR2, 
    occurrence_in IN INTEGER := 1,
    start_line_in IN INTEGER := 1,
    end_line_in IN INTEGER := 0,
    ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER
/*
|| An "INSTR" for operating system files. Returns the line number of
|| a file in which a text string was found.
*/
IS
   /* Handle to the file. Only will open if arguments are valid. */
   file_handle UTL_FILE.FILE_TYPE;

   /* Holds a line of text from the file. */
   line_of_text VARCHAR2(1000);

   text_loc INTEGER;
   found_count INTEGER := 0;

   /* Boolean to determine if there are more values to read */
   no_more_lines BOOLEAN := FALSE;

   /* Function return value */
   return_value INTEGER := 0;
BEGIN
   /* Assert valid arguments. If any fail, return NULL. */
   IF loc_in IS NULL OR
      file_in IS NULL OR
      text_in IS NULL OR
      occurrence_in <= 0 OR
      start_line_in < 1 OR
      end_line_in < 0
   THEN
      return_value := NULL;
   ELSE
      /* All arguments are fine. Open and read through the file. */
      file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'R');
      LOOP
         /* Get next line and exit if at end of file. */
         get_nextline (file_handle, line_of_text, no_more_lines);
         EXIT WHEN no_more_lines;

         /* Have another line from file. */
         return_value := return_value + 1;

         /* If this line is between the search range... */
         IF (return_value BETWEEN start_line_in AND end_line_in) OR
            (return_value >= start_line_in AND end_line_in = 0)
         THEN
            /* Use INSTR to see if text is present. */
            IF NOT ignore_case_in
            THEN
               text_loc := INSTR (line_of_text, text_in);
            ELSE
               text_loc := INSTR (UPPER (line_of_text), UPPER (text_in));
            END IF;

            /* If text location is positive, have a match. */
            IF text_loc > 0
            THEN
               /* Increment found counter. Exit if matches request. */
               found_count := found_count + 1;
               EXIT WHEN found_count = occurrence_in;
            END IF;
         END IF;
      END LOOP;
      UTL_FILE.FCLOSE (file_handle);
   END IF;

   IF no_more_lines
   THEN
      /* read through whole file without success. */
      return_value := NULL;
   END IF;

   

RETURN return_value;




END;

6.2.7.5 Getting the nth line from a file

What if you want to get a specific line from a file? The following function takes a filename and a line number and returns the text found on that line:

/* Filename on companion disk:

 nthline.sf */*
CREATE OR REPLACE FUNCTION get_nth_line 
   (loc_in IN VARCHAR2, file_in IN VARCHAR2, line_num_in IN INTEGER)
IS
   /* Handle to the file. Only will open if arguments are valid. */
   file_handle UTL_FILE.FILE_TYPE;

   /* Count of lines read from the file. */
   line_count INTEGER := 0;

   /* Boolean to determine if there are more values to read */
   no_more_lines BOOLEAN := FALSE;

   /* Function return value */
   return_value VARCHAR2(1000) := NULL;
BEGIN
   /* Need a file name and a positive line number. */
   IF file_in IS NOT NULL AND line_num_in > 0
   THEN
      /* All arguments are fine. Open and read through the file. */
      file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'R');
      LOOP
         /* Get next line from file. */
         get_nextline (file_handle, return_value, no_more_lines);

         /* Done if no more lines or if at the requested line. */
         EXIT WHEN no_more_lines OR line_count = line_num_in - 1;

         /* Otherwise, increment counter and read another line. */
         line_count := line_count + 1;
      END LOOP;
      UTL_FILE.FCLOSE (file_handle);
   END IF;

   /* Either NULL or contains last line read from file. */
   

RETURN return_value;




END;