One of the coolest features of Oracle REST Data Services v3.0 and higher, is the ability to REST enable your Oracle tables and views.

You get a full REST api on your table – INSERT, UPDATE, DELETE, Select with or without predicates and sorting, and getting metadata. You can also batch load records.

I’ve talked about the SELECT and GET bits before.

What I’m going to show today is how to POST (INSERT) up a new record without writing any SQL.

Pre-Requisites

You have Oracle REST Data Services v3 or higher running.

You have REST enabled your Schema.

Your table has a primary key.

You have REST enabled your table.

REST enabled, check. Primary key, check.
REST enabled, check. Primary key, check.

Ok, let’s post up a new record.

The docs show us how.


3.3.1.7 Insert Table Row
This example inserts data into the object. The body data supplied with the request is a JSON object containing the data to be inserted.

If the object has a primary key, then there must be an insert trigger on the object that populates the primary key fields. If the table does not have a primary key, then the ROWID of the row will be used as the item’s identifier.

If the object lacks a trigger to assign primary key values, then the PUT operation described in Update/Insert Table Row should be used instead.

Pattern: POST http://:/ords///

Example:

POST http://localhost:8080/ords/ordstest/emp/
Content-Type: application/json

{ “empno” :7, “ename”: “JBOND”, “job”:”SPY”, “deptno” :11 }

So let’s go do that.

Don't forget to set the content type!
Don’t forget to set the content type!

Now in the ‘religion’ of REST, everything is about the hyperlinks.

So what will our response be?

So, let's go GET it.
So, let’s go GET it.

Here’s the record.

Ta-da.
Ta-da.

A Few More Things

These are simple data types. We support complex ones too, including SPATIAL and GEO-JSON. You’ll need the latest version of ORDS for this.

You can upload multiple records too.

And I can UPDATE my existing record 🙂

PUT, not POST. And you supply the record in the URI via the PK value.
PUT, not POST. And you supply the record in the URI via the PK value.
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.

23 Comments

  1. Naresh Mallela Reply

    Rather than inserting one row at a time, in others a POST call for each INSERT, is there a way to insert multiple rows through a single POST call

  2. Taha Siddiqui Reply

    Hello Jeff,
    I have as situation and need your help. I am working on BLE Gateway. It posts a data to an endpoint .
    How I can insert that data into my table using ords 18.1???

    • Taha Siddiqui

      The data posted on endpoint is in JSON format

    • Taha Siddiqui

      Yes, the data post to an ords endpoint.
      JSON data is not shown on page but BLE gateway validates its connectivity with ords endpoint.
      How can I INSERT that data into my table?

    • Taha Siddiqui

      My endpoint URL is as below where my BLE gateway is pointed and hitting url with JSON data.
      http://servername:7070/ords/ad/ble/ac233fc04e20

      some of the fields from data which is sent through gateway is as below:

      {
      timestamp: “2019-12-31T10:00:27Z
      type: “Gateway”
      mac:”AC233FC04E20″
      }

    • So that doesn’t look like a rest enabled table.

      What’s the SQL or plsql backing that endpoint? Also, that’s not valid json.

    • Taha Siddiqui

      I have a minew BLE gateway .
      In its config I entered my http://serverip:7070/ords/apx/ble/gws endpoint as SERVICE ACCESS URL.
      Now it sends data with 1sec “UPLOAD INTERVAL” in JSON format on this defined url.

      How I can fetch that data into my rest enabled table?

    • You need to publish a service that takes the JSON and executes an INSERT SQL call with that data.

    • Taha Siddiqui

      json data
      [
      {
      “timestamp”: “2017-04-28T08:16:13Z”,
      “type”: “iBeacon”,
      “mac”: “CC0101000011”,
      “bleName”: “MiniBeacon_00012”
      },
      {
      “timestamp”: “2017-04-28T08:16:14Z”,
      “type”:”S1″,
      “mac”: “CC3101000034”,
      “bleName”: “S1”
      },
      {
      “timestamp”: ” 2017-04-28T08:16:14Z”,
      “type”:”Unknown”,
      “mac”: “EF3101000034”,
      “bleName”: “MI”
      },
      {
      “timestamp”: ” 2017-04-28T08:16:14Z”,
      “type”:”Gateway”,
      “mac”: “EF3101000034”
      }
      ]

      my POST

      declare
      newId ble_data.id%type;
      begin
      insert into ble_data
      (ble_timestamp,ble_type,ble_mac,ble_blename)
      values(:timestamp,:type,:mac,:bleName)
      returning id into newId;

      :newId:=newId;
      :status:=201;

      end;

      It is inserting single record but how can I insert whole JSON data?
      Please HELP

  3. Hi Jeff,

    I have been testing ORDS out for use within our ORG. For right now, I have been able to install REST, autoREST enable the schema and autoREST enable the tables. In the setup that I have a user called restUser which is supposed to be the generic user account on which is REST services are enabled.

    How do I do gets and puts using restUser account on tables owned by HR schema? Do I need to create views or synonyms?

    Thanks and Regrds
    Ravi

    • Is restUser and ORDS/Jetty user with ‘sql developer’ role, or is restUser a database user?

  4. For ORDS, can we authenticate against Db table directly as we can do in apex authentication scheme? I would like to reuse the same login table for both apex apps & ORDS authentication .

  5. Since the doc says: “there must be an insert trigger on the object that populates the primary key fields” – is it assuming the PK is an integer, surrogate key or identity column with an associated sequence?

  6. Thanks,
    I think a very critical missing feature is custom authentication on ORDS.

    • how do you mean custom? We allow you to authenticate MANY different ways. Single sign-on, 3rd party OAUTH2, database…

Reply To Ravi Cancel Reply