Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Unloading LOBs to a File

by Jeff Hunter, Sr. Database Administrator

Overview

In versions of Oracle (8i and earlier), the standard package "DBMS_LOB" provides mechanisms for reading from a binary OS file and loading (writing) the data into a BLOB through PL/SQL using "DBMS_LOB.LoadFromFile". Not provided though, are any corresponding mechanisms for reading from a BLOB and then writing the data to an OS file. Text files can be written using the UTL_FILE package, but binary files of any size cannot. Indeed, Oracle9i introduced two new procedures UTL_FILE.PUT_RAW and UTL_FILE.GET_RAW to get around this shortcomming, but for those not ready to upgrade from Oracle8i to Oracle9i, this can be a problem.

The following article provides a PL/SQL package called UTL_LOB which uses the Oracle8 external procedure feature to perform the BLOB reads and OS binary file writes in a C function which executes outside the server.

This solution was written by Bill Bailey, Oracle Support Services, Development Tools - Languages.

1. Configure the listener.ora and tnsnames.ora files for external procedures and start the external procedure listener. Make sure to test your environment to ensure the external procedure is functioning properly.

   (See: Calling Operating System Commands from PL/SQL using External Procedures)

2. Compile the external procedure shared library using one of the two options below:
   • Manually compiling / linking shared library

     ORACLE_HOME=/u01/app/oracle/product/8.1.7; export ORACLE_HOME
     gcc -G -c -I $ORACLE_HOME/rdbms/demo -I $ORACLE_HOME/rdbms/public lob2file.c
     ld -r -o lob2file.so lob2file.o
     chmod 775 lob2file.so

     -- OR --

   • Use the makefile from Oracle

     make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_with_context SHARED_LIBNAME=lob2file.so OBJS=lob2file.o

   This should compile and link the lob2file.so shared library in your current working directory. Move this library to the desired location.

3. Create the LIBRARY database object and the PACKAGE SPEC and BODY.

   NOTE: This package can be created in any user's schema, but the user will require CREATE LIBRARY privileges as well as as privileges for creating the package itself. I used the user "LOB_DEMO".

   NOTE: Make sure to edit this file and change the USER CONNECTION as well as the CREATE LIBRARY object definition at the beginning of the script to the shared library created in the previous step. (You must include the full path name with the shared library name)

   From SQL*Plus,

   SQL> @lob2file

   Observe output to ensure there were no errors before continuing.

4. Test the package to be sure it works using the provided test: test_utl_lob.sql.

   From SQL*Plus,

   SQL> @test_utl_lob

   You will be prompted for a path and a filename of a file to load into the database BLOB for the test. Use any file you wish, but keep in mind; the larger the file, the longer it will take.

   (Note: In this case, the path and filename are entered SEPARATELY)

   You will also be prompted for two additional filenames, one each for the output file to be generated by UTL_LOB and the log file.

   (Note: In these cases, the full path including filename is entered)

   The test script will then call an anonymous PL/SQL block to create a BFILE based on your input and load the contents of the file into the BLOB column. Another anonymous block will then invoke UTL_LOB to create the output file and log any progress/error messages to the specified logfile.

Download Source Code

PL/SQL and C Program Modules

lob2file.c The following C program should be compiled and linked as the shared library: lob2file.so     lob2file.sql Edit this script to change the CONNECTION and the CREATE LIBRARY pathname/location of the external shared library.     test_utl_lob.sql Testing script. Follow prompts from the script.     Output.log Example output log from utl_lob.

Using the UTL_LOB Package

  The UTL_LOB package specification looks like the following:

     create or replace package utl_lob is

       procedure SetLogging(which BOOLEAN, a_log VARCHAR2);

       procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER);

     end utl_lob;

  Procedure SetLogging
  --------------------

  Used to enable/disable logging of progress/error messages to a log file. Enable
  logging if you are having problems to obtain detailed information about where
  and why the UnloadToFile procedure might be failing.

     Parameter
     ---------
     which - TRUE/FALSE -> enables/disables the logging feature.

     a_log - Any valid path name; specifies name and location of the log.

             DEFAULTS to /tmp/lob2file.log if omitted or if the specified
             file cannot be opened for some reason (e.g. permissions).

             This parameter is ignored if 'which' is set to FALSE.

  NOTE: Files are written using the effective user id of the extproc process
        which is started by the external procedure listener. Thus, you must
        ensure that the user id under which the external procedure listener
        executes has permissions to the desired files or directories.

        It is recommended that you use a separate listener to listen for
        external procedures. In this case, you can run the listener as any
        user you choose. Be aware, however, that all external procedures
        will execute with the effective permissions of this user. Running
        this process as 'oracle' or 'root' is not recommended as it could
        represent a security risk.

  Procedure UnloadToFile
  ----------------------

  Used to unload the contents of a BLOB from the database and write the contents
  to a binary OS file in the specified location.

     Parameter
     ---------
     a_lob - The LOB locator for the BLOB to be unloaded.

             The origin of the LOB locator MUST BE a database table. You
             cannot use a LOB locator initialized to empty_blob.

             For example, use something like the following:

                declare
                   lob_var BLOB := NULL;
                begin
                   select lob_col into lob_var from lob_table;
                   utl_lob.unloadtofile(lob_var, '/tmp/afile', status);
                end;

             The following alone WILL NOT work!

                declare
                   lob_var BLOB := empty_blob();
                begin
                   utl_lob.unloadtofile(lob_var, '/tmp/afile', status);
                end;

     a_file - The name of the file to which the contents should be written.

              See the note on permissions above which applies here as well.

     status - Return 0 for success and -1 for failure.

              For most cases where a failure occurs, an exception is also
              raised to provide additional details about the nature of the
              failure. In the event that the return value is -1 but there
              is no corresponding exception, this indicates that the call
              to OCIExtProcGetEnv() has failed.

              In all cases, the lo file will contain additional info about
              the error provided you have enabled logging.