If the developer has done any file processing
(reading or writing on the file system), he is
certain to already be familiar with UTL_FILE.
This package is similar to how C or C++
reads/writes files on a file system. The general
process is to set a pointer to a file’s location
and name, open it, manipulate the contents, then
close it.
Oracle provides an extensive set or list of
exceptions which can occur when dealing with
files on the operating system. If it happens
that the developer is using UTL_FILE in several
places, it would be worthwhile to bundle these
exceptions into their own package to support
code reuse.
Starting with Oracle 10g, an enhancement to
UTL_FILE is the package’s ability to write out
the contents of a stored BLOB to the file
system. Put another way, a stored JPEG file can
be output. Aside from having the appropriate
permissions, one may have to account for the
file size and write the contents out in PL/SQL
variable-sized chunks of 32KB at a time.
Another enhancement is the departure from having
the location(s) specified by
utl_file_dir in the parameter
file. Previously, more than one location could
be specified using this parameter as long as the
locations all appeared together, meaning no
other parameters in between two locations. The
latest recommendation is to use a directory
object. A directory location is identified to
Oracle and stored within the database. Users are
then granted read or write on the directory.
This allows for multiple locations and more
granular control of where a user can read/write.
Query the
all_directories data dictionary
view for a description of all directories
available to the user running the query.
As an example, take the Word document inserted
as a BLOB (back in the UTL_COMPRESS section) and
write back out to the file system. If the script
was ran through as is, there will be a BLOB
stored in the record where INDX=1 or the
filename can be used. Here is an example of a
procedure to write a BLOB to the MYDIR
directory.
CREATE OR REPLACE
PROCEDURE WriteBLOBToFILE
(infilename
IN VARCHAR2) IS
v_blob
BLOB;
blob_length
INTEGER;
out_file
UTL_FILE.FILE_TYPE;
v_buffer
RAW(32767);
chunk_size
BINARY_INTEGER := 32767;
blob_position
INTEGER := 1;
BEGIN
-- Retrieve the
BLOB for reading
-- This uses a
Word document
SELECT y INTO
v_blob FROM compress_blob WHERE indx = 1;
-- Retrieve the
SIZE of the BLOB
blob_length:=DBMS_LOB.GETLENGTH(v_blob);
-- Open a
handle to the location of the BLOB file
-- The location
is the MYDIR directory
-- wb = write
in byte mode, 10g new feature
-- The out_file
picks up the name of the filename passed in
out_file :=
UTL_FILE.FOPEN
('MYDIR',
infilename, 'wb', chunk_size);
-- Write the
BLOB to file in chunks
WHILE
blob_position <= blob_length LOOP
IF
blob_position + chunk_size - 1 > blob_length
THEN
chunk_size :=
blob_length - blob_position + 1;
END IF;
DBMS_LOB.READ(v_blob,
chunk_size, blob_position, v_buffer);
UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
blob_position
:= blob_position + chunk_size;
END
LOOP;
-- Close the
file handle
UTL_FILE.FCLOSE
(out_file);
END;
/
Compiling this procedure and executing it…
SQL> exec writeblobtofile('A_57KB_Word_doc.doc');
PL/SQL procedure
successfully completed.
…writes the “A_57KB_Word_doc.doc” file back into
C:\Temp. The ability to write files out like
this must be safeguarded. Imagine the damage a
malicious – or not – user can wreak by being
able to what amounts to download from the
database any document.
The UTL_FILE package contains an amazing degree
of potential. The FREMOVE and FRENAME procedures
do exactly what their names imply. So just as
potentially dangerous as writing files out to
the file system, misuse of these two procedures,
inadvertent or otherwise, can be disastrous.
Imagine someone playing “what if” with database
files. “I wonder if I can read, write, remove,
or rename a database file with UTL_FILE?”
Clearly, one does not want users being able to
create directory objects on database file
locations.
Oracle’s documentation is much more complete
with respect to this package and many examples
are provided therein.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
