Let’s say you have some files, stored as BLOBs, in your database, and you want to make them available via REST.
So of course you install Oracle REST Data Services, and now you have two options:
- REST Enable the table or
- Deploy a custom RESTful service
With the first option, I get an ‘automatic’ REST endpoint for my resource, which in this case is my table that has the BLOB, but I don’t want or need the PUT, POST, or DELETE methods, and I don’t want a SELECT * on the GET which comes with AUTO-REST.
So, I’m going to deploy a RESTful Service.
For this sample, I’m using SQL Developer v4.2 EA2 and my Oracle Cloud, Exadata Express service.
In this version of SQL Developer, I can create and modify my RESTful services via the connection tree. This is much easier than using my ORDS login via the ORDS Development panel.
@sqlmaria asked me to remind you folks just how to create and publish a RESTful Service. Prior to version 4.1, you had to use the REST Development panel – and I talk about that here.
In version 4.2, you have the option of also working directly with your REST modules in the Connection Tree – you can follow along in this post to see what that looks like, but I’ll probably write another post that goes into more detail.
Note that I needed to REST enable my schema before I could deploy a RESTful service there.
So I’m returning a BLOB. I’ve changed the Source Type to ‘Media Resource.’ The query behind my service is pretty simple – I select the BLOB from the table. But, i’m ALSO going to select the media type (MIME) info so the browser knows how to treat the data it’s being sent.
Now, in my table I have a column that describes the data in the BLOB for the browser, so I can just include that in the query. If it’s not there, you could also hard code it into the query, say like…
SELECT 'application/json', BLOB FROM TABLE WHERE column1=:id
That might be OK if all your files are of the same type, but it’s be better if you wouldn’t have to hard code that info. But, that’s besides the point. Once the info is included in the REST GET response, the browser will know how to treat it.
My table DOES have a column containing the media info, or I can just add that to my query.
Here’s a text file containing JSON.
I also have a PNG image file in my table. Let’s print that record.
The Chrome Dev Tool panel shows me what’s actually returned…and on the left we can see the browser showing me the PNG image file.
What Happens if we JUST GET the BLOB?
Nothing very much exciting…and all that makes it to the browser’s user is an empty JSON document.
Wait, I need to step back, from the beginning…
I hear ya. I’m a database guy, so this REST stuff kind of new to me too. Thankfully I have our REST Data Services Installation, Configuration, and Development Guide to rely on.
There’s a nice example of pulling BLOB images back in a RESTful service, start-to-finish in our ORDS Documentation library.
A Bit More REST-Y
After reading this post again, having a GET handler on a Template called ‘select…’ didn’t seem respectful of the way of the REST. So I renamed them.
14 Comments
Hi Jeff,
It seems the select mime_type, json_col from j_table statement would pick from the first record in the table if no criteria given.
How to publish multiple json records to the client.?
Thanks,
Arun
Where in this post is there any mention of JSON?
Hi Jeff, I am having some trouble with the enconding of an csv that is returned from a GET “source_type_media”. Is there a way I can force an certain character encoding? I tried to change how the database save the characters, and it was not successful. I tried the following and also not successful:
select
‘text/csv;charset=UFT-8’, csv, ‘relatorio_barragens.csv’
FROM
snirh_ig.mv_relatorio_barragens;
Here is how it appears when I open the file in excel.
Barragem na Ãrea “A” Módulo 12 PAD-DF (Fazenda São Francisco)
And the csv file encoding is not changing:
Thanks for your help!
Hi Jeff
I have a report that call my rest service to return the logo stored in the database.
I want the rest service to return a different image if the member record does not contain a logo.
I don’t want to put a function in the calling SQL to do a check before calling the resource as the table is huge and the performance would be impacted.
If there a way to check in the media resource handler and return a different blob is a 404 will be returned
Thank you
Sandy
When you select the blob in your SQL, you don’t want to use nvl() ?
Jeff,
I created a web service to download file in a PDF viewwr or image viewer by Rest,,
is it possible to download blob files in a my table (more of one) directly in a file system?
Like previous I did by foe example bt dads
DADS.BLOBDOC_To_File
WRITE CONTENTS OF THE BLOB TO A FILE |
— +————————————————————-+
v_out_file := UTL_FILE.FOPEN(
location => ‘UPLOAD_REFERTI’, — AS ‘/u03/oradata/referti’; ‘EXAMPLE_LOB_DIR’,
filename => to_char(DATAREFIN,’RRMM’)||’-‘||sREFERTO||’-‘||SEDEx||’-‘||NAMEFILE||’.pdf’, –‘iDevelopment_info_logo_2_NEW.tif’,
open_mode => ‘wb’,
max_linesize => 32767);
WHILE v_amount >= v_buffer_size
….
thanks
the service returns the blob, if you want it to be downloaded as a file, you can control that with the http response headers
Hi Jeff,
please i need API to get photo but it depends on some PL/SQL before the select which will get the photo,
as mentioned we need to use Media Resource but how to use the PL/SQL before? (it is a procedure )
Best
A function can be called pretty easily in a SQL statement..I’m not sure what your procedure does or what your intended workflow is though.
Unfortunately i have to use procedure.. I am asking how to use PLSQL as Source Type in ORDS (not Media Resource) and still be able to get the media as a return..
Thank you,
But I am not very happy to explain my target, it is :
Looping from my blob table
then by REST write every blob column (it could image pdf word or others) in a file in a oracle directory created
UPLOAD_REFERTI (/u03/…)
when I run download by
BEGIN
ORDS.define_template(
p_module_name => ‘media_module’,
p_pattern => ‘media2/:filename’);
ORDS.define_handler(
p_module_name => ‘media_module’,
p_pattern => ‘media2/:filename’,
p_method => ‘GET’,
p_source_type => ORDS.source_type_media, — ‘resource/lob’
p_source => ‘SELECT content_type, content FROM media WHERE file_name = :filename’
);
COMMIT;
END;
It open pdf if column content is PDF
I would save column blob type in a file directly, only from table to file system
name of blob is anaother column of table where is Blob(pdf jpg) .
Thanks
I still don’t understand what you’re asking.
What exactly do you want to happen when your ords GET handler deals with a blob (file) ?
Jeff,
Would you say that this could be used as a production service to provide restful services within a organization? I couldn’t find any articles about if there could be a recomendation not to use this service in production? Thanks, I attended one of your lectures in Brasília – Brazil (Oracle Developer Tour – LATINO AMERICA)
it’s certified to run in production
in fact, it runs in our Oracle Public Cloud – so yeah, it’s good for production 🙂