DBA Hub

📋Steps in this guide1/7

HTML with Embedded Images from PL/SQL

Use PL/SQL to create HTML with embedded images.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

Background

The IMG tag allows you to include images in HTML. Typically you see this with the source containing a URL, as shown below. 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. 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.

Code/Command (click line numbers to comment):

1
2
3
<img src="https://oracle-base.com/images/site_logo.gif" />

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

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba
create or replace directory images as '/tmp/';
grant read, write on directory images to testuser1;

conn testuser1/testuser1@//localhost:1521/pdb1

create table images (
  id     number(10)    not null,
  name   varchar2(50)  not null,
  image  blob          not null,
  constraint images_pk primary key (id),
  constraint images_uk unique (name)
);

create sequence images_seq;

declare
  l_dir    varchar2(10) := 'IMAGES';
  l_file   varchar2(20) := 'site_logo.gif';
  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, l_file, empty_blob())
  return image into l_blob;

  l_bfile := bfilename(l_dir, l_file);
  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;
end;
/

create or replace procedure create_file_from_clob (p_dir  in varchar2,
                                                   p_file in varchar2,
                                                   p_clob in out nocopy clob)
as
  l_file  utl_file.file_type;
  l_step  pls_integer := 12000;
begin
  l_file := utl_file.fopen(p_dir, p_file, 'w', 32767);

  for i in 0 .. trunc((dbms_lob.getlength(p_clob) - 1 )/l_step) loop
    utl_file.put(l_file, dbms_lob.substr(p_clob, l_step, i * l_step + 1));
    utl_file.fflush(l_file);
  end loop;
  utl_file.fclose(l_file);
end;
/

create or replace function base64encode(p_blob in blob)
  return clob
is
  l_clob clob;
  l_step pls_integer := 12000; -- make sure you set a multiple of 3 not higher than 24573
begin
  for i in 0 .. trunc((dbms_lob.getlength(p_blob) - 1 )/l_step) loop
    l_clob := l_clob || utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(p_blob, l_step, i * l_step + 1)));
  end loop;
  return l_clob;
end;
/
3

Encoding Image Data

The original image could come from the file system, a BLOB column in a table in the database or a HTTP request from an app server. We shall deal with each of these below. Although I am using stored procedures in these examples, in a real system the code would be placed in packages. - Encoding Images from the File System - Encoding Images from HTTP - Encoding Images from a BLOB Column
4

Encoding Images from the File System

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace procedure get_enc_img_from_fs (p_dir  in varchar2,
                                                 p_file in varchar2,
                                                 p_clob in out nocopy clob)
as
  l_bfile bfile;
  l_step  pls_integer := 12000;
begin
  l_bfile := bfilename(p_dir, p_file);
  dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);

  for i in 0 .. trunc((dbms_lob.getlength(l_bfile) - 1 )/l_step) loop
    p_clob := p_clob || utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(l_bfile, l_step, i * l_step + 1)));
  end loop;

  dbms_lob.fileclose(l_bfile);
end;
/
5

Encoding Images from HTTP

The following procedure is similar to the previous one, except it reads data from a HTTP request, rather than from the filesystem. Alternatively, you could achieve the same result using the subtype of the . Thanks to Marco Gralike for reminding me about this. If we are using a HTTPS URL, we will need to make sure there is an appropriate ACL in place, and there is a wallet containing the root certificate of the site we are visiting.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create or replace procedure get_enc_img_from_http (p_url  in varchar2,
                                                   p_clob in out nocopy clob)
as
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_raw            raw(32767);
begin
  l_http_request  := utl_http.begin_request(p_url);
  l_http_response := utl_http.get_response(l_http_request);

  begin
    loop
      utl_http.read_raw(l_http_response, l_raw, 12000);
      p_clob := p_clob || utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_raw));
    end loop;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_http_response);
  end;
end;
/

create or replace procedure get_enc_img_from_http (p_url  in varchar2,
                                                   p_clob in out nocopy clob)
as
begin
  p_clob := p_clob || base64encode(httpuritype.createuri(p_url).getblob());
end;
/
6

Encoding Images from a BLOB Column

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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;
/
7

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. The output from all three calls should look exactly the same. You can see the output from the code here . Remember to view the page source to prove the image has been embedded into the HTML. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
declare
  l_clob  clob;
begin
  dbms_lob.createtemporary(l_clob, FALSE);

  -- Build the start of the HTML document, including the start of the IMG tag
  -- and place it in a CLOB.
  l_clob := '<html>
   <head>
     <title>Test HTML with Embedded Image</title>
   </head>
   <body>
     <h1>Test HTML with Embedded Image</h1>
     <p>And here it is:</p>
     <img src="data:image/gif;base64,'; 
 
  get_enc_img_from_fs (p_dir  => 'IMAGES',
                       p_file => 'site_logo.gif',
                       p_clob => l_clob);
 
  --get_enc_img_from_http (p_url  => 'https://oracle-base.com/images/site_logo.gif',
  --                       p_clob => l_clob);
 
  --get_enc_img_from_tab (p_image_name => 'site_logo.gif',
  --                      p_clob       => l_clob);
 
  -- Close off the IMG tag and complete the HTML document.
  l_clob := l_clob || '" alt="Site Logo" />
  <p>The end.</p>
  </body>
  </html>';
  
  -- The CLOB now contains the complete HTML with the embedded image, so do something with it.
  -- In this case I'm going to write it to the file system.
  create_file_from_clob (p_dir  => 'IMAGES',
                         p_file => 'EmbeddedImageTest.htm',
                         p_clob => l_clob);  


  dbms_lob.freetemporary(l_clob);
exception
  when others then
    dbms_lob.freetemporary(l_clob);
    raise;
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!