rem
rem $Header: adlplsql.sql 11-aug-99.10:59:04 hyeh Exp $
rem
rem adlplsql.sql
rem
rem Copyright (c) 1999, Oracle Corporation.  All rights reserved.
rem
rem    NAME
rem      adlplsql.sql - LOB samples for App Dev guide
rem
rem    DESCRIPTION
rem      This demo file provides PL/SQL procedure examples of accessing
rem      and manipulating CLOB, BLOB, and BFILE locators.
rem
rem    NOTES
rem      This file uses a data file 'adldemo.dat' for BFILE locators.
rem      Directories are created in adlsetup.sql.
rem
rem    MODIFIED   (MM/DD/YY)
rem    hyeh        08/11/99 - set session format
rem    mjaeger     07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem    ytsai       02/22/99 - add comment
rem    ytsai       02/01/99 - Created
rem

REMARK >>>> Set System Variables For Current SQLPlus Session <<<<
SET FEEDBACK 1
SET NUMWIDTH 10
SET PAGESIZE 24
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET DEFINE '^'

SET ECHO ON
SET SERVEROUTPUT ON
CONNECT adldemo/adldemo

/* chapter 2 examples follow */

/* setting an internal LOB to Empty */
DECLARE
  Lob_loc  BLOB;
BEGIN
  INSERT INTO a_table VALUES (EMPTY_BLOB()) RETURNING blob_col INTO Lob_loc;
  /* Now use the locator Lob_loc to populate the BLOB with data */
END;
/
/* PL/SQL procedure successfully completed. */

/*  */

/* Note that the example procedure loadLOBFromBFILE_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('FRAME_DIR', 'adldemo.dat');
  Amount         INTEGER := 4000;
BEGIN

   SELECT Frame INTO Dest_loc FROM Multimedia_tab
      WHERE Clip_ID = 3
        FOR UPDATE;
    /* Opening the FILE is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
    /* Load the internal LOB with data from the BFILE */
    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE(Dest_loc);
    /* Closing the FILE is mandatory: */
    DBMS_LOB.CLOSE(Src_loc);
    COMMIT;
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure lobIsOpen_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE lobIsOpen_proc IS
  Lob_loc     BLOB;
  Retval      INTEGER;
BEGIN
  SELECT Frame INTO Lob_loc  FROM Multimedia_tab where Clip_ID = 1;
   /* Opening the LOB is optional: */
DBMS_LOB.OPEN (Lob_loc , DBMS_LOB.LOB_READONLY);
/* See if the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1 meaning that the LOB is open. */
END;
/
/* Procedure created. */

/*  */
/* Note that the example procedure checkOutLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE checkOutLOB_proc IS
    Lob_loc     CLOB;
    Buffer      VARCHAR2(32767);
    Amount      BINARY_INTEGER := 32767;
    Position    INTEGER := 2147483647;
BEGIN

 /* Select the LOB: */
    SELECT Intab.Transcript INTO Lob_loc
        FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
                   WHERE Mtab.Clip_ID = 1) Intab
         WHERE Intab.Segment = 1;
    /* Opening the LOB is optional: */
DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
LOOP
        DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
        /* Process the buffer: */
        Position := Position + Amount;
    END LOOP;
    /* Closing the LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE (Lob_loc);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data');
END;
/
/*  */
/* Note that the example procedure checkInLOB_proc is not part of the
   DBMS_LOB package. */

CREATE OR REPLACE PROCEDURE checkInLOB_proc IS
    Lob_loc        CLOB;
    Buffer         VARCHAR2(32767);
    Amount         BINARY_INTEGER := 32767;
    Position       INTEGER := 2147483647;
    i              INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Intab.Transcript INTO Lob_loc
        FROM TABLE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
                   WHERE Clip_ID = 2) Intab
        WHERE Intab.Segment = 1
        FOR UPDATE;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
    FOR i IN 1..3 LOOP
        /* Fill the Buffer with data to be written. */
        /* Write data: */
        DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
        Position := Position + Amount;
    END LOOP;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/

/*  */

/* Example: Read Data from a LOB Using PL/SQL (DBMS_LOB Package) */
/* Note that the example procedure readLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE readLOB_proc IS
    Lob_loc           BLOB;
    Buffer            RAW(32767);
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 1000;
    Chunksize         INTEGER;
BEGIN
    /* Select the LOB: */
   SELECT Frame INTO Lob_loc
      FROM Multimedia_tab
      WHERE Clip_ID = 1;
   /* Find out the chunksize for this LOB column: */
   Chunksize := DBMS_LOB.GETCHUNKSIZE(Lob_loc);
   IF (Chunksize < 32767) THEN
      Amount := (32767 / Chunksize) * Chunksize;
   END IF;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Read data from the LOB: */
   DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure substringLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE substringLOB_proc IS
    Lob_loc           BLOB;
    Amount            BINARY_INTEGER := 32767;
    Position          INTEGER := 1024;
    Buffer            RAW(32767);
