DBA Tips Archive for Oracle

  


Load Binary File to BFILE then Write Back Out to Disk - (PL/SQL)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. Example
  3. Test



Overview

The following example is part of the Oracle LOB Examples Collection.

This example provides two PL/SQL procedures that demonstrate how to associate a source O/S binary file (a graphics file) with a BFILE column in the database.

Load_BFILE_From_Image

This PL/SQL procedure, uses the BFILENAME function within a SQL statement to associate the BFILE column with an image file on disk. Keep in mind that the image itself is NOT stored within the database and only exists on disk. The BFILE column only stores a pointer to the O/S file.
Write_BFILE_To_File
This PL/SQL procedure demonstrates how to write the contents of the graphics file (using the BFILE reference) back to disk. This is done using the BFILE column to read from the graphics file and then write its contents out to disk using the UTL_FILE.PUT_RAW procedure. Here, it uses DBMS_LOB.READ to read it peicewise from the BFILE reference into a local PL/SQL RAW variable. Each piece is written to a new dstination file with UTL_FILE.PUT_RAW procedure. After running this procedure, there will be two copies of the graphics file on disk - the orignal one that is referenced in the BFILE column and a second file that was created using UTL_FILE.PUT_RAW.

In the last phase of this PL/SQL procedure, the newly created graphics file on disk is associated with a second record in our test_bfile table. The table at this point has two records with two distinct BFILE references; one for the original graphics file and a second which is the one written out to disk using UTL_FILE.PUT_RAW.



Example

Let's first create all Oracle database objects:

DROP TABLE test_bfile CASCADE CONSTRAINTS
/

Table dropped.


CREATE TABLE test_bfile (
      id           NUMBER(15)
    , file_name    VARCHAR2(1000)
    , image        BFILE
    , timestamp    DATE
)
/

Table created.


CREATE OR REPLACE DIRECTORY
    EXAMPLE_LOB_DIR
    AS
    '/u01/app/oracle/lobs'
/

Directory created.


Now, let's define our two example procedures:

load_bfile_from_image.sql
CREATE OR REPLACE PROCEDURE Load_BFILE_From_Image
IS
BEGIN

    DBMS_OUTPUT.ENABLE(100000);

    INSERT INTO test_bfile (id, file_name, image, timestamp)
        VALUES (1001, 'oracle9i_logo.gif', null, sysdate);

    UPDATE test_bfile
        SET image = BFILENAME('EXAMPLE_LOB_DIR', 'oracle9i_logo.gif')
        WHERE id = 1001;

END;
/

SQL> @load_bfile_from_image.sql Procedure created.


write_bfile_to_file.sql
CREATE OR REPLACE PROCEDURE Write_BFILE_To_File
IS

  source_pic      BFILE;
  buffer          RAW(32767);
  buffer_size     CONSTANT BINARY_INTEGER := 32767;
  amount          BINARY_INTEGER;
  offset          NUMBER(38);

  file_handle     UTL_FILE.FILE_TYPE;
  directory_name  CONSTANT VARCHAR2(80) := 'EXAMPLE_LOB_DIR';
  image_filename  CONSTANT VARCHAR2(80) := 'oracle9i_logo2.gif';

  dest_pic        BFILE;

BEGIN

    DBMS_OUTPUT.ENABLE(100000);

    -- -----------------
    -- GET BFILE LOCATOR
    -- -----------------
    SELECT image INTO source_pic
        FROM test_bfile
        WHERE id = 1001;

    -- ---------------------------------------
    -- OPEN ORIGINAL BFILE IMAGE USING LOCATOR
    -- ---------------------------------------
    DBMS_LOB.FILEOPEN( 
        file_loc   => source_pic,
        open_mode  => DBMS_LOB.FILE_READONLY);

    -- ---------------------------------
    -- OPEN NEW IMAGE FILE IN WRITE MODE
    -- ---------------------------------
    file_handle := UTL_FILE.FOPEN(
        location     => directory_name,
        filename     => image_filename,
        open_mode    => 'w',
        max_linesize => buffer_size);

    amount := buffer_size;
    offset := 1;

    -- ---------------------------------------------------
    -- READ FROM BFILE IMAGE / WRITE OUT NEW IMAGE TO DISK
    -- ---------------------------------------------------
    WHILE amount >= buffer_size
    LOOP
        DBMS_LOB.READ(
            file_loc   => source_pic,
            amount     => amount,
            offset     => offset,
            buffer     => buffer);

        offset := offset + amount;

        UTL_FILE.PUT_RAW(
            file      => file_handle,
            buffer    => buffer,
            autoflush => false);

    END LOOP;

    UTL_FILE.FCLOSE(file => file_handle);
    DBMS_LOB.FILEOPEN(file_loc => source_pic);


    -- --------------------------------------------
    -- INSERT THE NEW IMAGE FILE AS A SECOND RECORD
    -- --------------------------------------------
    INSERT INTO test_bfile (id, file_name, image, timestamp)
        VALUES (1002, 'oracle9i_logo2.gif', null, sysdate);

    UPDATE test_bfile
        SET image = BFILENAME('EXAMPLE_LOB_DIR', 'oracle9i_logo2.gif' )
        WHERE id = 1002;

    COMMIT;

END;
/

SQL> @write_bfile_to_file.sql Procedure created.



Test

SQL> set serveroutput on
SQL> exec Load_BFILE_From_Image

PL/SQL procedure successfully completed.


SQL> exec Write_BFILE_To_File

PL/SQL procedure successfully completed.


SQL> SELECT id, DBMS_LOB.GETLENGTH(image) Length FROM test_bfile;

        ID     LENGTH
---------- ----------
      1001       1321
      1002       1322


SQL> host ls -l oracle9i_*
-rw-r--r--   1 oracle   dba         1321 Jul 21 21:27 oracle9i_logo.gif
-rw-r--r--   1 oracle   dba         1322 Jul 23 22:48 oracle9i_logo2.gif

NOTE: Notice that the newly created file, oracle9i_logo2.gif is 1 byte larger than the original. Read my article entitled, CR/LF Added to Binary File When Using UTL_FILE.PUT_RAW for an explanation on why this happens.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 23-Jan-2006 08:54:07 EST
Page Count: 36913