Storing pictures and photos in Oracle

Problem:

You would like to be able to store and extract images and other binary large objects (BLOBs) inside your Oracle Database.

Requirements:

Oracle database version 10g or higher

Execute access on DBMS_LOB

Execute access on UTL_FILE

Create directory privilege

1. Loading a File as a BLOB into your Database:

Step 1:

-- Create a table to store your image data CREATE TABLE lob_table (  filename VARCHAR2(50),  blobdata BLOB);

Step 2:

-- Grant appropriate privileges to the user GRANT CREATE ANY DIRECTORY TO boris; -- Specify from where the FILE will be read from CREATE OR REPLACE DIRECTORY in_file_loc AS '/tmp/test/in';

Step 3::

-- Create the procedure to load a FILE into a BLOB CREATE OR REPLACE PROCEDURE LoadFILEIntoBLOB (myfilename IN VARCHAR2) IS  out_blob     BLOB;  in_file      BFILE     := BFILENAME('IN_FILE_LOC', myfilename); --directory name  blob_length  INTEGER; BEGIN  -- Obtain the size of the blob file  DBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);    blob_length:=DBMS_LOB.GETLENGTH(in_file);  DBMS_LOB.FILECLOSE(in_file);    -- Insert a new record into the table containing the  -- filename you have specified and a LOB LOCATOR.      -- Return the LOB LOCATOR and assign it to out_blob.  INSERT INTO lob_table VALUES (myfilename, EMPTY_BLOB())    RETURNING blobdata INTO out_blob;  -- Load the image into the database as a BLOB  DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);  DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);  DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);  -- Close handles to blob and file  DBMS_LOB.CLOSE(out_blob);  DBMS_LOB.CLOSE(in_file);  COMMIT;  -- Confirm insert by querying the database  -- for LOB length information and output results  blob_length := 0;  SELECT DBMS_LOB.GETLENGTH(blobdata) INTO blob_length    FROM lob_table WHERE filename = myfilename;  DBMS_OUTPUT.PUT_LINE('Successfully inserted BLOB ''' ||    myfilename || ''' of size ' || blob_length || ' bytes.'); END; /

Step 4:

-- Turn on serveroutput to view confirmation details SET serveroutput ON -- Load FILE into BLOB EXEC LoadFILEIntoBLOB('my_Image.jpg'); --Output Successfully inserted BLOB 'my_Image.jpg' of size 1441 bytes.

2. Writing a BLOB from your Database to a File

Step 1:

-- Specify to where the FILE will be written CREATE OR REPLACE DIRECTORY out_file_loc AS '/tmp/test/out';

Step 2:

-- Create a procedure to write a BLOB to a FILE CREATE OR REPLACE PROCEDURE WriteBLOBToFILE (myfilename IN VARCHAR2) IS  v_blob         BLOB;  blob_length    INTEGER;  out_file       UTL_FILE.FILE_TYPE;  v_buffer       RAW(32767);  chunk_size     BINARY_INTEGER := 32767;  blob_position  INTEGER := 1; BEGIN  -- Retrieve the BLOB for reading  SELECT blobdata INTO v_blob FROM lob_table WHERE filename = myfilename;  -- Retrieve the SIZE of the BLOB  blob_length:=DBMS_LOB.GETLENGTH(v_blob);  -- Open a handle to the location where you are going to write the BLOB to file  -- NOTE: The 'wb' parameter means "write in byte mode" and is only available  --  in the UTL_FILE package with Oracle 10g or later  out_file := UTL_FILE.FOPEN ('OUT_FILE_LOC', myfilename, 'wb', chunk_size);  -- Write the BLOB to file in chunks  WHILE blob_position <= blob_length LOOP    IF blob_position + chunk_size - 1 > blob_length THEN      chunk_size := blob_length - blob_position + 1;    END IF;    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);    blob_position := blob_position + chunk_size;  END LOOP;  -- Close the file handle  UTL_FILE.FCLOSE (out_file); END; /

Step 4:

-- Turn on serveroutput to view confirmation details SET serveroutput ON -- Write BLOB to FILE EXEC WriteBLOBToFILE('my_Image.jpg');

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.