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 dba_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.
----------------------------------------------------------------
UTL_FILE
This package was introduced in the Oracle
version 7.3.4 for reading and writing any
operating system flat file that
is
accessible in our database server. This new
feature allowed us to write the flat file data
into the database tables along with the
flexibility of PL/SQL with an ease.
The different objects available in this package
are described below.
Directory
Prior to the Oracle release 9i, the UTL_FILE_DIR
initialization parameter was used for
determining the directories which can be read or
written by the UTL_FILE package. This became a
security threat as the directories present
inside this parameter are accessible by any
other database user. To mitigate this security
concern, Oracle has introduced the CREATE
DIRECTORY syntax for creating directories with
more security for working alongside the OS
files. Either read or write access to the
directories created using this syntax can be
granted to individual users or roles.
The prototype for creating a directory is shown
below,
CREATE DIRECTORY <Directory_name> AS
'<Directory_path>';
The prototype for granting access to the
directories to the database users is shown
below,
GRANT [READ] | [WRITE] ON DIRECTORY
<Directory_name> TO <Database_user>;
FILE_TYPE Record Type
This type is actually a PL/SQL record type which
is used for holding all the information related
to the file for processing by the UTL_FILE
package. This type acts as a handle for the
consecutive calls to the UTL_FILE package to
manipulate the file content. We must not
reference the individual attributes of this type
or manipulate them as it is specifically to be
handled by the UTL_FILE package.
The prototype of the FILE_TYPE record type is
shown below,
TYPE file_type IS RECORD (
id
BINARY_INTEGER,
datatype
BINARY_INTEGER,
byte_mode
BOOLEAN);
·
ID
parameter returns a numeric file handler number
which is generated internally for processing.
·
DATATYPE
parameter indicates the
data type
of the file.
·
BYTE_MODE
parameter is to indicate whether the file is
open as a text file or as a binary file.
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
