8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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 (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...