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

SET SERVEROUTPUT ON

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

connect lob_demo/lob_demo@OEM1DB

/*
 ** ---------------------------------------
 ** GET VALUES FROM USER
 ** ---------------------------------------
*/

ACCEPT lobinpath  PROMPT 'Enter full path name for the input file: ';
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: ';


/*
 ** ---------------------------------------
 ** CREATE TABLE: bfile_tab
 ** ---------------------------------------
*/

DROP TABLE bfile_tab
/

CREATE TABLE bfile_tab (
  bfile_column BFILE
)
/

/*
 ** ---------------------------------------
 ** CREATE TABLE: utl_lob_test
 ** ---------------------------------------
*/

DROP TABLE utl_lob_test
/

CREATE TABLE utl_lob_test (
  blob_column BLOB
)
/

/*
 ** ---------------------------------------
 ** CREATE DIRECTORY: utl_lob_dir
 ** ---------------------------------------
*/

CREATE OR REPLACE DIRECTORY utl_lob_dir AS '&lobinpath';

/*
 ** ---------------------------------------
 ** -- CREATE BFILE RECORD : bfile_tab
 ** -- WRITE BLOB VALUE    : utl_lob_test
 ** ---------------------------------------
*/

DECLARE
   a_blob  BLOB;
   a_bfile BFILE := BFILENAME('UTL_LOB_DIR','&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;
/

/*
 ** -----------------------------------------------
 ** -- SELECT BLOB LOCATOR          : utl_blob_test
 ** -- SET LOGGING TO TRUE          : utl_lob
 ** -- UNLOAD BLOB VALUE TO OS FILE : utl_lob
 ** -----------------------------------------------
*/

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