DBA Tips Archive for Oracle
Unloading LOBs to a File
by Jeff Hunter, Sr. Database Administrator
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 "Installing / Configuring UTL_LOB
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.
(See: Calling Operating System Commands from PL/SQL using External Procedures)
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 --
make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_with_context SHARED_LIBNAME=lob2file.so OBJS=lob2file.o
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)
Observe output to ensure there were no errors before continuing.
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.cThe following C program should be compiled and linked as the shared library: lob2file.solob2file.sqlEdit this script to change the CONNECTION and the CREATE LIBRARY pathname/location of the external shared library.test_utl_lob.sqlTesting script. Follow prompts from the script.Output.logExample 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.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at email@example.com.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.