Using the New UTL_FILE Package

In Oracle9i Database Release 2, there have been many changes to the UTL_FILE package. These include security enhancements using the CREATE DIRECTORY statement, copying, deleting and renaming files, and handling raw data.
In the latest release of 9i, the use of the UTL_FILE_DIR init.ora parameter is advised against on security grounds. Because it is possible to write programs to use this package and access the directories set by utl_file_dir it is potentially possible to read files and data you shouldn't be able to The advice now is to create a directory and use that instead. By default, only SYS and SYSTEM can create directories.We will examine the following new capabilities

• Using the CREATE DIRECTORY Statement to manage access to directories
• Handling Raw Data
• Copying, Deleting and Renaming Files

Using the CREATE DIRECTORY Statement to Manage Access to Directories
Both server and client side PL/SQL programs can use the UTL_FILE package although file system access is always on the server host. Prior releases controlled the server side access through the UTL_FILE_DIR initialization parameter. With Oracle9i Database Release 2, the server side access is controlled through the directory object feature that is enabled through the CREATE DIRECTORY command. The CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default. directory objects are always owned by SYS. Perform the following steps:

You will create a directory and grant read access to HR.
set echo on
connect / as sysdba;
create or replace directory USER_DIR as 'D:\wkdir';
grant read on directory USER_DIR to HR;
create or replace directory SYS_DIR as 'D:\wkdir';
grant read on directory SYS_DIR to dba;

HR has access to the files located in the D:\wkdir directory. Subdirectory access is not available. Accessing any file within the D:\wkdir directory must follow the case sensitivity rules as required by the operating system. All other users trying to access a file within this directory receive an Oracle exception: ORA-29289: directory access denied. You can catch this exception by using the UTL_FILE package exception ACCESS_DENIED. To show how this works, logon as HR and execute:
set serveroutput on
DECLARE
   v_fh    UTL_FILE.fILE_TYPE;
   v_buffer  VARCHAR2(4000) := 'Hello Utl_File';
BEGIN
   v_fh := UTL_FILE.FOPEN('SYS_DIR', 'userdata.txt', 'w');
   UTL_FILE.PUT_LINE(v_fh, v_buffer);
   UTL_FILE.FCLOSE(v_fh);
EXCEPTION
   WHEN UTL_FILE.ACCESS_DENIED THEN
       DBMS_OUTPUT.PUT_LINE('No Access!!!');
   WHEN others THEN
      DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/

You can now check your working directory to see that the userdata.txt file is there.

Copying, Deleting and Renaming Files
Four new procedures have been added to UTL_FILE to allow the file access activities of removing, copying, finding attributes, and renaming operating system files: UTL_FILE.FGETATTR, UTL_FILE.FCOPY, UTL_FILE.FRENAME, and UTL_FILE.FREMOVE.

Restrinctions:
An NFS mount will allow you to do this, yes. The file must appear to be available on the database server and NFS will do that easily.
UTL_FILE can only see the file system on the server itself. UTL_FILE is run in the Oracle dedicated/shared server which resides on the server -- it is not running on the client side -- it cannot see the file systems the client can see. For utl_file to write to a netware volumn -- that volumn would have to be mounted as a unix file system and accessible to the server itself. 
This is not a limitation of utl_file -- utl_file is running on the server itself and can only see that file system. Can you have the netware disk mounted on the unix machine -- making them available to processes running on the server? Else you'll have to bring the result set back to the client and write it there.

The UTL_FILE.FGETATTR procedure reads and returns the attributes of a disk file. Format is
               UTL_FILE.FGETATTR (location, filename, exists,file_length, blocksize, create_date, mod_date).
From your SQL*Plus session, execute the following:
DECLARE
   v_exists    BOOLEAN;
   v_length    NUMBER;
   v_blocksize NUMBER;
BEGIN 
    UTL_FILE.FGETATTR('USER_DIR', 'userdata.txt', v_exists, v_length, v_blocksize);
    IF v_exists THEN
       dbms_output.put_line('Length is: '||v_length);
       dbms_output.put_line('Block size is: '||v_blocksize);
    ELSE
      dbms_output.put_line('File not found.'); 
    END IF;
END;
/

The UTL_FILE.FCOPY procedure copies a contiguous portion of a text file to a new file. The entire file is copied if the start line and end line parameters are omitted. Format is:
                UTL_FILE.FCOPY (location, filename, dest_dir, dest_file, start_line, end_line).
From your SQL*Plus session session, execute:
set serveroutput on
DECLARE
   v_exists    BOOLEAN;
   v_length    NUMBER;
   v_blocksize NUMBER;
BEGIN
  UTL_FILE.FCOPY('USER_DIR', 'userdata.txt', 'USER_DIR', 'copy.txt', 1, 10);
  UTL_FILE.FGETATTR('USER_DIR', 'userdata.txt', v_exists, v_length, v_blocksize);
  IF v_exists THEN
      dbms_output.put_line('Source Length is: '||v_length);
      dbms_output.put_line('Source Block size is: '||v_blocksize);
  ELSE
    dbms_output.put_line('File not found.');
  END IF;
  UTL_FILE.FGETATTR('USER_DIR', 'copy.txt', v_exists, v_length, v_blocksize);
  IF v_exists THEN
      dbms_output.put_line('Copy Length is: '||v_length);
      dbms_output.put_line('Copy Block size is: '||v_blocksize);
  ELSE
      dbms_output.put_line('File not found.');
  END IF;
