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:

  1. REST Enable the table or
  2. 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 the 'REST Data Services' item on the tree.
Note the ‘REST Data Services’ item on the tree.

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
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.

you want the mime info first and THEN the BLOB
you want the mime info first and THEN the BLOB

Here’s a text file containing JSON.

I have a Chrome JSON formatter extension, that's why the text is 'pretty printed'
I have a Chrome JSON formatter extension, that’s why the text is ‘pretty printed’

I also have a PNG image file in my table. Let’s print that record.

The panel to the right is Chrome Dev Tools...
The panel to the right is Chrome Dev Tools…

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.

We can see the blob coming back like we asked in dev tools, but no mime...
We can see the blob coming back like we asked in dev tools, but no mime…

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.

What if I decided I needed to add a POST for my resource, aka 'select_trick?'
What if I decided I needed to add a POST for my resource, aka ‘select_trick?’

I'm a Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.


  1. 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)

    • thatjeffsmith

      it’s certified to run in production

      in fact, it runs in our Oracle Public Cloud – so yeah, it’s good for production 🙂

Write A Comment