BEGIN
    /* Select the LOB: */
    SELECT Sound INTO Lob_loc FROM Multimedia_tab
        WHERE Clip_ID = 1;
    /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
    Buffer := DBMS_LOB.SUBSTR(Lob_loc, Amount, Position);
    /* Process the data */
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
END;
/
/* Procedure created; but probably need some actual data */


/*  */

/* Note that the example procedure compareTwoLOBs_proc is not part of the
   DBMS_LOB package. */
/* Procedure created. */
CREATE OR REPLACE PROCEDURE compareTwoLOBs_proc IS
    Lob_loc1            BLOB;
    Lob_loc2            BLOB;

    Amount              INTEGER := 32767;
    Retval              INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Frame INTO Lob_loc1 FROM Multimedia_tab
        WHERE Clip_ID = 1;
    SELECT Frame INTO Lob_loc2 FROM Multimedia_tab
        WHERE Clip_ID = 2;
   /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc1, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN (Lob_loc2, DBMS_LOB.LOB_READONLY);
   /* Compare the two frames: */
    retval := DBMS_LOB.COMPARE(Lob_loc1, Lob_loc2, Amount, 1, 1);
    IF retval = 0 THEN
       DBMS_OUTPUT.PUT_LINE('Processing for equal frames');
    ELSE
       DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames');
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
END;
/

/*  */

/* Note that the example procedure instringLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE instringLOB_proc IS
    Lob_loc        CLOB;
    Pattern        VARCHAR2(30) := 'children';
    Position       INTEGER := 0;
    Offset         INTEGER := 1;
    Occurrence     INTEGER := 1;
BEGIN
    /* Select the LOB: */
    SELECT Story INTO Lob_loc
        FROM Multimedia_tab
        WHERE Clip_ID = 1;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
    /* Seek for the pattern: */
    Position := DBMS_LOB.INSTR(Lob_loc, Pattern, Offset, Occurrence);
    IF Position = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Pattern not found');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The pattern occurs at '
                || position);
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
END;
/
/* Procedure created. */


/*  */

/* Note that the example procedure instringLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE instringLOB_proc IS
    Lob_loc        CLOB;
    Pattern        VARCHAR2(30) := 'children';
    Position       INTEGER := 0;
    Offset         INTEGER := 1;
    Occurrence     INTEGER := 1;
BEGIN
    /* Select the LOB: */
    SELECT Story INTO Lob_loc
        FROM Multimedia_tab
        WHERE Clip_ID = 1;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
    /* Seek the pattern: */
    Position := DBMS_LOB.INSTR(Lob_loc, Pattern, Offset, Occurrence);
    IF Position = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Pattern not found');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The pattern occurs at '
                || position);
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
END;
/

/*  */

/* Note that the example procedure getLengthLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE getLengthLOB_proc IS
    Lob_loc     NCLOB;
    Length      INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT FLSub INTO Lob_loc FROM Multimedia_tab
        WHERE Clip_ID = 2;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
    /* Get the length of the LOB: */
    length := DBMS_LOB.GETLENGTH(Lob_loc);
    IF length IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('LOB is null.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The length is '
            || length);
    END IF;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
END;
/
/* Procedure created. */


/*  */

/* Note that the example procedure copyLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE copyLOB_proc IS
    Dest_loc     BLOB;
    Src_loc      BLOB;
    Amount       NUMBER;
    Dest_pos     NUMBER;
    Src_pos      NUMBER;
BEGIN
    SELECT Sound INTO Dest_loc FROM Multimedia_tab
        WHERE Clip_ID = 2 FOR UPDATE;
    /* Select the LOB: */
    SELECT Sound INTO Src_loc FROM Multimedia_tab
        WHERE Clip_ID = 1;
    /* Opening the LOBs is optional: */
    DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Copies the LOB from the source position to the destination position: */

    DBMS_LOB.COPY(Dest_loc, Src_loc, Amount, Dest_pos, Src_pos);
    /* Closing LOBs is mandatory if you have opened them: */
    DBMS_LOB.CLOSE(Dest_loc);
    DBMS_LOB.CLOSE(Src_loc);
    COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
 /*  Procedure created. */


/*  */

/* Note that the example procedure lobAssign_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE lobAssign_proc IS
  Lob_loc1    blob;
  Lob_loc2    blob;
BEGIN
  SELECT Frame INTO Lob_loc1 FROM Multimedia_tab where Clip_ID = 1 FOR UPDATE;

   /* Assign Lob_loc1 to Lob_loc2 thereby saving a copy of the value of the
lob
     at this point in time. */
  Lob_loc2 := Lob_loc1;
  /* When you write some data to the lob through Lob_loc1, Lob_loc2 will not
see
     the newly written data whereas Lob_loc1 will see the new data. */
END;
/
/* Procedure created. */


/*  */

/* Note that the example procedure appendLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE appendLOB_proc IS
    Dest_loc        BLOB;
    Src_loc         BLOB;
BEGIN
    /* Select the LOB, get the destination LOB locator: */
    SELECT Sound INTO Dest_loc FROM Multimedia_tab
        WHERE Clip_ID = 2
        FOR UPDATE;
    /* Select the LOB, get the destination LOB locator: */
    SELECT Sound INTO Src_loc FROM Multimedia_tab
        WHERE Clip_ID = 1;
     /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Dest_loc, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN (Src_loc, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.APPEND(Dest_loc, Src_loc);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Dest_loc);
    DBMS_LOB.CLOSE (Src_loc);
COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure lobWriteAppend_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE lobWriteAppend_proc IS
   Lob_loc    BLOB;
   Buffer     RAW(32767);
   Amount     Binary_integer := 32767;
BEGIN
   SELECT Frame INTO Lob_loc FROM Multimedia_tab where Clip_ID = 1 FOR UPDATE;

   /* Fill the buffer with data... */
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Append the data from the buffer to the end of the LOB: */
   DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */


/* Note that the example procedure writeDataToLOB_proc is not part of the
   DBMS_LOB package. */
CREATE or REPLACE PROCEDURE writeDataToLOB_proc IS
   Lob_loc         CLOB;
   Buffer          VARCHAR2(32767);
   Amount          BINARY_INTEGER := 32767;
   Position        INTEGER := 1;
   i               INTEGER;
BEGIN
   /* Select a LOB: */
   SELECT Story INTO Lob_loc
        FROM Multimedia_tab
        WHERE Clip_ID = 1
        FOR UPDATE;
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Fill the buffer with data to write to the LOB: */
   FOR i IN 1..3 LOOP
      DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
      /* Fill the buffer with more data to write to the LOB: */
      Position := Position + Amount;
   END LOOP;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
END;
/
/* Procedure created. */

/*  */
/* We add a second example to show a case in which the buffer size and amount
   differs from the first example. */
CREATE or REPLACE PROCEDURE writeDataToLOB_proc IS
   Lob_loc         CLOB;
   Buffer          VARCHAR2(32767);
   Amount          BINARY_INTEGER := 32767;
   Position        INTEGER;
   i               INTEGER;
   Chunk_size      INTEGER;
BEGIN
    SELECT Story INTO Lob_loc
        FROM Multimedia_tab
        WHERE Clip_ID = 1
        FOR UPDATE;
     /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);

    Chunk_size := DBMS_LOB.GETCHUNKSIZE(Lob_loc);

    /* Fill the buffer with 'Chunk_size' worth of data to write to
       the LOB. Use the chunk size (or a multiple of chunk size) when writing
       data to the LOB.  Make sure that you write within a chunk boundary and
       don't overlap different chunks within a single call to DBMS_LOB.WRITE.
*/
    Amount := Chunk_size;

    /* Write data starting at the beginning of the second chunk: */
    Position := Chunk_size + 1;

    FOR i IN 1..3 LOOP
        DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
        /* Fill the buffer with more data (of size Chunk_size) to  write to
the LOB: */
        Position := Position + Amount;
    END LOOP;
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
END;
/
/* Procedure created. */


/*  */

/* Note that the example procedure trimLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE trimLOB_proc IS
    Lob_loc        CLOB;
BEGIN
    /* Select the LOB, get the LOB locator: */
    SELECT Mtab.Voiced_ref.Script INTO Lob_loc FROM Multimedia_tab Mtab
        WHERE Mtab.Clip_ID = 2
        FOR UPDATE;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
    /* Trim the LOB data: */
    DBMS_LOB.TRIM(Lob_loc,100);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
COMMIT;
/* Exception handling */
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */


/*  */

/* Note that the example procedure eraseLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE eraseLOB_proc IS
    Lob_loc        BLOB;
    Amount         INTEGER := 3000;
BEGIN
    /* Select the LOB, get the LOB locator: */
    SELECT Sound INTO lob_loc FROM Multimedia_tab
        WHERE Clip_ID = 1
        FOR UPDATE;
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Erase the data: */
    DBMS_LOB.ERASE(Lob_loc, Amount, 2000);
    /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
COMMIT;
/* Exception handling */
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */


/*  */
/* Note that the example procedure updateUseBindVariable_proc
   is not part of the DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (Lob_loc BLOB) IS
BEGIN
     UPDATE Multimedia_tab SET Sound = lob_loc WHERE Clip_ID = 2;
END;
/
/* Procedure created. */


/*  */

DECLARE
  Lob_loc     BLOB;
BEGIN
   /* Select the LOB: */
   SELECT Sound INTO Lob_loc
      FROM Multimedia_tab
      WHERE Clip_ID = 1001;
  updateUseBindVariable_proc (Lob_loc);
  COMMIT;
END;
/
/* PL/SQL procedure successfully completed. */

/* Example: Delete a LOB Using SQL DML */
DELETE FROM Multimedia_tab
     WHERE Clip_ID = 10;
/* 0 rows deleted; need actual data. */


/*  */


/*Chapter 3 examples follow */
/* This example reads in a single video Frame from the Multimedia_tab table.
   Then it creates a temporary lob so that we can use the temporary lob to
   convert the video image from MPEG to JPEG format. The Temporary LOB which is
   created will be read through the CACHE, and it will be automatically cleaned
   up at the end of the user\'s session, if it is not explicitly freed sooner.
   */
DECLARE
  Dest_loc       BLOB;
  Src_loc        BLOB;
  Amount         INTEGER := 4000;
