Home arrow Oracle arrow Writing to Text Files in Oracle PL/SQL

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: