Using
the New UTL_FILE Package
In Oracle9i Database Release 2, there
have been many changes to the
UTL_FILE package. These include security enhancements using the CREATE
DIRECTORY statement, copying, deleting and renaming files, and handling
raw data.
In the latest release of 9i, the use of the UTL_FILE_DIR init.ora
parameter is
advised against on security grounds. Because it is possible to write
programs to use this package and access the directories set by
utl_file_dir it is potentially possible to read files and data you
shouldn't be able to The advice now is to create a
directory and use that instead. By default, only SYS and SYSTEM can
create directories.We will examine the following new capabilities
Using the
CREATE DIRECTORY Statement to Manage Access to Directories
Both server and client side PL/SQL programs can use the UTL_FILE
package although file system access is always on the server host. Prior
releases controlled the server side access through the UTL_FILE_DIR
initialization parameter. With Oracle9i Database Release 2, the server
side access is controlled through the directory object feature that is
enabled through the CREATE DIRECTORY command. The CREATE DIRECTORY
privilege is granted only to SYS and SYSTEM by default. directory
objects are always owned by SYS. Perform the following steps:
You will create a directory and grant read access to HR.
set echo on
connect / as sysdba;
create or replace directory USER_DIR as 'D:\wkdir';
grant read on directory USER_DIR to HR;
create or replace directory SYS_DIR as 'D:\wkdir';
grant read on directory SYS_DIR to dba;
HR has access to the files located in the D:\wkdir directory.
Subdirectory access is not available. Accessing any file within the
D:\wkdir directory must follow the case sensitivity rules as required
by the operating system. All other users trying to access a file within
this directory receive an Oracle exception: ORA-29289: directory access
denied. You can catch this exception by using the UTL_FILE package
exception ACCESS_DENIED. To show how this works, logon as HR and
execute:
set serveroutput on
DECLARE
v_fh UTL_FILE.fILE_TYPE;
v_buffer
VARCHAR2(4000) := 'Hello Utl_File';
BEGIN
v_fh :=
UTL_FILE.FOPEN('SYS_DIR', 'userdata.txt', 'w');
UTL_FILE.PUT_LINE(v_fh, v_buffer);
UTL_FILE.FCLOSE(v_fh);
EXCEPTION
WHEN
UTL_FILE.ACCESS_DENIED THEN
DBMS_OUTPUT.PUT_LINE('No Access!!!');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/
You can now check your working directory to see that the userdata.txt
file is there.
Copying, Deleting and Renaming Files
Four new procedures have been added to UTL_FILE to allow the file
access activities of removing, copying, finding attributes, and
renaming operating system files: UTL_FILE.FGETATTR, UTL_FILE.FCOPY,
UTL_FILE.FRENAME, and UTL_FILE.FREMOVE.
Restrinctions:
An NFS mount will allow you to do this, yes. The file must appear to be
available on the database server and NFS will do that easily.
UTL_FILE can only see the file system on the server itself. UTL_FILE is
run in the Oracle dedicated/shared server which resides on the server
-- it is not running on the client side -- it cannot see the file
systems the client can see. For utl_file to write to a netware volumn
-- that volumn would have to be mounted as a unix file system and
accessible to the server itself.
This is not a limitation of utl_file -- utl_file is running on the
server itself and can only see that file system. Can you have the
netware disk mounted on the unix machine -- making them available to
processes running on the server? Else you'll have to bring the result
set back to the client and write it there.
The UTL_FILE.FGETATTR
procedure reads and returns the attributes of a disk file. Format is
UTL_FILE.FGETATTR (location, filename, exists,file_length, blocksize,
create_date, mod_date).
From your SQL*Plus session, execute the following:
DECLARE
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
BEGIN
UTL_FILE.FGETATTR('USER_DIR', 'userdata.txt', v_exists, v_length,
v_blocksize);
IF v_exists THEN
dbms_output.put_line('Length is: '||v_length);
dbms_output.put_line('Block size is: '||v_blocksize);
ELSE
dbms_output.put_line('File not found.');
END IF;
END;
/
The UTL_FILE.FCOPY procedure
copies a contiguous portion of a text file to a new file. The entire
file is copied if the start line and end line parameters are omitted.
Format is:
UTL_FILE.FCOPY (location, filename, dest_dir, dest_file,
start_line, end_line).
From your SQL*Plus session session, execute:
set serveroutput on
DECLARE
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
BEGIN
UTL_FILE.FCOPY('USER_DIR', 'userdata.txt', 'USER_DIR', 'copy.txt', 1,
10);
UTL_FILE.FGETATTR('USER_DIR', 'userdata.txt', v_exists, v_length,
v_blocksize);
IF v_exists THEN
dbms_output.put_line('Source Length is: '||v_length);
dbms_output.put_line('Source Block size is: '||v_blocksize);
ELSE
dbms_output.put_line('File not found.');
END IF;
UTL_FILE.FGETATTR('USER_DIR', 'copy.txt', v_exists, v_length,
v_blocksize);
IF v_exists THEN
dbms_output.put_line('Copy Length is: '||v_length);
dbms_output.put_line('Copy Block size is: '||v_blocksize);
ELSE
dbms_output.put_line('File not found.');
END IF;
END;
/
The UTL_FILE.FRENAME
procedure renames an existing file to a new name. You will rename the
file userdata.txt to userdata2.txt in the USER_DIR directory. Format is:
UTL_FILE.FRENAME (location, filename, dest_dir, dest_file, overwrite).
Execute the following SQL:
set serveroutput on
BEGIN
UTL_FILE.FRENAME('USER_DIR', 'userdata.txt',
'USER_DIR', 'userdata2.txt', TRUE);
END;
/
The UTL_FILE.FREMOVE procedure
removes an operating system file. You will remove the userdata2.txt
file from the USER_DIR directory. Fromat is:
UTL_FILE.FREMOVE (location,
filename).
Execute the following script:
set serveroutput on
DECLARE
v_fh UTL_FILE.FILE_TYPE;
v_exists BOOLEAN;
v_length NUMBER;
v_blocksize NUMBER;
BEGIN
v_fh :=
UTL_FILE.FOPEN('USER_DIR', 'userdata2.txt', 'w');
UTL_FILE.PUT_LINE(v_fh,
'Hello Utl_File');
UTL_FILE.FCLOSE(v_fh);
UTL_FILE.FREMOVE
('USER_DIR', 'userdata2.txt');
DBMS_OUTPUT.PUT_LINE('File is removed.');
END;
/
SET SERVEROUTPUT ON
DECLARE
v_exists BOOLEAN;
v_file_length
NUMBER;
v_block_size
NUMBER;
BEGIN
UTL_FILE.FRENAME
(src_location => 'c:\',
src_filename => 'test.txt',
dest_location => 'c:\',
dest_filename => 'test1.txt',
overwrite => TRUE);
UTL_FILE.FCOPY(src_location => 'c:\',
src_filename => 'test1.txt',
dest_location => 'c:\',
dest_filename => 'test2.txt');
UTL_FILE.FREMOVE(location => 'c:\',
filename => 'test2.txt');
UTL_FILE.FGETATTR(LOCATION => 'c:\',
FILENAME => 'test1.txt',
FEXISTS => v_exists,
FILE_LENGTH => v_file_length,
BLOCK_SIZE => v_block_size);
IF v_exists
THEN
DBMS_OUTPUT.PUT_LINE('Exists: TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('Exists: FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE('File Length:' || v_file_length);
DBMS_OUTPUT.PUT_LINE('Block Size :' || v_block_size);
END;
/
Support for NCHAR and RAW data has been included with the
following procedures:
FOPEN_NCHAR
GET_LINE_NCHAR
PUT_LINE_NCHAR
PUT_NCHAR
PUTF_NCHAR
GET_RAW
PUT_RAW
Following
Exceptions are valid for UTL_FILE
The package specification of UTL_FILE defines seven exceptions. The
cause behind a UTL_FILE exception can often be difficult to understand.
Here are the explanations Oracle provides for each of the exceptions:
NOTE: As a result of the way these exceptions are declared (as "user-defined exceptions"), there is no error number associated with any of the exceptions. Thus you must include explicit exception handlers in programs that call UTL_FILE if you wish to find out which error was raised. See the section " Handling file I/O errors " for more details on this process.
The
four procedures to write data out to the file
Name |
Parameters |
Behavior |
UTL_FILE.PUT |
File is the file handle, buffer
is the variable holding the data |
Writes a string value to an
output file without appending the line terminator |
UTL_FILE.PUT_LINE |
File is the file handle, buffer
is a line of data, autoflush if TRUE, flushes the buffer to disk |
Writes a line of data of the
size specified in FOPEN to an output file and appends the line
terminator after writing. The autoflush feature is available as of
Oracle9i Release. Also, the maximum length of buffer is 32K as of
Oracle9i Release 2 |
UTL_FILE.PUTF |
File is the file handle, format
is a formatting string with text and/or %s and \n and arg1 to arg5 are
argument string variables |
Writes formatted data to an
output file |
UTL_FILE.NEW_LINE |
File is the file handle and
lines is the number of line terminators to be written |
Writes a line terminator character to an output file |
File
I/O capabilities in Oracle9i
File I/O was enhanced in Oracle9i to include the reading and writing of
data in Unicode
format. Five new sub-programs were introduced in the UTL_FILE
package for this purpose:
UTL_FILE.FOPEN_NCHAR
UTL_FILE.GET_LINE_NCHAR
UTL_FILE.PUT_NCHAR
UTL_FILE.PUT_LINE_NCHAR
UTL_FILE.PUTF_NCHAR
Smarter
file I/O and file management capabilities on Oracle9i Release 2
Oracle9i Release 2 enables some smart file I/O and file management
functions to be performed. The smart file I/O functions include:
• Reading and writing binary data - This is done using the procedures
UTL_FILE.GET_RAW and UTL_FILE.PUT_RAW.
• Performing autoflush and seek operations - The autoflush operation is
done using the procedure UTL_FILE.PUT_LINE with the autoflush parameter
specified as TRUE. The seek operation is done using the procedure
UTL_FILE.FSEEK.
• Obtaining the relative offset - This is obtained by means of the
UTL_FILE.FGETPOS function.
Oracle9i
Release 2 also allows file management operations to be performed
• Copying files - This is done using the procedure UTL_FILE.FCOPY.
• Deleting files - This is done using the procedure UTL_FILE.FREMOVE.
• Renaming files - This is done using the procedure UTL_FILE.FRENAME.
• Retrieving the attributes of a file such as whether it exists or not,
the file length, and file system block size - This is done using the
procedure UTL_FILE.FGETATTR.
So you've got a file (or a dozen files) out on disk, filled with all sorts of good information you want to access from your PL/SQL-based application. You will find yourself performing the same kinds of operations against those files over and over again.
After you work your way through this book, I hope that you will recognize almost without conscious thought that you do not want to repeatedly build the open, read, and close operations for each of these files, for each of the various recurring operations. Instead, you will instantly say to yourself, "Hot diggity! This is an opportunity to build a set of standard, generic modules that will help manage my files."
This section contains a few of my candidates for the first contributions to a UTL_FILE toolbox of utilities. I recommend that you consider building a single package to contain all of these utilities.
The GET_LINE procedure is simple and
straightforward. It gets the next line from the file. If the pointer to
the file is already located at the last line of the file,
UTL_FILE.GET_LINE does not return data, but instead raises the
NO_DATA_FOUND exception. Whenever you write programs using GET_LINE,
you will therefore need to handle this exception. Let's explore the
different ways you can do this.
The following example uses a loop to read
the contents of a file into a PL/SQL table (whose type definition,
tabpkg.names_tabtype, has been declared previously):
/* Filename on companion disk:
file2tab.sp */
CREATE OR REPLACE PACKAGE tabpkg
IS
TYPE names_tabtype
IS TABLE OF VARCHAR2(100)
INDEX BY BINARY_INTEGER;
END;
/
CREATE OR REPLACE PROCEDURE
file_to_table
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
table_in IN
OUT tabpkg.names_tabtype)
IS
/* Open file and get
handle right in declaration */
names_file
UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc_in, file_in, 'R');
/* Counter used to
store the Nth name. */
line_counter INTEGER
:= 1;
BEGIN
LOOP
UTL_FILE.GET_LINE (names_file, table_in(line_counter));
line_counter := line_counter + 1;
END LOOP;
EXCEPTION
WHEN
NO_DATA_FOUND
THEN
UTL_FILE.FCLOSE (names_file);
END;
/
I am not entirely comfortable with this approach. I don't like to code infinite loops without an EXIT statement; the termination condition is not structured into the loop itself. Furthermore, the end-of-file condition is not really an exception; every file, after all, must end at some point.
I believe that a better approach to handling the end-of-file condition is to build a layer of code around GET_LINE that immediately checks for end-of-file and returns a Boolean value (TRUE or FALSE). Theget_nextline procedure shown here embodies this principle.
/* Filename on companion disk: getnext.sp */
PROCEDURE get_nextline
(file_in IN UTL_FILE.FILE_TYPE,
line_out OUT VARCHAR2,
eof_out OUT BOOLEAN)
IS
BEGIN
UTL_FILE.GET_LINE (file_in, line_out);
eof_out := FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
line_out := NULL;
eof_out := TRUE;
END;
The get_nextline procedure accepts an already assigned file handle and returns two pieces of information: the line of text (if there is one) and a Boolean flag (set to TRUE if the end-of-file is reached, FALSE otherwise). Using get_nextline, I can now read through a file with a loop that has an EXIT statement.
My file_to_table procedure will look like the following after adding get_nextline:
/* Filename on companion disk: fil2tab2.sp */
PROCEDURE file_to_tableWith get_nextline, I no longer treat end-of-file as an exception. I read a line from the file until I am done, and then I close the file and exit. This is, I believe, a more straightforward and easily understood program.
(loc_in IN VARCHAR2, file_in IN VARCHAR2,
table_in IN OUT names_tabtype)
IS
-- Open file and get handle right in declaration
names_file CONSTANT UTL_FILE.FILE_TYPE :=
UTL_FILE.FOPEN (loc_in, file_in, 'R');
-- Counter used to create the Nth name.
line_counter INTEGER := 1;
end_of_file BOOLEAN := FALSE;
BEGIN
WHILE NOT end_of_file LOOP
get_nextline (names_file, table_in(line_counter), end_of_file);
line_counter := line_counter + 1;
END LOOP;
UTL_FILE.FCLOSE (names_file);
END;
The second program checks to see if a file exists. Notice that it creates a local procedure to handle the close logic (which is called both in the body of the function and in the exception section).
/* Filename on companon disk: filexist.sf */
CCREATE OR REPLACE FUNCTION file_exists
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
close_in IN BOOLEAN := FALSE)
RETURN BOOLEAN
IS
file_handle UTL_FILE.FILE_TYPE;
retval BOOLEAN;
PROCEDURE closeif IS
BEGIN
IF close_in AND UTL_FILE.IS_OPEN (file_handle) THEN
UTL_FILE.FCLOSE (file_handle);
END IF;
END;
BEGIN
-- Open the file.
file_handle := UTL_FILE.FOPEN (loc_in, file_in, 'R');
-- Return the result of a check with IS_OPEN.
retval := UTL_FILE.IS_OPEN (file_handle);
closeif;
RETURN retval;
EXCEPTION
WHEN OTHERS THEN
closeif;
RETURN FALSE;
END;
/
Because I found the INSTR function to be so useful, I figured that this same kind of operation would also really come in handy with operating system files. The line_with_text function coming up shortly returns the line number in a file containing the specified text. The simplest version of such a function would have a specification like this:
FUNCTION line_with_text (loc_in IN VARCHAR2, file_in IN VARCHAR2, text_in IN VARCHAR2)
RETURN INTEGER
In other words, given a location, a filename, and a chunk of text, find the first line in the file that contains the text. You could call this function as follows:
IF line_with_text ('h:\pers', 'names.vp', 'Hanubi') > 0 THEN
MESSAGE ('Josephine Hanubi is a vice president!');
END IF;
The problem with this version of line_with_text is its total lack of vision. What if I want to find the second occurrence in the file? What if I need to start my search from the tenth line? What if I want to perform a case-insensitive search? None of these variations are supported.
I urge you strongly to think through all the different ways a utility like line_with_text might be used before you build it. Don't just build for today's requirement. Anticipate what you will need tomorrow and next week as well.
For line_with_text, a broader vision would yield a specification like this:
FUNCTION line_with_textWow! That's a lot more parameter passing. Let's take a look at the kind of flexibility we gain from these additional arguments. First, the following table provides a description of each parameter.
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
text_in IN VARCHAR2,
occurrence_in IN INTEGER := 1,
start_line_in IN INTEGER := 1,
end_line_in IN INTEGER := 0,
ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER