The UTL_FILE.GET_LINE procedure in Oracle PL/SQL is part of the UTL_FILE package, which allows for reading and writing text files from within the database. This specific procedure is used to read a line of text from an open file.
Syntax
UTL_FILE.GET_LINE ( file IN UTL_FILE.FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL);
Parameters
file: This is a file handle that refers to a file opened using the UTL_FILE.FOPEN or UTL_FILE.FOPEN_NCHAR procedure.
buffer: This is an OUT parameter that will contain the line of text read from the file. The text is stored in a VARCHAR2 variable, and the procedure will return one line at a time.
len (optional): This parameter is used to specify the maximum number of characters to read. If not provided, the default is to read up to the maximum line size.
How It Works
File Reading: UTL_FILE.GET_LINE reads one line of text at a time from an open file. The line of text is stored in the buffer variable.
End of File (EOF): If you attempt to read beyond the end of the file (EOF), the procedure raises the NO_DATA_FOUND exception.
Newline Characters: The procedure strips newline characters (e.g., \n) at the end of the line automatically when it reads the line into the buffer.
Example
DECLARE
file_handle UTL_FILE.FILE_TYPE;
line_buffer VARCHAR2(32767);
BEGIN
-- Open the file for reading
file_handle := UTL_FILE.FOPEN('DIR_ALIAS', 'example.txt', 'R');
-- Loop to read lines from the file
LOOP
BEGIN
UTL_FILE.GET_LINE(file_handle, line_buffer);
DBMS_OUTPUT.PUT_LINE(line_buffer);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT; -- End of file reached
END;
END LOOP;
-- Close the file
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN OTHERS THEN
-- Handle any exceptions
IF UTL_FILE.IS_OPEN(file_handle) THEN
UTL_FILE.FCLOSE(file_handle);
END IF;
RAISE;
END;
/
Key Points
Reading Line by Line: The procedure reads one line from a text file at a time, storing the content in a variable.
Exception Handling: It is essential to handle the NO_DATA_FOUND exception, which is raised when the end of the file is reached.
File Handles: You must open the file before reading and close it afterward to avoid resource leaks.
Limitations
Buffer Size: The size of the line read by UTL_FILE.GET_LINE is limited by the maximum size of a VARCHAR2 variable, which is typically 32,767 characters in PL/SQL. Reading beyond this limit requires careful handling or splitting the file contents across multiple reads.
In summary, UTL_FILE.GET_LINE is a fundamental procedure in Oracle PL/SQL for reading text files one line at a time, making it useful in scenarios such as data loading, log file analysis, or batch processing.