BEGIN
    SELECT Frame INTO Src_loc FROM Multimedia_tab WHERE Clip_ID = 1001;
     /* Create a temporary LOB */
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Copy the entire frame from the Src_loc to the Temporary Lob */
    DBMS_LOB.COPY(Dest_loc,Src_loc,DBMS_LOB.GETLENGTH(Src_loc),1,1);
    DBMS_LOB.FREETEMPORARY(Dest_loc);
END;
/
/* PL/SQL procedure successfully completed. */

/*  */

/* This is also an example of freeing a temporary LOB. First we test to make
   sure that the LOB locator points to a temporary LOB, then we free it.
   Otherwise, we issue an error. */
CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS
BEGIN
  /* Free the temporary lob locator passed in */
  /* First check to make sure that the locator is pointing to a temporary lob.
  */
  IF DBMS_LOB.ISTEMPORARY(Lob_loc) = 1 THEN
    /* Free the temporary lob locator */
    DBMS_LOB.FREETEMPORARY(Lob_loc);
    DBMS_OUTPUT.PUT_LINE(' temporary lob was freed');
  ELSE
    /* Print an error */
    DBMS_OUTPUT.PUT_LINE('Locator passed in was not a temporary lob locator');
  END IF;
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure freeTempLob_proc is not part of the
   DBMS_LOB package. */
CREATE or REPLACE PROCEDURE freeTempLob_proc(Lob_loc IN OUT BLOB) IS

BEGIN
   /* Free the temporary lob locator passed in */
   /* First  make sure that the locator is pointing to a temporary LOB */
     DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
     /* use the temporary lob locator here..... then free it....*/
       /* Free the temporary LOB locator */
        DBMS_LOB.FREETEMPORARY(Lob_loc);
        DBMS_OUTPUT.PUT_LINE(' temporary lob was freed');
END;
/
/* Procedure created. */

/*  */

/* Example: Load a Temporary LOB with Data from a BFILE Using PL/SQL (DBMS_LOB
 Package) */
DECLARE
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
  Amount         INTEGER := 36;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Opening the FILE is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE(Src_loc);
    DBMS_LOB.CLOSE(Dest_loc);
    /* free the temporary lob*/
    DBMS_LOB.FREETEMPORARY(Dest_loc);
END;
/

/* PL/SQL procedure successfully completed. */

/*  */

/* Note that the example procedure seeTempLOBIsOpen_proc is not part of the
   DBMS_LOB package.This procedure takes a locator as input and
   returns 0 or 1 depending on whether or not the LOB is closed or open.It
   returns 1 if it is open */
CREATE OR REPLACE PROCEDURE seeTempLOBIsOpen_proc(Lob_loc IN OUT BLOB, Retval
OUT INTEGER) IS
BEGIN
   /* Create the temporary lob */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);

   /* See If the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1  if  the LOB is open. */
   /* free the temporary lob */
   DBMS_LOB.FREETEMPORARY(Lob_loc);

END;
/
/* Procedure created. */

/*  */

/* Example : Display the Temporary LOB Data Using PL/SQL (DBMS_LOB Package) */
DECLARE
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
  Amount         INTEGER := 36;
  Bbuf           RAW(36);
  Position       INTEGER :=1;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Opening the FILE  is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Opening the LOB is optional: */
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE(Dest_loc,Src_loc,Amount);

   LOOP
        DBMS_LOB.READ (Dest_loc, Amount, Position, Bbuf);
        /* Display the buffer contents: */

       DBMS_OUTPUT.PUT_LINE('Result :'|| utl_raw.cast_to_varchar2(Bbuf));
        Position := Position + Amount;
    END LOOP;
       EXCEPTION
      WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('End of data loaded into temp LOB');

    DBMS_LOB.CLOSE(Dest_loc);
    DBMS_LOB.FREETEMPORARY(Dest_loc);
    /* Closing the file is mandatory unless you close the files later */
    DBMS_LOB.CLOSE(Src_loc);
END;
/
/* PL/SQL procedure successfully completed. */

/*  */

/* Note that PL/SQL does not support streaming reads. The OCI example will
   illustrate streaming reads. */
 DECLARE
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
  Amount         INTEGER := 36;
  Bbuf           RAW(32767);
  Position       INTEGER :=1;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Opening the FILE is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Opening the LOB is optional: */
    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
    DBMS_LOB.READ (Dest_loc, Amount, Position, Bbuf);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE(Src_loc);
END;
/
/* PL/SQL procedure successfully completed. */

/*  */

/* Note that the example procedure substringTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE or REPLACE PROCEDURE substringLOB_proc IS
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
  Amount         INTEGER := 32767;
  Bbuf           RAW(32767);
  Position       INTEGER :=128;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Opening the FILE  is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* opening the LOB is optional */
    DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
    Bbuf := DBMS_LOB.SUBSTR(Dest_loc, Amount, Position);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE(Src_loc);
    DBMS_LOB.CLOSE(Dest_loc);
END;
/
/* Procedure created. */

/*  */

