Downloading files in Oracle by name via REST APIs

Question: How can I build a REST API for downloading files in Oracle Database…by their file name? Said files are from a BLOB in a table.

It’s really easy if you’re using Oracle REST Data Services (ORDS). Let’s take a quick look at one way to do it.

But first, we need a table, and some data.

The TABLE

CREATE TABLE BLOBS
   (
    ID NUMBER(*,0), 
    DESCRIPTION VARCHAR2(25), 
    THE_FILE BLOB, 
    FILE_NAME VARCHAR2(128),
    MIME_TYPE VARCHAR2(128)
   ) ;

I’m going to let you supply your own data, but I have 2 GIFs and a PDF.

Browsing my table, I can see we have some rows, and some media…

Now click on one of the BLOBs, and you can ask to render the file…

For a PDF, I’ll want to inspect the ‘Media’ tab. If it were JSON, I could see the raw contents by looking at the ‘Text’ panel, for example.

Now Gimme a REST API

In terms of designing our REST API, we just need a URI template, and to choose the proper HTTP Verb. In ORDS, I already have a ‘101’ module that I use to collect all of my ‘how do I’ examples.

VERB: GET

I’m just downloading files, so a GET is OK.

TEMPLATE: files/:file_name

My collection is ‘files/’, but I’m going to identify them by their file name, so I add a :file_name to the end of the URI.

Note that this text can be anything I want, but that I MUST use the :file_name bind variable in my SQL WHERE clause if I want ORDS to grab the information off of the HTTP GET request to feed to the SQL.

So I I instead used files/:x, my SQL would be WHERE FILE_NAME = 😡

Got it?

REST API as shown in SQL Developer Web

Note that my ‘Source Type’ is ‘resource/lob’ – that tells ORDS to simply stream the bits to the client, no need to do the ‘turn this into JSON’ trick that we do for say query results.

IF you include the mime_type in the query, we’ll send that down the pipe as well, making it easy for your client or browser to render the file.

If your files are ALWAYS PNGs, then you could simply do something like

SELECT ‘image/png’, the_file… — you’re hard-coding the mime type in the API, beware!

downloading files in Oracle — My file is called “A_GIF.GIF” – the :file_name from the URI is made available to the SQL SELECT.

Then when I call the API, and ask for ‘A_GIF.GIF’ – that’s what I get!

Our Handler as defined by the PL/SQL API

ORDS.DEFINE_HANDLER(
      p_module_name    => '101',
      p_pattern        => 'files/:file_name',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'select mime_type, the_file
from blobs
where FILE_NAME = :file_name');