8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | 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...