/* Example: Compare All or Part of Two (Temporary) LOBs Using PL/SQL (DBMS_LOB
Package) */
/* Note that the example procedure compTwoTempOrPersistLOBs_proc is not
 part of the DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE compTwoTempOrPersistLOBs_proc IS
    Lob_loc1 BLOB;
    Lob_loc2 BLOB;
    Temp_loc BLOB;
    Amount   INTEGER := 32767;
    Retval   INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Frame INTO Lob_loc1 FROM Multimedia_tab
        WHERE Clip_ID = 1;
    SELECT Frame INTO Lob_loc2 FROM Multimedia_tab
        WHERE Clip_ID = 2;
    /* Copy a frame into a temp LOB and convert it to a different format */
    /* before comparing the frames : */
    DBMS_LOB.CREATETEMPORARY(Temp_loc,TRUE,DBMS_LOB.SESSION);
    DBMS_LOB.OPEN(Temp_loc,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN(Lob_loc2,DBMS_LOB.LOB_READONLY);
    /* Copy the persistent LOB into the temp LOB */
    DBMS_LOB.COPY(Temp_loc,Lob_loc2,DBMS_LOB.GETLENGTH(Lob_loc2),1,1);
    /* Perform some conversion function on the temp LOB before comparing it*/
    /* ...some_conversion_format_function(Temp_loc); */
    retval := DBMS_LOB.COMPARE(Lob_loc1, Temp_loc, Amount, 1, 1);
    IF retval = 0 THEN
       DBMS_OUTPUT.PUT_LINE('Processing for equal frames');
    ELSE
       DBMS_OUTPUT.PUT_LINE('Processing for non-equal frames');
    END IF;
    DBMS_LOB.CLOSE(Temp_loc);
    DBMS_LOB.CLOSE(Lob_loc1);
    DBMS_LOB.CLOSE(Lob_loc2);
    /* Free the temporary LOB now that we are done using it */
    DBMS_LOB.FREETEMPORARY(Temp_loc);
    END;
/
/* Procedure created. */

/*  */
/* Note that the example procedure instringTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE instringTempLOB_proc IS
    Lob_loc        CLOB;
    Temp_clob      CLOB;
    Pattern        VARCHAR2(30) := 'children';
    Position       INTEGER := 0;
    Offset         INTEGER := 1;
    Occurrence     INTEGER := 1;
BEGIN
    /* Create the temp LOB and copy a clob into it */
    DBMS_LOB.CREATETEMPORARY(Temp_clob,TRUE, DBMS_LOB.SESSION);
    SELECT Story INTO Lob_loc
        FROM Multimedia_tab
        WHERE Clip_ID = 1;

    DBMS_LOB.OPEN(Temp_clob,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Lob_loc,DBMS_LOB.LOB_READONLY);
    /* Copy the clob into the temp CLOB */
    DBMS_LOB.COPY(Temp_clob,Lob_loc,DBMS_LOB.GETLENGTH(Lob_loc),1,1);
    /* Seek the pattern in the temp CLOB: */
    Position := DBMS_LOB.INSTR(Temp_clob, Pattern, Offset, Occurrence);
    IF Position = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Pattern not found');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The pattern occurs at '
                || position);
    END IF;
    DBMS_LOB.CLOSE(Lob_loc);
    DBMS_LOB.CLOSE(Temp_clob);
    /* free the temporary lob */
    DBMS_LOB.FREETEMPORARY(Temp_clob);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure getLengthLOB_proc is not part of the
   DBMS_LOB package. */
/* Note that the example procedure getLengthTempCLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE getLengthTempCLOB_proc IS
       Length      INTEGER;
       tlob        CLOB;
       bufc        varchar2(8);
       Amount         number;
       pos         number;
       Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
BEGIN
    DBMS_LOB.CREATETEMPORARY(tlob,TRUE,DBMS_LOB.SESSION);
    /* opening the lob is optional */
    DBMS_LOB.OPEN(tlob,DBMS_LOB.LOB_READWRITE);
    /* opening the file is mandatory */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    Amount := 32767;
    DBMS_LOB.LOADFROMFILE(tlob, Src_loc, Amount);
        /* Get the length of the LOB: */
    length := DBMS_LOB.GETLENGTH(tlob);
    IF length = 0 THEN
        DBMS_OUTPUT.PUT_LINE('LOB is empty.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The length is ' || length);
    END IF;
     /* must close any lobs that were opened */
    DBMS_LOB.CLOSE(tlob);
    DBMS_LOB.CLOSE(Src_loc);
    /* free the temporary lob now that we are done with it */
    DBMS_LOB.FREETEMPORARY(tlob);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure copyTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE copyTempLOB_proc IS
    Dest_pos     NUMBER;
    Src_pos      NUMBER;
    Dest_loc       BLOB;
    Dest_loc2      BLOB;
    Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
    Amount      INTEGER := 32767;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE,DBMS_LOB.SESSION);
    DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE, DBMS_LOB.SESSION);
    /* Opening the FILE  is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* opening the temporary  LOBs is optional */
     DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
     DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE);

     DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
       /* Set Dest_pos to the position at which we should start writing in the
          target temp LOB */
       /* Copies the LOB from the source position to the destination
          position:*/
       /* Set amount to the amount you want copied */
          Amount := 328;
          Dest_pos := 1000;
          Src_pos := 1000;
       /* Set Src_pos to the position from which we should start copying data
          from  tclob_src */
          DBMS_LOB.COPY(Dest_loc2,Dest_loc, Amount, Dest_pos, Src_pos);
     COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
     DBMS_LOB.CLOSE(Dest_loc);
     DBMS_LOB.CLOSE(Dest_loc2);
     DBMS_LOB.CLOSE(Src_loc);
     DBMS_LOB.FREETEMPORARY(Dest_loc);
     DBMS_LOB.FREETEMPORARY(Dest_loc2);
