Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.