convert -->blob (file) to unix file with utl_file. 500 points if there is solution tomorrow!!!

    the winner it's the first that his script works !!!

What version of Oracle?

Read this:

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)
through 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 Oracle Call Interface (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 that executes outside the data server.
 
 
Program Notes
-------------
 
This article explains how to install and use the package. There are some steps
required to configure the server for external procedures that must be done
prior to using this package. This article does not attempt to cover such steps.
Please consult your Oracle 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.  
 
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.
 

 
3.) Compile the external procedure shared library as follows:
 
    make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_with_context \
      SHARED_LIBNAME=lob2file80.so OBJS=lob2file80.o
 
    This should compile and link the lob2file80.so shared library in your
    current working directory. Move this library to the desired location.
 
4.) Create the LIBRARY database object, the package specification, and package body.
 
    (Note: This package can be created in any schema, but the
           user will require CREATE LIBRARY privileges as well as
           as privileges for creating the package itself.)
 
    From SQL*Plus,
 
       SQL> @lob2file80.sql
 
    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> @harness80.sql
 
    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
      that 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 access 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.
 
            Refer to 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 log file will contain additional information
            about the error, provided you have enabled logging.
 
For an simple example of UTL_LOB usage, see the harness80.sql script.
 
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: harness80.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: lob2file80.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: lob2file80.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  - - - - - - - - - - - - - - - -
 
 
Sample Output
-------------
 
Review the target OS file.
 

.com/Articles/8i/ExportBlob.asp

Thanks to all ....

To Yoren :

   Bit I have some questions ....

Works in UNix ?

What happen with utl_file_dir parameter ?

CAn I create directories over any unix directory ?

Or I need configure the init.ora file (located in $ORACLE_HOME/dbs directory)

      
      

Yes, they both should work in UNIX. I don't think the utl_file_dir parameter applies here; you can put files anywhere you want. You just have to create a DIRECTORY in Oracle. I believe the "oracle" user will need write access to that directory. You shouldn't need to do anything to your init.ora file.

By MOSORIO
   My oracle version it's 8.1.7 !!!

In that case, you can write the file anywhere. You don't need to create an Oracle directory.