JoeGarrepy.com Oracle Store and Display Image on Web Stored as BLOB using Oracle Web Application Server

Programming/
Tools
      Oracle
      JavaScript
      C#

Fun Stuff
      Music
      Martial Arts
      Robotech

Other
      Main

DISCLAIMER

This disclaimer is intended to cover all software/freeware, and other various files which are downloadable through or from this site.
Joe Garrepy, isp, and web host can not be held responsible for any software downloaded from this site. All freeware is downloaded at your own risk. Software which is listed on this site is assumed to work properly, but may or may not have different effects on different computer systems. The same rules apply to these programs. Joe Garrepy assumes no responsibility for those unusual occurrences where software has adverse effects on users computer. Joe Garrepy does not warrant these programs/files, and as such, can offer no guidelines in which to use them. All freeware/software/files, trademarks, service marks, and copyrights are acknowledged as belonging to their owners. Any of these which the owner wishes to be removed will be done so immediately. By downloading any software or viewing pages in which software can be downloaded, you agree to be bound to this agreement.

JoeGarrepy.com Oracle Store and Display Image on Web Stored as BLOB using Oracle Web Application Server

The following code is for importing images from a directory on the host machine Oracle resides on into the database and then displaying them on the web with the Oracle Web Application Server. One of the cool things about Oracle is the built in Web Application Server which uses packages like SYS.HTP to create web pages using PL/SQL. The next logical step after creating a simple web page is using it to display or download content files stored in the database. Though this example is for displaying images it could be used for any type of file that is stored as a BLOB.

SQL Script

/*
  -- ****************************************************************** --
   10/11/2004 Store and Display Image on Web Stored as BLOB using Oracle
   Web Application Server (Joe Garrepy) VERSION 1.0
  -- ****************************************************************** --
   The following script creates objects to insert and view images stored
   in an Oracle Table.  To download the images shown in this example paste
   the following link into your web browser...
   http://www.joegarrepy.com/oracle_webimage_images/oracle_webimage.zip
  -- ****************************************************************** --
*/

--> Step 1, create table to store blobs to
CREATE TABLE entity_image
(uidn     NUMBER(8) NOT NULL,
 e_image  BLOB);

--> Step 2, create directory location on sever for images to be
--> Oracle must have access to the directory that you create
CREATE OR REPLACE directory dir_image AS 'd:\orautl\images\';
CREATE PUBLIC SYNONYM DIR_IMAGE FOR DIR_IMAGE;

--> Step 3
--> Create package, package has code to display images, show a web
--> page with the image from the database and a procedure that will
--> Import blobs from a directory on the server...
CREATE OR REPLACE
PACKAGE pkg_image
IS
 -- ************************************************** --
 -- Display an HTML page with an image retrieved from
 -- the database
 -- ************************************************** --
  PROCEDURE p_page
  ;
 -- ************************************************** --
 -- Procedure displays a page with an image from a     --
 -- blob stored in the table entity_image...
 -- ************************************************** --
  PROCEDURE p_display
  (in_uidn   IN    ENTITY_IMAGE.UIDN%TYPE)
  ;
 --
 -- ************************************************** --
 -- Procedure will insert a row into entity_image
 -- including adding a BLOB to the BLOB column...
 -- ************************************************** --
  PROCEDURE p_ins_entity_image
  (in_uidn   IN   ENTITY_IMAGE.UIDN%TYPE,
   in_dir    IN   VARCHAR2 DEFAULT 'DIR_IMAGE',
   in_file   IN   VARCHAR2 DEFAULT 'bw_op.gif')
  ;
 --
