Citation:
Subject: Example: How To Unload LOBs to a File in Oracle 8i (SCR 1275)
Doc ID: Note:150104.1 Type: SCRIPT
Last Revision Date: 20-NOV-2002 Status: PUBLISHED
Overview
--------
Currently, the built-in package DBMS_LOB provides a mechanism for reading from
a binary OS file and loading the data into a BLOB (DBMS_LOB.LoadFromFile) thru
PL/SQL. However, there is no corresponding mechanism 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. Thus, users have had
to resort to 3GL solutions such as Pro*C or OCI.
The UTL_LOB package provided here offers a PL/SQL callable solution 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.
Program Notes
-------------
Installing and Using the UTL_LOB Package
----------------------------------------
This README explains how to install and use the package. There are some steps
required to configure the server for external procedures which must be done
prior to using this package. This README does not attempt to cover such steps.
Please consult your documentation for assistance in these areas.
Installing UTL_LOB
------------------
1.) Configure the listener.ora and tnsnames.ora files for external procedures
and start the external procedure listener. See docs for details.
2.) Test the configuration to be sure everything is set up correctly. This can
be done using the demo external procedure in $ORACLE_HOME/plsql/demo.
Note 70638.1 which is available from Support provides additional information
on configuring external procedures and testing your configuration.
3.) Compile the external procedure shared library as follows:
make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_with_context \
SHARED_LIBNAME=lob2file.so OBJS=lob2file81.o
This should compile and link the lob2file.so shared library in your
current working directory. Move this library to the desired location.
4.) 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)
From SQL*Plus,
SQL> @lob2file81
When prompted for the library location, enter the full path including
the shared library name for the shared library created in step 3.
Observe output to ensure there were no errors before continuing.
5.) Test the package to be sure it works using the provided test harness.
From SQL*Plus,
SQL> @harness81
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 harness 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.
If all of the above worked as expected, you are ready to use UTL_LOB in your
own PL/SQL applications. See section below for usage details.
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.
See Note 70638.1 and your documentation for further details.
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.
For an simple example of UTL_LOB usage, see the harness80.sql script.
Have Fun!
References
----------
Caution
-------
The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.
Program
-------
- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -
--*****************************************************************************
--
-- Name: harness.sql
--
-- Author: Bill Bailey
--
-- Created: 6/18/99
--
-- Description: This is a test harness for testing the utl_lob package.
-- It creates the necessary tables, inserts some test data,
-- and invokes the UnloadToFile function to unload the data
-- to an OS file.
--
--*****************************************************************************
set serveroutput on
accept lobindir prompt 'Enter full path to input directory: ';
accept lobinfile prompt 'Enter file name of input file: ';
accept loboutfile prompt 'Enter full path of output file including file name: ';
accept loblogfile prompt 'Enter full path of log file including file name: ';
drop table bfile_tab;
create table bfile_tab (bfile_column BFILE);
drop table utl_lob_test;
create table utl_lob_test (blob_column BLOB);
create or replace directory utllobdir as '&lobindir';
declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','&lobinfile');
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/
declare
a_blob BLOB;
status NUMBER;
begin
select blob_column into a_blob
from utl_lob_test;
utl_lob.SetLogging(TRUE, '&loblogfile');
utl_lob.UnloadToFile(a_blob, '&loboutfile', status);
dbms_output.put_line('Exit status = ' || status);
end;
/
exit
/*****************************************************************************
*
* Name: lob2file.c
*
* Author: Bill Bailey
*
* Created: 6/14/99
*
* Description: This C function is intended for use as an external procedure
* which unloads the contents of a BLOB into a binary OS file.
*
*****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <errno.h>
#include <oci.h>
#include <ociextp.h>
#define DEFAULT_CHUNK_SIZE 1024
static int logging;
static char logfile[512];
static FILE *logfilep = NULL;
int lob2file ( OCILobLocator *a_lob, /* the LOB */
short lbind, /* LOB indicator */
char *path, /* file to write */
short pind, /* file indicator */
int plen, /* filename length */
char *lpath, /* logfile name */
short lpind, /* logfile indicator */
int lplen, /* logfile name length */
int logit, /* logging enabled? */
OCIExtProcContext *ctxt /* OCI Context */
)
{
sword errnum = 0;
OCIEnv *envhp = NULL;
OCISvcCtx *svchp = NULL;
OCIError *errhp = NULL;
char lobfile[512];
FILE *lobfilep = NULL;
/*
* If required, open the log file for writing
* Use the user provided logfile name if possible
* Otherwise, default the logfile to /tmp/lob2file.log
*/
logging = logit;
if (logging)
{
if (lpind == -1 || lplen == 0 || lplen >= 512)
{
strcpy(logfile, "/tmp/lob2file.log");
}
else
{
strncpy(logfile, lpath, lplen);
logfile[lplen] = '\0';
}
logfilep = fopen(logfile, "w");
if (logfilep == NULL)
{
if ((logfilep = fopen("/tmp/lob2file.log", "w")) != NULL)
{
fprintf(logfilep, "Error: Unable to open logfile %s\n", logfile);
fprintf(logfilep, "Error: errno = %d\n", errno);
}
}
}
/*
* Retrieve the environment, service context, and error handles
*/
if ((errnum = OCIExtProcGetEnv(ctxt, &envhp,
&svchp, &errhp)) != OCIEXTPROC_SUCCESS)
{
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: Call to OCIExtProcGetEnv failed\n");
fprintf(logfilep, "Error: OCIExtProcGetEnv returned %d\n", errnum);
fclose(logfilep);
return -1;
}
}
/*
* Verify that the user has provided a name for the output file
*/
if (pind == -1 || plen == 0)
{
char *errmsg = "Pathname is null or empty string";
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: %s\n", errmsg);
fclose(logfilep);
}
errnum = 20001;
OCIExtProcRaiseExcpWithMsg(ctxt, errnum, (text *)errmsg, strlen(errmsg));
return -1;
}
else /* Use the provided name */
{
strncpy(lobfile, path, plen);
lobfile[plen] = '\0';
}
/*
* Verify that the user has provided a valid LOB locator
*/
if (lbind == -1)
{
char *errmsg = "LOB locator is null";
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: %s\n", errmsg);
fclose(logfilep);
}
errnum = 20002;
OCIExtProcRaiseExcpWithMsg(ctxt, errnum, (text *)errmsg, strlen(errmsg));
return -1;
}
if (logging && logfilep != NULL)
fprintf(logfilep, "Opening OS file in write mode\n");
/*
* Open the output file for writing
*/
if ((lobfilep = fopen(lobfile, "wb")) != NULL)
{
dvoid *chunk;
ub4 cksz = 0, totsz = 0;
if (logging && logfilep != NULL)
fprintf(logfilep, "Getting total size for LOB\n");
if (checkerr(ctxt, errhp,
OCILobGetLength(svchp, errhp, a_lob, &totsz)) != 0)
return -1;
/*
* For 8.0.X the OCILogGetChunkSize will not have been called.
* IN this case, reset the chunk size to 1K.
*/
if (cksz == 0) cksz = DEFAULT_CHUNK_SIZE;
if (logging && logfilep != NULL)
fprintf(logfilep,
"Allocating %d bytes of memory for LOB chunks\n",
(int) cksz );
/*
* Dynamically allocate enough memory to hold a single chunk
*/
if ((chunk = OCIExtProcAllocCallMemory(ctxt, (size_t) cksz)) != NULL)
{
int cnt = 1;
ub4 amt = cksz, offset = 1;
/*
* Read data from the LOB and write it to the file while
* more data remains.
*/
while (offset < (int)totsz)
{
if (logging && logfilep != NULL)
fprintf(logfilep,
"Reading chunk %d starting at %d for max %d bytes\n",
cnt, (int) offset, (int) amt);
errnum = OCILobRead(svchp, errhp, a_lob, &amt, offset,
chunk, cksz, (dvoid *) 0,
(sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
(ub2) 0, (ub1)SQLCS_IMPLICIT);
if (checkerr(ctxt, errhp, errnum) != 0) return -1;
if (logging && logfilep != NULL)
fprintf(logfilep,
"Successfully read chunk containing %d bytes\n",
(int) amt);
if (logging && logfilep != NULL)
fprintf(logfilep,
"Writing %d bytes of chunk %d to file %s\n",
(int) amt, cnt, lobfile);
if (fwrite((void *)chunk, (size_t)1, (size_t)amt, lobfilep) == amt)
{
if (logging && logfilep != NULL)
fprintf(logfilep, "Successfully wrote %d bytes to file %s\n",
(int) amt, lobfile);
}
else
{
char *errmsg = "Write to OS file failed";
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: %s\n", errmsg);
fprintf(logfilep, "Error: errno = %d\n", errno);
}
errnum = 20003;
OCIExtProcRaiseExcpWithMsg(ctxt, errnum,
(text *)errmsg, strlen(errmsg));
return -1;
}
cnt++;
offset += amt;
}
if (logfilep != NULL) fclose(logfilep);
fclose(lobfilep);
return 0;
}
else
{
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: Unable to allocate memory\n");
fclose(logfilep);
}
return -1;
}
}
else
{
char *errmsg = "Unable to open file";
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: %s %s\n", errmsg, lobfile);
fprintf(logfilep, "Error: errno = %d\n", errno);
fclose(logfilep);
}
errnum = 20003;
OCIExtProcRaiseExcpWithMsg(ctxt, errnum,
(text *)errmsg, strlen(errmsg));
return -1;
}
}
int checkerr(OCIExtProcContext *ctxt, OCIError *errhp, sword status)
{
sword errnum = 0;
text errbuf[512];
switch (status)
{
case OCI_SUCCESS_WITH_INFO:
errnum = 20004;
strcpy((char *)errbuf, "Error: OCI_SUCCESS_WITH_INFO");
break;
case OCI_NO_DATA:
errnum = 20005;
strcpy((char *)errbuf, "Error: OCI_NO_DATA");
break;
case OCI_NEED_DATA:
errnum = 20006;
strcpy((char *)errbuf, "Error: OCI_NEED_DATA");
break;
case OCI_INVALID_HANDLE:
errnum = 20007;
strcpy((char *)errbuf, "Error: OCI_INVALID_HANDLE");
break;
case OCI_STILL_EXECUTING:
errnum = 20008;
strcpy((char *)errbuf, "Error: OCI_STILL_EXECUTING");
break;
case OCI_CONTINUE:
errnum = 20009;
strcpy((char *)errbuf, "Error: OCI_CONTINUE");
break;
case OCI_ERROR:
(void)OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL,
(sb4 *) &errnum, (text *) errbuf,
(ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
break;
default:
break;
}
if (errnum != 0)
{
if (logging && logfilep != NULL)
{
fprintf(logfilep, "Error: %d %s\n", errnum, errbuf);
fclose(logfilep);
}
(void)OCIExtProcRaiseExcpWithMsg(ctxt, errnum, errbuf, strlen(errbuf));
}
return errnum;
}
--****************************************************************************
--
-- Name: lob2file.sql
--
-- Author: Bill Bailey
--
-- Created: 6/14/99
--
-- Description: Create the library, PLSQL package and call spec for an
-- external procedure used to unload a BLOB to an OS file.
--
--****************************************************************************
accept liblocation prompt 'Enter full path to utl_lob shared library: ';
-- NOTE: full path includes the name of the library itself with extension
create or replace library utlloblib is '&liblocation';
/
grant execute on utlloblib to public;
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;
/
show errors
create or replace package body utl_lob is
logSetting BOOLEAN := FALSE;
logFileName VARCHAR2(512) := NULL;
procedure SetLogging(which BOOLEAN, a_log VARCHAR2) is
begin
logSetting := which;
if (logSetting = TRUE) then
logFileName := a_log;
else
logFileName := NULL;
end if;
end;
function LobToFile(a_lob BLOB, a_file VARCHAR2,
a_log VARCHAR2, logging BOOLEAN) return BINARY_INTEGER is
external
library utlloblib
name "lob2file"
with context
parameters ( a_lob OCILOBLOCATOR,
a_lob INDICATOR SHORT,
a_file STRING,
a_file INDICATOR SHORT,
a_file LENGTH INT,
a_log STRING,
a_log INDICATOR SHORT,
a_log LENGTH INT,
logging INT,
CONTEXT,
RETURN );
procedure UnloadToFile(a_lob BLOB, a_file VARCHAR2, status OUT NUMBER) is
begin
status := LobToFile(a_lob, a_file, logFileName, logSetting);
end;
end utl_lob;
/
show errors
grant execute on utl_lob to public;
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -
