It seems kind of weird to do a blog post around a feature that’s fairly well documented, but this came up twice this week, so I figured I’d throw it out there.

Scenario: I want to post up a record to be inserted into a table. The record will consist of a couple of regular values, but also a file, which will be stored as a BLOB.

Ok, how does this happen?

Let’s start from the POST request perspective.

We’re going to send the ‘regular’ data via the header as parameters.

It just so happens my table has columns for Content-Type and a description.

The file is going to go up in the BODY of the request.


What does ORDS do with this?

The secret sauce bits, which are discussed in the docs here, is that ORDS auto binds the data from the body to :body.

It’s only avail on a POST or PUT.

This makes doing our INSERT very easy.

I can plug the BLOB value right in there by just referencing the data via ‘:body”
 image_id integer;
 insert into gallery (title,content_type,image) 
             values  (:title,:content_type,:body)
             returning id into image_id;
 :status := 201;
 :location := image_id;

The other two values are plucked off the request headers:

bingo, bango, bongo!

And that’s it, really.

So the request from POSTMAN goes up, and into my table goes the BLOB.

And that’s the REST of the story.

Step by Step Example

I go into MUCH more detail, and I share all the code for how to insert a ROW via REST API here. It covers all sorts of data, not just LOBs.

You can build this same scenario yourself – exact same code I used, it’s in our DOCS.


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


  1. Avatar

    Hello Jeff,

    Thank you for showing me this process. It seems to be working, however i cannot get the blob to display.

    this is how i would normally display a blob in the browser:

    DBMS_LOB.createtemporary (l_blob, TRUE, DBMS_LOB.SESSION);

    select CONTENT into l_blob
    where SU_DOC_ID= 14;
    OWA_UTIL.mime_header (‘application/pdf’, bclose_header => FALSE);
    — set content length
    HTP.p (‘Content-length: ‘ || DBMS_LOB.getlength (l_blob));
    — download the file and display in browser
    WPG_DOCLOAD.download_file (l_blob);
    — release resources
    DBMS_LOB.freetemporary (l_blob);

    however, something is going wrong and it is not displaying when I upload blobs using the api. Any suggestions?

    — Cole

    • thatjeffsmith

      You only need to select the blob.. the trick is to set the service as a media resource and have the first column of your query be the mine type so your browser knows what’s coming. I have more than a few examples on here.

  2. Avatar


    Nice example. Is it possible via restfull download or open blob (.pdf, .xls) in delohi firemonkey app? GL

  3. Avatar

    Hi Jeff,

    it works fine for me. Thank for the useful Information.

    I have another Question : How to POST Blob and another Parameters in the Body. We want to have Blob and some Parameters in the Body. Is there a way to have Blob and another parameters in the Body? Or must we set the Others Parameters in the Headers?

    Regards and Thanks


  4. Avatar

    Hi Dietmar

    thank you so much to provide this “hack”
    I have the same problem, and with this view we have now be able to upload document like in mod_plsql 🙂

    Just another little thing, it is necessary to CREATE PUBLIC SYNONYM FOR APEX_RELEASE and a GRANT SELECT on APEX_RELEASE TO PUBLIC

    Best regards

  5. Avatar

    Hi Jeff,

    many people try to switch from mod_plsql to ORDS with their old owa_toolkit applications. Some might have APEX installed, some not. Some might use both at the same time, some not.

    Anyhow, the parameter to make it work is apex.docTable in the defaults.xml in ORDS.

    Unfortunately it does not work as expected. As soon as ORDS detects that APEX is installed in the database ( APEX_UTIL_CHECK = “select version_no from apex_release”; ), it basically ignores the setting in apex.docTable for the connection pool and uses the APEX table. But the owa_toolkit application cannot access the uploaded file from FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ because there is no apex session.

    Unfortunately it doesn’t even work when apex is not installed because the check statement (( APEX_UTIL_CHECK = “select version_no from apex_release”; )) throws an error:
    Jun 27, 2016 11:52:10 PM oracle.dbtools.apex.hooks.fileUpload.ApexFileLoader checkDocMethod
    SCHWERWIEGEND: ORA-00942: table or view does not exist
    java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
    at oracle.jdbc.driver.T4CTTIoer.processError(

    A very simple “hack” for a schema that is used in a plsql gateway (owa_toolkit) only application is the following:

    select ” version_no,
    ” api_compatibility,
    ” patch_applied
    from dual;

    This way the statement does not bomb but it doesn’t return a valid APEX release number either => thus ORDS “believes” that APEX is not installed and the apex.docTable parameter is used and it works.

    But this is not really a good suggestion. You might run into issues when using APEX later in this schema.


Write A Comment