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.
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…
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.
I’m just downloading files, so a GET is OK.
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 = 😡
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!
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');