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

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.