8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

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.

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.