END pkg_image;
/
CREATE OR REPLACE
PACKAGE BODY pkg_image
IS
 -- ************************************************** --
  PROCEDURE p_page
  IS
  BEGIN
   --
    htp.htmlOpen;
    htp.Title('Diplay Image from BLOB in Database');
    htp.bodyOpen;
    htp.p('<TABLE STYLE="border color:orange dotted width:2;"><TR><TH ALIGN="center">Display Image</TH></TR><TR><TD ALIGN="center">');
   --> display first image
    htp.p('<IMG SRC="pkg_image.p_display?in_uidn=1" width="250">');
    htp.br;
    htp.p('<A HREF="pkg_image.p_display?in_uidn=1">Click to Display Full Image</A>');
   --
    htp.p('</TD></TR><TR><TD ALIGN="center">');
   --> display second image
    htp.p('<IMG SRC="pkg_image.p_display?in_uidn=2" width="350">');
    htp.br;
    htp.p('<A HREF="pkg_image.p_display?in_uidn=2">Click to Display Full Image</A>');
   --
    htp.p('</TD></TR></TABLE>');
    htp.bodyClose;
    htp.htmlClose;
   --
  END p_page;
 -- ************************************************** --
  PROCEDURE p_display
  (in_uidn   IN    ENTITY_IMAGE.UIDN%TYPE)
  IS
    intImgSize                  INTEGER;
    blobImage                   BLOB;
    varMimeType                 VARCHAR2(4000);
    CURSOR tblImage IS select entity_image.e_image AS val
                       from entity_image
                       where entity_image.uidn = in_uidn;
    rowImage                    tblImage%ROWTYPE;
  BEGIN
   --> Get Image
    OPEN tblImage;
    FETCH tblImage INTO rowImage;
    CLOSE tblImage;
   --> get image size
    intImgSize := dbms_lob.getlength(rowImage.val);
   --> allocate blob memory/initiliaze
    dbms_lob.createtemporary(blobImage,true);
   --> copy fetched value to newly initiliazed BLOB
    dbms_lob.copy(blobImage,rowImage.val,intImgSize,1,1);
   --> create mime header
    owa_util.mime_header('image/gif', FALSE, NULL);
    htp.p('Content-length: '|| intImgSize);
    htp.p('Pragma: no-cache');
    htp.p('Cache-Control: no-cache');
    htp.p('Expires: Thu, 01 Jan 1970 12:00:00 GMT');
    owa_util.http_header_close;
   --> download BLOB
    wpg_docload.download_file(blobImage);
   --> clean up memory
    dbms_lob.freetemporary(blobImage);
   --
  EXCEPTION
    WHEN OTHERS THEN
      owa_util.mime_header('text/html', false);
      htp.p('Content-length: 0');
      owa_util.http_header_close;
      htp.p('<BR><BR><FONT COLOR=RED>' || sqlerrm || '</FONT>');
  END p_display;
 -- ************************************************** --
  PROCEDURE p_ins_entity_image
  (in_uidn   IN   ENTITY_IMAGE.UIDN%TYPE,
   in_dir    IN   VARCHAR2 DEFAULT 'DIR_IMAGE',
   in_file   IN   VARCHAR2 DEFAULT 'bw_op.gif')
  IS
    bfileImage    BFILE := BFILENAME(in_dir, in_file); --> Pointer to file
    blobImage     BLOB;
    blobE_Image   ENTITY_IMAGE.E_IMAGE%TYPE;
  BEGIN
   --> Insert into entity_image and return BLOB from new record (used as a pointer)
    INSERT INTO entity_image
    (uidn,
     e_image)
    VALUES
    (in_uidn,
     EMPTY_BLOB())
    RETURNING entity_image.e_image INTO blobE_Image;
   --> get image file into BFILE variable
    dbms_lob.fileopen(bfileImage,dbms_lob.file_readonly);
   --> load blob
    dbms_lob.loadfromfile(blobE_Image,bfileImage,dbms_lob.getlength(bfileImage));
    dbms_lob.close(bfileImage);
   --
 END;
 -- ************************************************** --
END pkg_image;
/
CREATE PUBLIC SYNONYM pkg_image FOR pkg_image;

--> Step 4 Insert record into ENTITY_IMAGE including the BLOB column data
BEGIN
  pkg_image.p_ins_entity_image(1,'DIR_IMAGE','bw_op.gif');
  pkg_image.p_ins_entity_image(2,'DIR_IMAGE','myart.gif');
  COMMIT;
END;
/

--> you are now ready to view your page displaying the image...
http:/..../pkg_image.p_page

When you display your page it should look like the following...

Diplay Image from BLOB in Database

Display Image
Click to Display Full Image
Click to Display Full Image

If you find any bugs or want to contact me I can be reached @ doughjoe77@hotmail.com.