Images from Oracle Over HTTP

This articles defines a process for getting images out of the database over HTTP but it could easily be extended to serve a variety of binary documents. The database functionality used is available in later versions of Oracle8i also:
·    Database Access Descriptor Configuration
·    Create Schema
·    Create PL/SQL Code
·    Load Images
·    Test It
Database Access Descriptor Configuration (DAD)
First we must set up a Database Access Descriptor (DAD) to allow the webserver to connect to the database when a specific URL is requested:
·    Access the database HTTP server main page via a browser (http://yourServer:7777/). The correct port number is listed in the $ORACLE_HOME/Apache/Apache/setupinfo.txt file
·    Click on the "Mod_plsql Configuration Menu" link.
·    Click on the "Gateway Database Access Descriptor Settings" link.
·    Click on the "Add Default (blank configuration)" link.
·    Enter SCOTT as the Database Access Descriptor Name. This will be used in the requesting URL.
·    Enter the username (SCOTT), password (TIGER) and connect string (W2K1) for the desired database connection.
·    Select the "Basic" authentication mode.
·    Click the OK button at the top right of the screen.
The list of DADs will now contain the SCOTT entry, which can be edited if necessary by clicking the edit icon next to it.

Create Schema
Next we create a schema to hold our images:

CREATE TABLE images (
  id     NUMBER(10)    NOT NULL,
  name   VARCHAR2(50)  NOT NULL,
  image  BLOB          NOT NULL
)
/

ALTER TABLE images ADD (
  CONSTRAINT images_pk PRIMARY KEY (id)
)
/

ALTER TABLE images ADD (
  CONSTRAINT images_uk UNIQUE (name)
)
/

CREATE SEQUENCE images_seq
/

CREATE OR REPLACE DIRECTORY image_dir AS 'C:\'
/
Create PL/SQL Code
Next we create the code to load and retrieve the images:

CREATE OR REPLACE PACKAGE Images_API AS

PROCEDURE Load (p_name  IN  images.name%TYPE);
PROCEDURE Get  (p_name  IN  images.name%TYPE,
                p_type  IN  VARCHAR2 DEFAULT 'gif');
                
PROCEDURE Get_Gif  (p_name  IN  images.name%TYPE);
PROCEDURE Get_Jpeg (p_name  IN  images.name%TYPE);

END;
/

CREATE OR REPLACE PACKAGE BODY Images_API AS

PROCEDURE Load (p_name  IN  images.name%TYPE) IS
  v_bfile  BFILE;
  v_blob   BLOB;
BEGIN
  INSERT INTO images (id, name, image)
  VALUES (images_seq.NEXTVAL, p_name, empty_blob())
  RETURN image INTO v_blob;

  v_bfile := BFILENAME('IMAGE_DIR', p_name);
  Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
  Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
  Dbms_Lob.Fileclose(v_bfile);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

PROCEDURE Get (p_name  IN  images.name%TYPE,
               p_type  IN  VARCHAR2 DEFAULT 'gif') IS
  v_blob  BLOB;
  v_amt   NUMBER := 30;
  v_off   NUMBER := 1;
  v_raw   RAW(4096);
BEGIN
  SELECT image
  INTO   v_blob
  FROM   images
  WHERE  name = p_name;

  Owa_Util.Mime_Header('image/' || p_type);

  BEGIN
    LOOP
      Dbms_Lob.Read(v_blob, v_amt, v_off, v_raw);
     
      Htp.Prn(Utl_Raw.Cast_To_Varchar2(v_raw));
      v_off := v_off + v_amt;
      v_amt := 4096;
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
END;

PROCEDURE Get_Gif  (p_name   IN images.name%TYPE) IS
BEGIN
  Get (p_name, 'gif');
END;

PROCEDURE Get_Jpeg (p_name  IN  images.name%TYPE) IS
BEGIN
  Get (p_name, 'jpeg');
END;

END;
/
Load Images
Next we load some images into the database:

EXEC Images_API.Load('OCP.gif');
EXEC Images_API.Load('opn_logo2.gif');
EXEC Images_API.Load('Title.gif');
EXEC Images_API.Load('Google.gif');
Test It
With the DAD configured and the PL/SQL code in place the XML document can be retrieved by entering the correct URL into a browser:

http://yourServer:7777/pls/SCOTT/Images_API.Get_Gif?p_name=OCP.gif

For security reasons you may wish to access the data over SSL:

https://yourServer:443/pls/SCOTT/Images_API.Get_Gif?p_name=OCP.gif