HTML with Embedded Images from PL/SQL

Background

The IMG tag allows you to include images in HTML. Typically you see this with the source containing a URL, as shown below.

<img src="http://oracle-base.com/images/site_logo.gif" />

Using a URL in the tag means the browser (or email client) must make a separate HTTP request to get the image. An alternative is to actually embed the image data into the IMG tag. The basic format of the tag contents is shown below, where "mimetype" is the mime type of the image ("image/png", "image/gif" etc.) and "data" is the base64 encoded data that makes up the image.

<img src="data:<mimetype>;base64,<data>" />

There are a couple of reasons why you might prefer this method:

Reducing the total number of HTTP requests against your app server may improve performance, provided you are not constantly embedding large images.

When HTML may be viewed offline, like HTML emails, embedding the images rather than using URLs can be advantageous.

This article illustrates how to build an embedded image in HTML using PL/SQL.

Setup

The example code in this article requires a number of objects to be created.

First, create a directory object pointing to a physical directory on the database server that the "oracle" user has read/write permissions on. This will be used by the file system examples.

Next, create and populate a table to hold images for the database example. In this case I'm just using a single image called "site_logo.gif" that is loaded into the table from the 'IMAGES' directory created previously.

The HTTP example needs access to the internet (or some other HTTP server). If you are using Oracle 11g, you will need to make sure an appropriate ACL is present to allow network access from the database. You can see how this is done here.

We need a way of checking that the HTML is actually generated correctly. To do that we will write it out to the file system using the following procedure.

The code in first draft of the article was a little verbose. Anton Scheffer suggested using the following function to do the base64 encoding, so I've switched across to it where possible, or used a similar approach where a straight substitution doesn't fit.

Encoding Images from the File System

The following procedure uses the DBMS_LOB package to read chunks of data from a BFILE pointing to the image on the filesystem. The UTL_ENCODE and UTL_RAW packages are used to encode the data and convert it to a string suitable for inclusion into the HTML.

Encoding Images from a BLOB Column

The following procedure uses an image stored in a BLOB column of table.

CREATE OR REPLACE PROCEDURE get_enc_img_from_tab (p_image_name IN VARCHAR2,
p_clob IN OUT NOCOPY CLOB)
AS
BEGIN
SELECT p_clob || base64encode(image)
INTO p_clob
FROM images
WHERE name = p_image_name;
END;
/

Test It

We now have three procedures to retrieve image data and encode it so it can be added to a HTML document. Next, we need to write the code to generate some HTML and call one of the procedures to embed the image data. The following code creates a HTML document in a temporary CLOB. Examples of all three procedure calls are present, so try each of them out in turn.

Once the HTML is in the temporary CLOB it could be published as an email, a web page (using the embedded PL/SQL gateway or a mod_plsql enabled application server) or written to the file system. For simplicity I will just write it to the filesystem, but examples of the other technologies are available from the links at the end of the article.