END;
/
/*   Procedure created. */

/*  */

/* Note that the example procedure copyTempLOBLocator_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE copyTempLOBLocator_proc(Lob_loc1 IN OUT CLOB,
Lob_loc2 IN OUT CLOB) IS
bufp  varchar2(4);
Amount   number  := 32767;
Src_loc  BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
BEGIN
    DBMS_LOB.CREATETEMPORARY(Lob_loc1,TRUE,DBMS_LOB.SESSION);
    DBMS_LOB.CREATETEMPORARY(Lob_loc2,TRUE,DBMS_LOB.SESSION);
    /* populate the first temporary lob with some data */
    /* Opening file is mandatory */
    DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READONLY);
    /* Opening LOB is optional */
    DBMS_LOB.OPEN(Lob_loc1,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Lob_loc2,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(Lob_loc1,Src_loc,Amount);

   /* Assign Lob_loc1 to Lob_loc2 thereby creating  a copy of the value of
      the temporary LOB referenced by Lob_loc1 at this point in time. */
   Lob_loc2 := Lob_loc1;

   /* When you write some data to the LOB through Lob_loc1, Lob_loc2
      will not see the newly written data whereas Lob_loc1 will see
      the new data. */
   /*Closing LOBs is mandatory if they were opened */
    DBMS_LOB.CLOSE (Src_loc);
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
    DBMS_LOB.FREETEMPORARY(Lob_loc1);
    DBMS_LOB.FREETEMPORARY(Lob_loc2);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure appendTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE appendTempLOB_proc IS
Dest_loc2 CLOB;
Dest_loc  CLOB;
Amount    NUMBER;
Src_loc   BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
BEGIN
        DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE,DBMS_LOB.SESSION);
        DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE,DBMS_LOB.SESSION);
        DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
        DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE);
        DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READWRITE);
        Amount := 32767;
        DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
        DBMS_LOB.LOADFROMFILE(Dest_loc2, Src_loc, Amount);

        DBMS_LOB.APPEND(Dest_loc, Dest_loc2);
        /* CLOSE the temporary lobs and then free them */
        DBMS_LOB.CLOSE(Dest_loc);
        DBMS_LOB.CLOSE(Dest_loc2);
        DBMS_LOB.CLOSE(Src_loc);
        DBMS_LOB.FREETEMPORARY(Dest_loc);
        DBMS_LOB.FREETEMPORARY(Dest_loc2);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure writeAppendTempLOB_proc is not part of the
   DBMS_LOB package. This example  procedure will read in 32767 bytes of  data
   from the adldemo.dat file starting at offset 128 and append it to a
   temporary LOB. The procedure takes a locator passed in and creates a
   temporary LOB and points that locator to it. */
CREATE OR REPLACE PROCEDURE writeAppendTempLOB_proc IS
   Lob_loc    BLOB;
   Buffer     RAW(32767);
   Src_loc    BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
   Amount     Binary_integer := 32767;
   Position   Binary_integer := 128;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* opening the temporary lob is optional */
   DBMS_LOB.OPEN(Lob_loc,DBMS_LOB.LOB_READWRITE);
   /* Opening the FILE is mandatory */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Fill the buffer with data... */
   DBMS_LOB.LOADFROMFILE (Lob_loc,Src_loc, Amount);

   /* Append the data from the buffer to the end of the LOB: */
   DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.CLOSE(Lob_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
  END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure writeDataToTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE or REPLACE PROCEDURE writeDataToTempLOB_proc IS
   Lob_loc         CLOB;
   Buffer          VARCHAR2(26);
   Amount          BINARY_INTEGER := 26;
   Position        INTEGER := 1;
   i               INTEGER;
BEGIN
    DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Fill the buffer with data to write to the LOB: */
   Buffer := 'abcdefghijklmnopqrstuvwxyz';

   FOR i IN 1..3 LOOP
      DBMS_LOB.WRITE (Lob_loc, Amount, Position, Buffer);
      /* Fill the buffer with more data to write to the LOB: */
      Position := Position + Amount;
   END LOOP;
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure trimTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
    Lob_loc        CLOB;
    Amount         number;
    Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
    TrimAmount     number := 100;
BEGIN
    /* Create a temporary lob */
    DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
     /* opening the file is mandatory */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* populate the temporary lob with some data */
    Amount := 32767;
    DBMS_LOB.LOADFROMFILE(Lob_loc, Src_loc, Amount);
    DBMS_LOB.TRIM(Lob_loc,TrimAmount);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
    DBMS_LOB.CLOSE(Src_loc);
    DBMS_LOB.FREETEMPORARY(Lob_loc);
COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure eraseTempLOB_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
    Lob_loc        CLOB;
    amt            number;
    Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'adldemo.dat');
    Amount         INTEGER := 32767;

