Home arrow Oracle arrow Page 2 - 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 - 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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In this section, we shall look at reading a text file from a host operating system.  Please understand that I am giving examples based on Windows only.  The paths would be a bit different if you use any non-Windows environment.

The first step we need to perform is nothing but letting the Oracle database know on which directory we are working (reading or writing files only from that directory).  This can be achieved using the following command:

CREATE DIRECTORY sampledata AS 'c:sampledata';

The above command creates a new directory entry into the Oracle database with a logical name to the directory, "SAMPLEDATA" (pointing to the path "c:sampledata").  To execute the above command, you need to have certain privileges.  I suggest you log in using "SYSTEM" user (or contact your database administrator on this issue).  If you wanted to list all the directories registered, you can use the following command:

SELECT * FROM ALL_DIRECTORIES;

The second step we need to perform is providing necessary permissions to the user to work with that directory.  This can be achieved with something like the following:

GRANT read, write ON DIRECTORY sampledata TO PUBLIC;

The above step makes sure that the directory "sampledata" (or "c:sampledata") is accessible to every user (PUBLIC).  It is not at all a good idea to provide grants to the PUBLIC.  I suggest you provide the grants only to the required users (who really need them).

The third step we need to perform is simply creating a new text file within the folder "c:sampledata".  I hope you can do it yourself.

The last and final step is trying to read the file using PL/SQL.  Open your SQL*PLUS environment and copy the following code:

declare
f utl_file.file_type;
s varchar2(200);
begin
f := utl_file.fopen('SAMPLEDATA','sample1.txt','R');
utl_file.get_line(f,s);
utl_file.fclose(f);
dbms_output.put_line(s);
end;
/

Before executing the above program, make sure that you execute the statement "SET SERVEROUTPUT ON" (so that the result gets displayed on the screen).  Once you complete all of the above steps, it should run successfully, retrieving the first line of the text file. 

The next section gives you the explanation of the previous PL/SQL program in detail.



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