PL/SQL can read text files on the database server with UTL_FILE: open the file through a directory object, read it one line at a time with GET_LINE, and close it. GET_LINE raises NO_DATA_FOUND after the last line, which is how a loop knows it has reached the end of the file.
Code for This Guide
The main examples are in the examples/pkg-files-network folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
f := utl_file.fopen('DIRECTORY', 'file name', 'r' [, max_linesize]);
utl_file.get_line(f, buffer_out [, len]);
utl_file.fclose(f);The directory object name is passed in uppercase as a string. MAX_LINESIZE, up to 32,767 and 1,024 by default, must be at least as long as the longest line.
The examples read fuel_prices.csv from the NIMBUS_FILES directory, which the NIMBUS setup creates; the file comes from the setup/nimbus/files folder of the repository.
Read a CSV File
The loop skips the header line, then averages the fuel prices for Dubai.
Example:
declare
f utl_file.file_type;
v_line varchar2(200);
v_n pls_integer := 0;
v_sum number := 0;
begin
f := utl_file.fopen('NIMBUS_FILES', 'fuel_prices.csv', 'r');
utl_file.get_line(f, v_line); -- skip the header
loop
begin
utl_file.get_line(f, v_line);
exception
when no_data_found then exit; -- end of file
end;
if v_line like 'DXB,%' then
v_n := v_n + 1;
v_sum := v_sum + to_number(regexp_substr(v_line, '[^,]+', 1, 3));
end if;
end loop;
utl_file.fclose(f);
dbms_output.put_line('DXB: ' || v_n || ' prices, average '
|| to_char(v_sum / v_n, 'FM0.00') || ' USD per gallon');
end;
/Output:
DXB: 3 prices, average 2.41 USD per gallon PL/SQL procedure successfully completed.
Three lines start with DXB, averaging 2.41 USD per gallon. The inner block catches NO_DATA_FOUND and leaves the loop at the end of the file.
Split Each Line into Fields
Example:
-- read every line of the file and show the first three data lines split into fields
declare
f utl_file.file_type;
v_line varchar2(200);
v_n pls_integer := 0;
begin
f := utl_file.fopen('NIMBUS_FILES', 'fuel_prices.csv', 'r', max_linesize => 200);
loop
begin
utl_file.get_line(f, v_line);
exception
when no_data_found then exit;
end;
v_n := v_n + 1;
if v_n between 2 and 4 then
dbms_output.put_line('airport ' || regexp_substr(v_line, '[^,]+', 1, 1)
|| ', date ' || regexp_substr(v_line, '[^,]+', 1, 2)
|| ', price ' || regexp_substr(v_line, '[^,]+', 1, 3));
end if;
end loop;
utl_file.fclose(f);
dbms_output.put_line(v_n || ' lines in the file, header included');
end;
/Output:
airport DXB, date 2026-01-05, price 2.41 airport LHR, date 2026-01-05, price 2.88 airport SIN, date 2026-01-05, price 2.52 13 lines in the file, header included PL/SQL procedure successfully completed.
REGEXP_SUBSTR with [^,]+ picks the first, second, and third field of each line. The counter shows the file has 13 lines, including the header.
Things to Know
- A line longer than MAX_LINESIZE raises VALUE_ERROR; set it to the longest line you expect.
- Close the file in exception handlers too, or use UTL_FILE.FCLOSE_ALL as a last resort.
- For files you query often, an external table reads them with plain SQL.
Related Guides
Conclusion
UTL_FILE.GET_LINE reads a server text file one line at a time, and NO_DATA_FOUND marks the end. Open the file with a big enough line size, split lines into fields with REGEXP_SUBSTR, and always close the file.
See my articles more often when you search Google.
