Home arrow Oracle arrow Page 3 - Reading Text Files using Oracle PL/SQL and UTL_FILE

How to read a text file from a file system and display the contents using PL/SQL: discussion - Oracle

In this series of articles, we shall concentrate on working with file input/output using Oracle PL/SQL packages. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of the most advanced techniques using file input/output with Oracle PL/SQL.

  1. Reading Text Files using Oracle PL/SQL and UTL_FILE
  2. How to read a text file from a file system and display the contents using PL/SQL
  3. How to read a text file from a file system and display the contents using PL/SQL: discussion
  4. Improving it a bit with exception handling
By: Jagadish Chatarji
Rating: starstarstarstarstar / 129
April 11, 2006

print this article



In this section, I shall explain the PL/SQL program given in the previous section.  Let us go through it bit by bit:

f utl_file.file_type;

The above statement creates a simple variable "f" (can also be called the file handle), which is based on the data type "utl_file.file_type".  This is not a simple data type like integer or varchar.  It has its own in-depth meaning. 

"UTL_FILE" is a built-in package, which comes with the Oracle database installation.  Any package in Oracle (either user-defined or built-in) contains one or more members in it.  These members (together as a unit) are generally used to constitute a complete system to facilitate a particular feature.  Similarly, "UTL_FILE" is also a package which has several members to facilitate use of the features of accessing file system.

"utl_file.file_type" is a different data type which points to a particular file specified by the user.  Once it successfully points, we can proceed with other operations like reading, writing, and so on.  Further proceeding, we have the following:

s varchar2(200);

I declared a simple variable "s" to simply store a line (max. 200 chars) of information from the text file.  Further we have the following:

f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');

The above statement is the key to the whole program.  It simply opens the file "sample1.txt" available in the "SAMPLEDATA" directory in readonly ("R") mode.  Once PL/SQL opens the file, the handle to that file gets stored in the variable "f".  Be aware that "SAMPLEDATA" is the logical directory name as discussed in the previous section (and not the physical directory existing in your operating system).  Further proceeding we have the following:


The above line simply tries to read the first line available in the file (where "f" is pointing to) and places the line in "s" (declared previously).  Once we finish all the operations with a file, we need to close it and release any memory resources (like file handles and more).  The same is achieved using the following:


It is highly recommended (in fact compulsory) that you close any file after finishing our operations (even though it doesn't issue any error when not closed).  And finally, we display the output back to screen using the following statement:


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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: