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 Oracle 8i 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

The SSL port is listed in the $ORACLE_HOME/Apache/Apache/setupinfo.txt file.

You May Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *