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.
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://
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.
Now in the ‘religion’ of REST, everything is about the hyperlinks.
So what will our response be?
Here’s the record.
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 🙂
23 Comments
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
Yes.
Rather than inserting one row at a time, in other way a POST call for each INSERT, is there a way to insert multiple rows through a single POST call
Jeff, Can you please share the link where I can find those details
I already did
https://www.thatjeffsmith.com/archive/2017/09/bulk-load-an-oracle-table-from-csv-via-rest/
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???
The data posted on endpoint is in JSON format
Have that data post to an ords endpoint as well, it will be pretty straightforward.
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?
I would need to see your data and your rest endpoint definition.
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.
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.
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
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?
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 .
No, don’t think so.
I understand. So, is it something you are planning to provide ?
In our case, we use ORDS for native mobile Apps, and it is going very convenient to authenticate APEX and ORDS against one table.
So someone smarter than me, namely @krisrice, said you can do that today, check this out.
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?
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…