PL/SQL File I/O in UNIX

This month we will discuss some PL/SQL file I/O. Oracle version 7.3 and up are capable of reading and writing from an operating system text file. This is accomplished by using the PL/SQL package UTL_FILE. The discussion here is based on UNIX files. 

Procedures and functions

The following are the various procedures and functions available in UTL_FILE package:

Function		FOPEN
Description		Open a file for input or output
Parameters		Location, File name, Open mode 
			Open modes are R-Read, W-Write, A-Append
Procedure		FCLOSE
Description		Close a file
Parameters		File handle
Procedure 		FCLOSE_ALL
Description		Close all open files
Parameters		-
Function		IS_OPEN
Description		Verify if the file is open
Parameters		File handle
Procedure		PUT
Description		Write a line without a line terminator
Parameters		File handle, Write string
Procedure		PUT_LINE
Description		Write a line with line terminator
Parameters		File handle, Write string
Procedure		PUTF
Description		Write line with formatting
Parameters		File handle, Format, Up to 5 arguments
Procedure		GET_LINE
Description		Read a line from an opened file
Parameters		File handle, Read string
Procedure		NEW_LINE
Description		Write line terminators to a file
Parameters		File handle, Number of lines
Procedure		FFLUSH
Description		Write all pending outputs to file
Parameters		File handle

Here file handle is an internal data type (UTL_FILE.FILE_TYPE) variable which is assigned the value returned by the FOPEN function. This serves as a pointer and is referenced in all subsequent procedures for any operation on that file.

Write/Read Location

The the path (or directory) where you write or read a file should be accessable to oracle software owner (shadow process owner). The directory you wish to use should be mentioned in the init.ora file using the parameter UTL_FILE_DIR. If you use multiple read/write directories each directory should be specified here. Specify the paramter multiple times with each directory name. If you specify *, any directory can be used to read/write provided the OS permissions are valid.

Exceptions

Oracle provides 7 predefined exceptions for UTL_FILE operations. They are as follows:

Exception Name		Description
INVALID_PATH		The location or file name is invalid
INVALID_MODE		File open mode is invalid
INVALID_FILEHANDLE	File handle was invalid
INVALID_OPERATION	File could not be opened or operated as requested
READ_ERROR		OS error while reading
WRITE_ERROR		OS error while writing
INTERNAL_ERROR		An unspecified error

Example 1 : Read from a table and write to a file

  1  REM Example UTL_FILE program
  2  REM Read from a table and write to a file
  3  REM
  4  set serveroutput on
  5  REM
  6  declare
  7    /* Decalre the file handle */
  8    wfile_handle  UTL_FILE.FILE_TYPE;
  9    /* Declare a cursor to read the table data */
 10    cursor C_MAIN is SELECT NUM_NUMBER, NUM_WORD FROM BIJU_NUMBERS
 11                WHERE NUM_NUMBER < 25;
 12    wstring varchar2 (1000);
 13  begin
 14    /* Open the file for write */
 15    wfile_handle := UTL_FILE.FOPEN ('/ora_backup/emodb', 'example1.out', 'W')
;
 16    for R_MAIN in C_MAIN loop
 17      wstring := to_char(R_MAIN.NUM_NUMBER) || ' - ' || R_MAIN.NUM_WORD;
 18      utl_file.put_line (wfile_handle, wstring);
 19    end loop;
 20    /* Close the file */
 21    UTL_FILE.FCLOSE (wfile_handle);
 22  EXCEPTION
 23    WHEN UTL_FILE.INVALID_PATH then
 24       dbms_output.put_line ('Please verify the directory / file name');
 25       RAISE;
 26    WHEN OTHERS then
 27       RAISE;
 28 end;
 29 /

Example 2 : Read an OS file and display the output in PL/SQL

  1  REM Example UTL_FILE program
  2  REM Read from a file and display to screen
  3  REM
  4  set serveroutput on
  5  REM
  6  declare
  7    /* Decalre the file handle */
  8    wfile_handle  UTL_FILE.FILE_TYPE;
  9    wstring varchar2 (1000);
 10  begin
 11    /* Open the file for read */
 12    wfile_handle := UTL_FILE.FOPEN ('/ora_backup/demodb', 'example1.out', 'R'
);
 13    loop
 14      begin
 15         utl_file.get_line (wfile_handle, wstring);
 16      exception
 17         when no_data_found then exit;
 18      end;
 19      dbms_output.put_line (wstring);
 20    end loop;
 21    /* Close the file */
 22    UTL_FILE.FCLOSE (wfile_handle);
 23  EXCEPTION
 24    WHEN UTL_FILE.INVALID_PATH then
 25       dbms_output.put_line ('Please verify the directory / file name');
 26       RAISE;
 27    WHEN OTHERS then
 28       RAISE;
 29 end;
 30 /

Example 3 : Another version of example 1

Here we use two more UTL_FILE procedures to obtain the same result. Change the lines 16 to 19 in example 1 to

for R_MAIN in C_MAIN loop
    utl_file.put (wfile_handle, to_char(R_MAIN.NUM_NUMBER));
    utl_file.put (wfile_handle, ' - ');
    utl_file.put_line (wfile_handle, R_MAIN.NUM_WORD);
end loop

Example 4 : Formatting the writing to a file

Use the same example 1 and change the lines 16 through 19 to

  for R_MAIN in C_MAIN loop
    utl_file.putf (wfile_handle, '%s - %s', to_char(R_MAIN.NUM_NUMBER), R_MAIN.NUM_WORD);
    utl_file.new_line(wfile_handle, 1);
  end loop;

Example 5 : Another version of example 4

  for R_MAIN in C_MAIN loop
    utl_file.putf (wfile_handle, '%s - %s\n to_char(R_MAIN.NUM_NUMBER), R_MAIN.NUM_WORD);
  end loop;

"%s" is used to substitute a variable and "\n" is a line feed.

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.