Using UTL_FILE in Oracle10g XE

Oracle provides UTL_FILE package to handle files from PL/SQL. UTL_FILE is a collection of simple procedures and functions to process files in operating system filesystem, from Oracle using PL/SQL.

The following sections explain how to create required objects in database and grant necessary privileges to use UTL_FILE package. Then we will create two programs - one to read content from file and another to write content into file.

Getting Started
Reading lines from NAMES.TXT and inserting lines into NAMES table
Reading job titles from JOBS table and writing into JOBS.TXT file

Getting Started

In order to use UTL_FILE package, which is owned by SYS, you have to grant EXECUTE privilege to user after you log in as SYS.

Execute the following commands at SQL> prompt after starting SQL*PLUS.

connect sys/password as sysdba

Provide the password of SYS account, which is given at the time of installation of Oracle 10g XE.

Give EXECUTE privilege to required user or PUBLIC (all users) as follows.

grant execute on UTL_FILE to public;

The next important step is to create a logical directory (directory alias) in Oracle that points to physical directory in filesystem. However, directory alias can be created only by DBA (SYS or SYSTEM). So after you logged in as SYS, create directory alias for ORACLE folder in C:\ and grant read and write permissions to PUBLIC as follows.

create directory filesdir as 'c:\oracle';
grant read on directory filesdir to public;
grant write on directory filesdir to public;

Now, you can log into HR account and create a table - NAMES as follows.

connect hr/hr;

create table names
( 
 name varchar2(30) 
);

We also need a simple text file NAMES.TXT in C:\ORACLE as follows. NAMES.TXT contains one line for one name.

Kevin
Tom
Steve
George

Now, we are ready to write programs that use UTL_FILE package.

Reading NAMES.TXT

The following program uses UTL_FILE package to read one line at a time from NAMES.TXT file and inserts it into NAMES table. It uses FOPEN, GET_LINE and FCLOSE methods of UTL_FILE package.

DECLARE
 line VARCHAR2(100);
 namesfile UTL_FILE.FILE_TYPE;
BEGIN
 --  Syntax : FOPEN ( directory alias, filename, open mode)
 namesfile  := UTL_FILE.FOPEN('FILESDIR','NAMES.TXT','R');    -- open in read mode 
 LOOP
   -- read  a line from NAMES.TXT
   -- NO_DATA_FOUND exception occurs when you reach end of file
   -- Syntax : GET_LINE( filename, variable , max length)
   UTL_FILE.GET_LINE(namesfile,line,100);
   insert into names values( line);   -- insert into NAMES table
 END LOOP;
 EXCEPTION
   WHEN OTHERS THEN
     UTL_FILE.FCLOSE(namesfile);    -- close file
   WHEN OTHERS THEN
     dbms_output
END;

After running the above program, you will see names inserted into NAMES table. Use the following query to list rows from NAMES table.

select * from names;

Writing into JOBS.TXT

UTL_FILE provides PUT_LINE method to write a complete line into file along with new line. For this to work, the file must be opened in write(w) mode. The following program takes job titles from JOBS table and writes each title on a separate line in JOBS.TXT file. However, note that you must have WRITE permission on the directory to write into file, which we have granted to PUBLIC from SYS in getting started section.

DECLARE
JOBSFILE UTL_FILE.FILE_TYPE;
-- TAKE ALL JOB TITLES FROM JOBS
CURSOR JOBSCUR IS
  SELECT JOB_TITLE FROM JOBS ORDER BY JOB_TITLE;
BEGIN
 -- OPEN FILE FOR WRITING
 JOBSFILE := UTL_FILE.FOPEN('FILESDIR','JOBS.TXT','W');
 
 -- TAKE ONE RECORD FROM CURSOR
 FOR REC IN JOBSCUR
 LOOP
   UTL_FILE.PUT_LINE(JOBSFILE, REC.JOB_TITLE);  -- WRITE INTO FILE
 END LOOP;
 
 UTL_FILE.FCLOSE(JOBSFILE);  -- CLOSE FILE
 
EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE( 'ERROR -->' ||  SQLERRM);
END;

After you run the above program, open JOBS.TXT file from C:\ORACLE folder to see job titles written into it.

UTL_FILE package does provide some more interesting methods. For more details on this Oracle supplied package, please read Oracle documentation on UTL_FILE package.