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

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?’
Author

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

14 Comments

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

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

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

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

  5. Hadi Aldbs Reply

    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.

    • Hadi Aldbs

      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) ?

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

Write A Comment