UTL_FILE - Random Access of Files

UTL_FILE - Random Access of Files
I was recently asked if I could read the first and last line of a file using PL/SQL. Until recently this was not possible without reading the whole file or using a Java stored procedure, but Oracle9i Release 2 supports random access of files through the UTL_FILE package. This article shows a simple mechanism to solve this problem using these UTL_FILE enhancements.
First we create a directory object pointing to the location of the file of interest:
CREATE OR REPLACE DIRECTORY my_docs AS '/usr/users/oracle/';
Prior to Oracle9i Release 2 I would solve this problem by reading the whole file as follows:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DOCS';
l_filename VARCHAR2(100) := 'temp';
l_text VARCHAR2(32767);
BEGIN
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);
-- Read and output first line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('First Line: ' l_text '');
-- Read through the file until we reach the last line.
BEGIN
LOOP
UTL_FILE.get_line(l_file, l_text, 32767);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
-- Output the last line.
DBMS_OUTPUT.put_line('Last Line : ' l_text '');
-- Close the file. UTL_FILE.fclose(l_file);
END;
/
Using the UTL_FILE enhancements we can now do the following:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DOCS';
l_filename VARCHAR2(100) := 'temp';
l_exists BOOLEAN;
l_file_length NUMBER;
l_blocksize NUMBER;
l_text VARCHAR2(32767);
BEGIN
UTL_FILE.fgetattr(l_location, l_filename, l_exists, l_file_length, l_blocksize);
IF l_exists THEN
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);
-- Read and output first line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('First Line: ' l_text '');
UTL_FILE.FSEEK (l_file, l_file_length-1);
-- Step backwards through the file until we reach the start of the last line.
FOR i IN REVERSE 0 .. l_file_length-2
LOOP
UTL_FILE.FSEEK (l_file, NULL, -2);
UTL_FILE.get_line(l_file, l_text, 1);
EXIT WHEN l_text IS NULL;
END LOOP;
-- Read and output the last line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('Last Line : ' l_text '');
-- Close the file. UTL_FILE.fclose(l_file);
END IF;
END;
/
The fgetattr procedure allows us to check that the file exists and return the file length. We then read the first line using the get_line procedure as normal. To get the last line we need to skip to the end of the file using the fseek procedure and work backwards until we hit a line terminator. The get_line procedure does not return line terminators so we detect it's presence by checking for the return of an empty line. We can then display the last line.
I'm not too sure about the performance of the fseek procedure. For large files it's often quicker to read the whole file which is a bit disappointing. Even so, if you need to move both backwards and forwards in the file, these enhancements may still be useful.