POST Up a BLOB to an Oracle Table via REST

thatjeffsmith ORDS 3 Comments

Tell Others About This Story:

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.

BINARY.

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”

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

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

Tell Others About This Story:

Comments 3

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

    Pierre

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

  3. 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(T4CTTIoer.java:450)

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

    CREATE OR REPLACE FORCE VIEW APEX_RELEASE AS
    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.

    Cheers,
    ~Dietmar.

Leave a Reply

Your email address will not be published. Required fields are marked *