BEGIN
    /* Create a temporary lob */
    DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE,DBMS_LOB.SESSION);
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
    /* populate the temporary lob with some data */
    Amount := 32767;
    DBMS_LOB.LOADFROMFILE(Lob_loc, Src_loc, Amount);
    /* Erase  the LOB data: */
    amt := 1000;
    DBMS_LOB.ERASE(Lob_loc, amt, 2);
     /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE (Lob_loc);
    DBMS_LOB.CLOSE(Src_loc);
    DBMS_LOB.FREETEMPORARY(Lob_loc);
COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */

/*  */
/*  chapter 4 examples follow */


/* Note that the example procedure loadLOBFromBFILE_proc is not part of the
 DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc IS
  Dest_loc       BLOB;
  Src_loc        BFILE := BFILENAME('FRAME_DIR', 'adldemo.dat');
  Amount         INTEGER := 4000;
BEGIN
SELECT Frame INTO Dest_loc FROM Multimedia_tab
WHERE Clip_ID = 3
        FOR UPDATE;
    /* Opening the LOB is mandatory: */
    DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
    /* Opening the LOB is optional: */
    DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
    /* Closing the LOB is mandatory if you have opened it: */
    DBMS_LOB.CLOSE(Dest_loc);
    DBMS_LOB.CLOSE(Src_loc);
    COMMIT;
END;
/
/* Procedure created. */

/*  */

/*  Note that the example procedure openBFILE_procOne is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE openBFILE_procOne IS
  Lob_loc    BFILE := BFILENAME('PHOTO_DIR', 'adldemo.dat');
BEGIN
   /* Open the BFILE: */
DBMS_LOB.FILEOPEN (Lob_loc, DBMS_LOB.FILE_READONLY);
/* ... Do some processing. */
  DBMS_LOB.FILECLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure openBFILE_procTwo is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE openBFILE_procTwo IS
  Lob_loc    BFILE := BFILENAME('PHOTO_DIR', 'adldemo.dat');
BEGIN
   /* Open the BFILE: */
DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
/* ... Do some processing. */
  DBMS_LOB.CLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure seeIfOpenBFILE_procOne is not part of the
   DBMS_LOB package. */
/* Note that the example procedure seeIfOpenBFILE_procOne is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procOne IS
   Lob_loc      BFILE;
   RetVal       INTEGER;
BEGIN
    /* Select the LOB, initializing the BFILE locator: */
    SELECT Music INTO Lob_loc FROM Multimedia_tab
        WHERE Clip_ID = 3;
    RetVal := DBMS_LOB.FILEISOPEN(Lob_loc);
    IF (RetVal = 1)
    THEN
       DBMS_OUTPUT.PUT_LINE('File is open');
    ELSE
       DBMS_OUTPUT.PUT_LINE('File is not open');
    END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure seeIfOpenBFILE_procTwo is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procTwo IS
  Lob_loc      BFILE;
  RetVal       INTEGER;
BEGIN
    /* Select the LOB, initializing the BFILE locator: */
    SELECT Music INTO Lob_loc FROM Multimedia_tab
        WHERE Clip_ID = 3;
    RetVal := DBMS_LOB.ISOPEN(Lob_loc);
    IF (RetVal = 1)
    THEN
        DBMS_OUTPUT.PUT_LINE('File is open');
    ELSE
        DBMS_OUTPUT.PUT_LINE('File is not open');
    END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure readBFILE_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE readBFILE_proc IS
  Lob_loc       BFILE := BFILENAME('PHOTO_DIR', 'adldemo.dat');
  Amount        INTEGER := 32767;
  Position      INTEGER := 1;
  Buffer        RAW(32767);
BEGIN
    /* Select the LOB: */
    SELECT Photo INTO Lob_loc FROM Multimedia_tab
        WHERE Clip_ID = 3;
    /* Open the BFILE: */
    DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
    /* Read data: */
    DBMS_LOB.READ(Lob_loc, Amount, Position, Buffer);
    /* Close the BFILE: */
    DBMS_LOB.CLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure substringBFILE_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE substringBFILE_proc IS
  Lob_loc         BFILE;
  Position        INTEGER := 1;
  Buffer          RAW(32767);
