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