By Steven Feuerstein
Oracle9i Release 2 UTL_FILE enhances file I/O.
Some would argue that you can contain and represent the entire world, perhaps universe, in an Oracle database. That may be true, but there are those of us who still want to be able to work with operating system (OS) files from within our PL/SQL programs. Given that desire, developers have long had a love-hate relationship with the UTL_FILE supplied package
For years, UTL_FILE provided a way to read and write files from within PL/SQL. Some in the development community, however, have wanted to do more than read and write lines sequentially in files. Some developers wanted to delete a file, rename a file, copy a file, and perform other typical file-related operations, but UTL_FILE didn't support these operations.
Oracle9i Release 2 lifts those troublesome UTL_FILE restrictions. This article explores several new and very useful additions to the UTL_FILE package in Oracle9i Release 2, including:
UTL_FILE.FREMOVE Remove a file.
UTL_FILE.FRENAME Rename a file, and also, in effect, move files.
UTL_FILE.FCOPY Copy all or part of one file to another.
UTL_FILE.FGETATTR Retrieve attributes of the file, such as its length.
In addition to new programs, UTL_FILE also now allows database-defined directory objects to specify the location of OS directories. While it's not possible to cover all of the new features of UTL_FILE, you'll certainly find enough in this article to get you excited about new possibilities for file I/O within native PL/SQL code.
Before Oracle9i Release 2, UTL_FILE required that you explicitly name the location of the file when you called FOPEN to open the file (for read or write). This was a less-than-optimal implementation, because it usually meant that developers would hard-code those locations in multiple places in the application. If that directory changed, you had a messy clean-up job.
In Oracle9i Release 2, you can now specify the name of an Oracle directory object for the file system location of the directory. This technique "hides" the actual operating system location. If that location ever needs to change, you need to update only the directory object definition; all calls to FOPEN, FREMOVE, FRENAME, and FCOPY are unaffected.
To create a directory object, you will need the CREATE ANY DIRECTORY privilege. You then define a new directory object, as shown in this example:
CREATE OR REPLACE DIRECTORY DEVELOPMENT_ DIR as '/dev/source'; CREATE OR REPLACE DIRECTORY TEST_DIR as '/test/source';
You should know that Oracle Database does not validate the location you specify when you create a directory object. Another thing to keep in mind is that when you specify the name of a directory object in a call to, say, UTL_FILE.FOPEN, it is treated as a case-sensitive string. In other words, if you do not specify the directory object name in uppercase, the operation will fail.
After creating the directory object, you can grant authority to specific users to work with that directory object as follows:
GRANT READ ON DIRECTORY DEVELOPMENT_DIR to senior_developer;
Finally, you can query the contents of ALL_DIRECTORIES to determine which directory objects are available in the currently connected schema. Here is a Boolean function that tells you if the specified directory object is available to you:
CREATE OR REPLACE FUNCTION dir_available ( dir_in IN VARCHAR2, uppercase_in IN BOOLEAN := TRUE ) RETURN BOOLEAN IS v_dir VARCHAR2 (100) := dir_in; retval BOOLEAN; dummy CHAR (1); BEGIN IF uppercase_in THEN v_dir := UPPER (v_dir); END IF; SELECT 'x' INTO dummy FROM all_directories WHERE directory_name = l_dir; RETURN TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE; END;
One advantage to building utilities like this one is that you can easily add sophisticated handling of the case of the directory to avoid formatting errors, such as forgetting to specify the directory name in uppercase.
In the bad old days, the only way to copy a file using UTL_FILE was to write a lot of code to read the content of one file (line by line) and then write it to a new file (line by line). Now, you simply ask UTL_FILE to do the work for you. In Listing 1, I use UTL_FILE.FCOPY to perform a selective backup—a copy of a single file from the development directory to the archive directory.Code Listing 1: Using FCOPY to copy a file
DECLARE file_suffix VARCHAR2 (100) := TO_CHAR (SYSDATE, 'YYYYMMDDHHMISS'); BEGIN - Copy the entire file... UTL_FILE.fcopy ( src_location => 'DEVELOPMENT_DIR', src_filename => 'archive.zip', dest_location => 'ARCHIVE_DIR', dest_filename => 'archive' || file_suffix || '.zip' ); END;
You can also use FCOPY to copy just a portion of a file. To do this, you indicate the starting and ending line numbers of the file you want to copy. Suppose that I have a text file containing the names of the annual winners of my son's bowling league championship. I started recording the names in 1990 and would like to move all the names prior to 1996 into another file. I can do that by taking advantage of the fifth and sixth arguments of the following procedure:
- Copy just a part of a file UTL_FILE.fcopy ( src_location => 'WINNERS_DIR', src_filename => 'names.txt', dest_location => 'OLD_NEWS_DIR', dest_filename => 'prevnames.txt', start_line => 1, end_line => 6 );
Code Listing 2: Using FREMOVE to delete a file
Deleting a file is also now a breeze. Suppose that after moving my zip file to the archive directory, I would like to remove it to free up some disk space. This is the perfect opportunity to deploy UTL_FILE.FREMOVE. Notice that in Listing 2, I also include an explicit exception handler for the new UTL_FILE.DELETE_FAILED exception. This approach allows me to flag a delete failure (due, for example, to the fact that I do not have the necessary privileges).
BEGIN UTL_FILE.fremove ( src_location => 'DEVELOPMENT_DIR', src_filename => 'archive.zip' ); EXCEPTION - If you call FREMOVE, you should check explicitly - for deletion failures. WHEN UTL_FILE.delete_failed THEN ... Deal with failure to remove END;
Code Listing 3: Using FRENAME to move a file
I can also combine copy and remove operations into a single step by calling the UTL_FILE.FRENAME procedure. This handy utility allows me to either rename a file in the same directory or rename a file to another name and location (in effect, moving that file). The example in Listing 3 uses FRENAME to move a file: archive.zip.
DECLARE file_suffix VARCHAR2 (100) := TO_CHAR (SYSDATE, 'YYYYMMDD'); BEGIN - Rename/move the entire file in a single step. UTL_FILE.frename ( src_location => 'DEVELOPMENT_DIR', src_filename => 'archive.zip', dest_location => 'ARCHIVE_DIR', dest_filename => 'archive' || file_suffix || '.zip', overwrite => FALSE ); EXCEPTION WHEN UTL_FILE.frename_failed THEN ... Deal with failure to rename END;
Again, when working with FRENAME, you should include an exception handler that will trap very specifically a rename failure.
How big is this file? Does a specific file even exist? What is the block size of my file? Such questions are no longer mysteries to be solved only with the help of an OS command. UTL_FILE. FGETATTR now provides all that information in a single native procedure call. Perhaps the best way to take advantage of FGETATTR is to build your own function—on top of the built-in function—that answers a single question, such as the example in Listing 4 that returns a file's size (length).Code Listing 4: Using FGETATTR as basis for function to find file size
CREATE OR REPLACE FUNCTION flength ( location_in IN VARCHAR2, file_in IN VARCHAR2 ) RETURN PLS_INTEGER IS TYPE fgetattr_t IS RECORD ( fexists BOOLEAN, file_length PLS_INTEGER, block_size PLS_INTEGER ); fgetattr_rec fgetattr_t; BEGIN UTL_FILE.fgetattr ( location => location_in, filename => file_in, fexists => fgetattr_rec.fexists, file_length => fgetattr_rec.file_length, block_size => fgetattr_rec.block_size ); RETURN fgetattr_rec.file_length; END flength; /
With the function in place, I can now very easily obtain the file size without having to declare variables for each of the attributes retrieved by FGETATTR, as in the following PL/SQL example:
how_big := flength ('DEVELOPMENT_DIR', 'all_the_rules.pkg');
This FLENGTH function also includes code to find a file's block size and determine whether a file exists. You can easily apply the same technique that is used to find the file length (size)—shown in Listing 4—to create functions to obtain the block size and return a simple Boolean value to determine whether or not the file exists.
Another new capability in UTL_FILE is the "auto-flush" feature of PUT_LINE. When you write data out to a file in your program, it doesn't necessarily immediately appear in that file, ready to be read. Instead, the OS almost certainly utilizes asynchronous I/O, filling a buffer with the results of multiple write operations before actually sending those bits to disk.
While asynchronous I/O improves performance, it can be an irritation for programmers or support staff who need to see the contents of, say, a log file immediately.
UTL_FILE now includes a new parameter in the UTL_FILE.PUT_LINE procedure so that a developer can specify that she wants the line of text supplied to be flushed immediately to disk. The procedure in Listing 5 demonstrates this technique.Code Listing 5: Using PUT_LINE to write to disk immediately
CREATE OR REPLACE PROCEDURE log_error ( error_in IN PLS_INTEGER, message_in IN VARCHAR2 := NULL ) IS v_handle UTL_FILE.file_type; BEGIN v_handle := UTL_FILE.fopen ( location => 'ERROR_LOG_DIR', filename => 'error.log', open_mode => 'a', max_linesize => 32767 ); UTL_FILE.put_line ( FILE => v_handle, buffer => 'Error ' || error_in || ' occurred at ' || TO_CHAR ( SYSDATE, 'YYYYMMDD HH:MI:SS' ) || ': ' || NVL ( message_in, SQLERRM ), autoflush => TRUE ); UTL_FILE.fclose (v_handle); END;
For PL/SQL to be used widely and successfully, it must strongly support a wide range of functionality. Interaction with OS files is certainly a key part of that range.
Oracle has once again responded to the requests of its developer community and improved core PL/SQL functionality. UTL_FILE for Oracle9i Release 2 now offers a significantly higher level of support for common operations.
Steven Feuerstein (email@example.com) has been developing software since 1980 and is an authority on the PL/SQL language. Steven is the author of six books on PL/SQL, including Oracle PL/SQL Best Practices and Oracle PL/SQL Programming (all from O'Reilly & Associates). He is a senior technology advisor for Quest Software.