/*
 ** +----------------------------------------+
 ** | FILE        : lob2file.sql             |
 ** | AUTHOR      : Jeff Hunter              |
 ** | DATE        : 05-DEC-2001              |
 ** | TESTED WITH : 8 / 8i / 9i              |
 ** +----------------------------------------+
*/

/*
 ** -------------------------------------
 ** MAKE CONNECTION: lob_demo
 ** -------------------------------------
*/

connect lob_demo/lob_demo@OEM1DB

/*
 ** -------------------------------------
 ** CREATE LIBRARY: utl_lob_lib
 ** -------------------------------------
*/

CREATE OR REPLACE LIBRARY utl_lob_lib is '/u01/app/oracle/Programming/lobs/UnloadToFile/lob2file.so';
/

GRANT EXECUTE ON utl_lob_lib TO PUBLIC
/

/*
 ** -------------------------------------
 ** CREATE PACKAGE: utl_lob
 ** -------------------------------------
*/

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 PACKAGE BODY: utl_lob
 ** -------------------------------------
*/

CREATE OR REPLACE PACKAGE BODY utl_lob IS

  logSetting   BOOLEAN        := FALSE;
  logFileName  VARCHAR2(512)  := NULL;

  -- -----------------------
  -- PROCEDURE: (SetLogging)
  -- -----------------------
  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)
  -- -----------------------
  FUNCTION LobToFile(  a_lob BLOB
                     , a_file VARCHAR2
                     , a_log VARCHAR2
                     , logging BOOLEAN) RETURN BINARY_INTEGER IS
    EXTERNAL
    LIBRARY  utl_lob_lib
    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)
  -- -------------------------
  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
/