Writing to Text Files in Oracle PL/SQL (Page 1 of 6 )
This is the second article in a series focusing on file input/output using Oracle PL/SQL packages.
If you are new to working with UTL_FILE, I strongly suggest you go through my first article in this series. It gives you all the necessary steps for creating and developing PL/SQL with UTL_FILE for beginners.
How to read multiple lines from the text file using PL/SQL
In the previous article, we have seen how to work with only the first line in the text file. But no one would ever be happy working with only a single line. In this section, we shall work on more than one line (or read all lines) from the text file.
We will also need to work with exceptions in this article. If you are new to exception handling in PL/SQL, I strongly suggest you refer to my contributions at:
http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Predefined-Exceptions/
http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-User-
defined-Exceptions-Nested-Blocks/
Now, let us follow the sample code:
declare
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
loop
utl_file.get_line(f,s);
dbms_output.put_line(s);
end loop;
exception
when NO_DATA_FOUND then
utl_file.fclose(f);
end;
Since I explained much of the above program in my previous article, I don’t want to go through every line. The most important issue from the above program is that I used “utl_file.get_line” in an infinite loop (a loop that would never finish).
When does the loop terminate? It all depends on the “exception.” When the PL/SQL run-time successfully reads a line from the text file, it simply places that information into the variable “s” (which gets displayed directly onto the screen).
When it is unable to read any more lines from the text file, it simply raises an exception (NO_DATA_FOUND), which gets handled in the “exception” section. Once an exception gets raised, the control will automatically terminate the loop abnormally.
Next: How to count the number of lines from the text file >>
More Oracle Articles
More By Jagadish Chatarji