END;
/

The UTL_FILE.FRENAME procedure renames an existing file to a new name. You will rename the file userdata.txt to userdata2.txt in the USER_DIR directory. Format is:
              UTL_FILE.FRENAME (location, filename, dest_dir, dest_file, overwrite). Execute the following SQL:
set serveroutput on
BEGIN
  UTL_FILE.FRENAME('USER_DIR', 'userdata.txt',
     'USER_DIR', 'userdata2.txt', TRUE);
END;
/

The UTL_FILE.FREMOVE procedure removes an operating system file. You will remove the userdata2.txt file from the USER_DIR directory. Fromat is:
                 UTL_FILE.FREMOVE (location, filename).
Execute the following script:
set serveroutput on
DECLARE
  v_fh UTL_FILE.FILE_TYPE;
  v_exists BOOLEAN;
  v_length NUMBER;
  v_blocksize NUMBER;
BEGIN
  v_fh := UTL_FILE.FOPEN('USER_DIR', 'userdata2.txt', 'w');
  UTL_FILE.PUT_LINE(v_fh, 'Hello Utl_File');
  UTL_FILE.FCLOSE(v_fh);
  UTL_FILE.FREMOVE ('USER_DIR', 'userdata2.txt');
  DBMS_OUTPUT.PUT_LINE('File is removed.');
END;
/

SET SERVEROUTPUT ON
DECLARE
  v_exists       BOOLEAN;
  v_file_length  NUMBER;
  v_block_size   NUMBER;
BEGIN
  UTL_FILE.FRENAME (src_location  => 'c:\',
                    src_filename  => 'test.txt',
                    dest_location => 'c:\',
                    dest_filename => 'test1.txt',
                    overwrite     => TRUE);

  UTL_FILE.FCOPY(src_location  => 'c:\',
                 src_filename  => 'test1.txt',
                 dest_location => 'c:\',
                 dest_filename => 'test2.txt');

  UTL_FILE.FREMOVE(location  => 'c:\',
                   filename  => 'test2.txt');

  UTL_FILE.FGETATTR(LOCATION => 'c:\',
                    FILENAME => 'test1.txt',
                    FEXISTS => v_exists,
                    FILE_LENGTH => v_file_length,
                    BLOCK_SIZE => v_block_size);
 
  IF v_exists THEN                 
    DBMS_OUTPUT.PUT_LINE('Exists: TRUE');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Exists: FALSE');
  END IF;
  DBMS_OUTPUT.PUT_LINE('File Length:' || v_file_length);
  DBMS_OUTPUT.PUT_LINE('Block Size :' || v_block_size);
END;
/

Support for NCHAR and RAW data has been included with the following procedures:
FOPEN_NCHAR
GET_LINE_NCHAR
PUT_LINE_NCHAR
PUT_NCHAR
PUTF_NCHAR
GET_RAW
PUT_RAW

Following Exceptions are valid for UTL_FILE
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 " 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).

The four procedures to write data out to the file

File I/O capabilities in Oracle9i
File I/O was enhanced in Oracle9i to include the reading and writing of data in Unicode format. Five new sub-programs were introduced in the UTL_FILE package for this purpose:
UTL_FILE.FOPEN_NCHAR
UTL_FILE.GET_LINE_NCHAR
UTL_FILE.PUT_NCHAR
UTL_FILE.PUT_LINE_NCHAR
UTL_FILE.PUTF_NCHAR


Smarter file I/O and file management capabilities on Oracle9i Release 2
Oracle9i Release 2 enables some smart file I/O and file management functions to be performed. The smart file I/O functions include:
• Reading and writing binary data - This is done using the procedures UTL_FILE.GET_RAW and UTL_FILE.PUT_RAW.
• Performing autoflush and seek operations - The autoflush operation is done using the procedure UTL_FILE.PUT_LINE with the autoflush parameter specified as TRUE. The seek operation is done using the procedure UTL_FILE.FSEEK.
• Obtaining the relative offset - This is obtained by means of the UTL_FILE.FGETPOS function.


Oracle9i Release 2 also allows file management operations to be performed
• Copying files - This is done using the procedure UTL_FILE.FCOPY.
• Deleting files - This is done using the procedure UTL_FILE.FREMOVE.
• Renaming files - This is done using the procedure UTL_FILE.FRENAME.
• Retrieving the attributes of a file such as whether it exists or not, the file length, and file system block size - This is done using the procedure UTL_FILE.FGETATTR.

MORE UTL_FILE Examples

So you've got a file (or a dozen files) out on disk, filled with all sorts of good information you want to access from your PL/SQL-based application. You will find yourself performing the same kinds of operations against those files over and over again.

After you work your way through this book, I hope that you will recognize almost without conscious thought that you do not want to repeatedly build the open, read, and close operations for each of these files, for each of the various recurring operations. Instead, you will instantly say to yourself, "Hot diggity! This is an opportunity to build a set of standard, generic modules that will help manage my files."

This section contains a few of my candidates for the first contributions to a UTL_FILE toolbox of utilities. I recommend that you consider building a single package to contain all of these utilities.

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;
/

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). Theget_nextline procedure shown here embodies this principle.

/* Filename on companion disk: getnext.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: fil2tab2.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;

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;
/

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