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
 l_bfile BFILE;
 l_blob BLOB;
 l_dest_offset INTEGER := 1;
 l_src_offset INTEGER := 1;
BEGIN
 INSERT INTO images (id, name, image)
 VALUES (images_seq.NEXTVAL, p_name, EMPTY_BLOB())
 RETURN image INTO l_blob;
 l_bfile := BFILENAME('IMAGE_DIR', p_name);
 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
 -- loadfromfile deprecated.
 -- DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
 DBMS_LOB.loadblobfromfile (
 dest_lob => l_blob,
 src_bfile => l_bfile,
 amount => DBMS_LOB.lobmaxsize,
 dest_offset => l_dest_offset,
 src_offset => l_src_offset);
 DBMS_LOB.fileclose(l_bfile);
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 RAISE;
END;
PROCEDURE Get (p_name IN images.name%TYPE,
 p_type IN VARCHAR2 DEFAULT 'gif') IS
 l_blob BLOB;
BEGIN
 SELECT image
 INTO l_blob
 FROM images 
 WHERE name = p_name;
 OWA_UTIL.mime_header('image/' || p_type, FALSE);
 HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob));
 HTP.p('Content-Disposition: filename="' || p_name || '"');
 OWA_UTIL.http_header_close;
 -- Start 9i method.
 DECLARE
 l_amt NUMBER := 30;
 l_off NUMBER := 1;
 l_raw RAW(4096);
 BEGIN
 LOOP
 DBMS_LOB.read(l_blob, l_amt, l_off, l_raw);
 
 HTP.prn(UTL_RAW.cast_to_varchar2(l_raw));
 l_off := l_off + l_amt;
 l_amt := 4096;
 END LOOP;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN 
 NULL;
 END;
 -- End 9i method.
 -- 10g method.
 -- WPG_DOCLOAD.download_file(l_blob);
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;
/

In Oracle 10g you can replace the loop that reads from the BLOB and pushes the buffer over the gateway with a single call to WPG_DOCLOAD.DOWNLOAD_FILE.

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.

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)