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.
