Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle UTL_FILE Package
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.