BEGIN
    /* Select the LOB: */
    SELECT Mtab.Voiced_ref.Recording INTO Lob_loc FROM Multimedia_tab Mtab
        WHERE Mtab.Clip_ID = 3;
    /* Open the BFILE: */
    DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
    Buffer := DBMS_LOB.SUBSTR(Lob_loc, 255, Position);
    /* Close the BFILE: */
    DBMS_LOB.CLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure compareBFILEs_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE compareBFILEs_proc IS
  /* Initialize the BFILE locator: */
  Lob_loc1       BFILE := BFILENAME('PHOTO_DIR', 'adldemo.dat');
  Lob_loc2       BFILE;
  Retval         INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Photo INTO Lob_loc2 FROM Multimedia_tab
        WHERE Clip_ID = 3;
    /* Open the BFILEs: */
    DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READONLY);
    Retval := DBMS_LOB.COMPARE(Lob_loc2, Lob_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1);
    /* Close the BFILEs: */
    DBMS_LOB.CLOSE(Lob_loc1);
    DBMS_LOB.CLOSE(Lob_loc2);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure instringBFILE_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE instringBFILE_proc IS
  Lob_loc        BFILE;
  Pattern        RAW(32767);
  Position       INTEGER;
BEGIN
    /* Select the LOB: */
    SELECT Intab.Recording INTO Lob_loc
        FROM THE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
                   WHERE Clip_ID = 3) Intab
        WHERE Segment = 1;
  /* Open the BFILE: */
    DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
    /*  Initialize the pattern for which to search, find the 2nd occurrence of
        the pattern starting from the beginning of the BFILE: */
    Position := DBMS_LOB.INSTR(Lob_loc, Pattern, 1, 2);
    /* Close the BFILE: */
    DBMS_LOB.CLOSE(Lob_loc);
END;
/

/*  */

/* Note that the example procedure seeIfExistsBFILE_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE seeIfExistsBFILE_proc IS
  Lob_loc      BFILE;
BEGIN
    /* Select the LOB: */
    SELECT Intab.Recording INTO Lob_loc
        FROM THE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
        WHERE Mtab.Clip_ID = 3) Intab
    WHERE Intab.Segment = 1;
    /* See If the BFILE exists: */
 IF (DBMS_LOB.FILEEXISTS(Lob_loc) != 0)
    THEN
       DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE exists');
    ELSE
       DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE does not exist');
    END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;
/

/*  */


/* Note that the example procedure getLengthBFILE_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE getLengthBFILE_proc IS
  Lob_loc      BFILE;
  Length       INTEGER;
BEGIN
    /* Initialize the BFILE locator by selecting the LOB: */
    SELECT Mtab.Voiced_ref.Recording INTO Lob_loc FROM Multimedia_tab Mtab
        WHERE Mtab.Clip_ID = 3;
    /* Open the BFILE: */
    DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
    /* Get the length of the LOB: */
    Length := DBMS_LOB.GETLENGTH(Lob_loc);
    IF Length IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('BFILE is null.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The length is ' || length);
    END IF;
    /* Close the BFILE: */
    DBMS_LOB.CLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure BFILEAssign_proc is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE BFILEAssign_proc IS
  Lob_loc1    BFILE;
  Lob_loc2    BFILE;
BEGIN
  SELECT Photo INTO Lob_loc1 FROM Multimedia_tab where Clip_ID = 3
    for update;
   /* Assign Lob_loc1 to Lob_loc2 so that they both refer to the same operating
      system file. */
  Lob_loc2 := Lob_loc1;
  /* Now you can read the bfile from either Lob_loc1 or Lob_loc2. */
END;
/
/* Procedure created. */

/*  */

/* Example: Get Directory Alias and Filename Using PL/SQL */
CREATE OR REPLACE PROCEDURE getNameBFILE_proc IS
  Lob_loc         BFILE;
  DirAlias_name   varchar2(30);
  File_name       varchar2(40);
BEGIN
  SELECT Music INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3;
  DBMS_LOB.FILEGETNAME(Lob_loc, DirAlias_name, File_name);
  /* do some processing based on the directory alias and file names */
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure updateUseBindVariable_proc
   is not part of the DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (Lob_loc BFILE) IS
BEGIN
     UPDATE Multimedia_tab SET Photo = Lob_loc WHERE Clip_ID = 3;
END;
/
/* Procedure created. */

DECLARE
  Lob_loc  BFILE;
BEGIN
  SELECT Photo INTO Lob_loc
      FROM Multimedia_tab
      WHERE Clip_ID = 1001;
  updateUseBindVariable_proc (Lob_loc);
  COMMIT;
END;
/

/*  */

/* Note that the example procedure closeBFILE_procOne is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE closeBFILE_procOne IS
   Lob_loc    BFILE := BFILENAME('PHOTO_DIR', 'adldemo.dat');
BEGIN
   DBMS_LOB.FILEOPEN(Lob_loc, DBMS_LOB.FILE_READONLY);
   /* ...Do some processing. */
   DBMS_LOB.FILECLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure closeBFILE_procTwo is not part of the
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE closeBFILE_procTwo IS
   Lob_loc    BFILE := BFILENAME('PHOTO_DIR', 'adldemo.dat');
BEGIN
   DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
   /* ...Do some processing. */
   DBMS_LOB.CLOSE(Lob_loc);
END;
/
/* Procedure created. */

/*  */

/* Note that the example procedure closeAllOpenFilesBFILE_proc is not part of
   the DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE closeAllOpenFilesBFILE_proc IS
BEGIN
    /* Close all open BFILEs: */
    DBMS_LOB.FILECLOSEALL;
END;
/
/* Procedure created. */

/*  */


