ORDS web services returning BLOBs

When it comes to deliving blobs from the database, I'm sure many of us have used, or came across a procedure that look like the one described here.

This sample includes some commentary on how the surrounding infrastructure should look, but it's a little out of date.

create or replace procedure get_image(p_id  IN  images.id%TYPE) IS
  l_mime        images.image_type%TYPE;
  l_length      NUMBER;
  l_lob         BLOB;
-- This procedure needs
  -- Grant to apex_public_user
  -- Public synonym, so not reliant on #OWNER#
  -- Function wwv_flow_epg_include_mod_local needs to include this procedure name
  -- Test rendering using call such as
  -- http://domain.com.au/pls/apex/GET_IMAGE?p_id=2
  -- If not found, check case, synonym
  -- If no permission, check execute priv, presence in wwv_flow_epg_include_mod_local
BEGIN
  IF p_id IS NOT NULL THEN
    SELECT image_type, the_blob, DBMS_LOB.GETLENGTH(the_blob)
    INTO   l_mime, l_lob, l_length
    FROM   images
    WHERE  id = p_id;

    OWA_UTIL.MIME_HEADER(COALESCE(l_mime, 'application/octet'), FALSE);
    HTP.P ('Content-length: '||l_length);
    OWA_UTIL.HTTP_HEADER_CLOSE;
    WPG_DOCLOAD.DOWNLOAD_FILE(l_lob);
  END IF;
END get_image;

There are alternative methods available, so you can make ORDS more inherently secure, as descibed by Kris Rice. In fact, this was the reason I needed to do something. Regression testing with a newer version of ORDS uncovered a difference with requestValidationFunction parameter.

For instance, you could use an application process, thanks to Joel Kallman for yet another solution ;p.

We've previously delivered other images using web services, thanking Kris again for the inspiration. The web service is a basic query on the images table, and it also means referencing the image within the HTML is easy
<img src="/ords/rest/image/fetch/3141592" />

Often this would translate to an expression like this within your report
<img src="/ords/rest/image/fetch/#IMAGE_ID#" />

Or perhaps

<img src="#YOUR_IMAGE_PATH##IMAGE_ID#" />

I recently upgraded SQL Developer to a relatively shiny 17.3, so I could use the fancy REST navigator in the connections window.

I've also been meaning to note down the process, and typical values, since I still only understand web services at a relatively basic level.
(secret: the real reason we write blogs is so we can google ourselves later.)

I new based on existing web services that the following values were required, so I just had to match these to the steps in the create wizard.

Module: image
URI Prefix: /image/
URI Pattern: fetch/{id}

The module being a group of templates, which are perhaps difference sources of images -> image table, user table, product table. Then a handler is created to get or post data.

The first step is a good start. I've always had trouble working out what value goes where in the URL equation, so it's great to see the translation in the first step.

Step 1 - Define Module

Step 2 finalises the URL by defining how the identifier for the database record will be provided.

Step 2 - Define Template

And we're at the summary already, noting we can create web services without initially publishing them for use (from step 1).

Step 3 - Summary

We can flip over to the SQL tab to show all that SQL Developer is doing behind the scenes - calling supplied APIs.

Step 3b - Review SQL

Now the web service should be visible in the SQL Developer Connections navigator.

SQL Developer UI

Righ click on the template to create a handler, in this case to GET the image.

Step 4 - Add Handler

All the source types return some form of text, except Media Resource, which delivers a binary representation of our blob.

Handler Type

And the handler is just a SQL statement fetching the blob from the table, filtering with a bind variable mapped to the {id} named in the URI pattern.

Handler SQL

The query in the image:

select image_type as mimetype
,the_blob
from images
where id = :id

All pretty simple so far, but we had another similar procedure that delivered binary content using bfilename(). ie - files in the file system, resolved via an Oracle Directory.
I figured I could still write a query that delivered this using a table function:

select mime_type, document
from table(get_job_doc(:code));

Where get_job_doc returns an object type. The function doesn't need to be pipelined, because it only returns the one row. I describe the relevant pseudo-code in this forum post.
It works, but I had trouble adding content-disposition in the header, so .msg files don't download well. I bet there is some tidy ORDS feature/solution I'm yet to discover.

In the end, one procedure was replaced with a web service, another with an application process. Both more secure options that don't require custom PL/SQL to be available via the URL. A whitelist is safer when there's no need for one.

In the era of cloud, web services are king.
Now we just need to wait for ORDS & APEX to talk the same data set when it comes to manipulating these services.