Slide 1
Slide 1 text
Master Product Manager Vice President, R&D
[email protected] [email protected]
@thatjeffsmith @krisrice
Oracle REST Data Services
RESTful Services for Oracle Database
Jeff Smith Kris Rice
Slide 2
Slide 2 text
No content
Slide 3
Slide 3 text
• What is REST?
• Some quick HTTP basics
• RESTful Development
In 30 minutes…
Slide 4
Slide 4 text
Architectural Style
• Client – Server
• Stateless
• Cacheable
• Uniform Interface
Exercising the API requires:
• Location of our resource (URL)
• Action to be performed (GET, PUT, …)
REpresentational Stateless Transfer (REST)
Slide 5
Slide 5 text
Things you can do with REST APIs
• Power applications – mobile, desktop, web…
• HTTP(S) is ubiquitous
• Continuous Integration & Delivery
• Automation (BASH + cURL)
• OCI comes with REST APIs for all tenancy and DBCS operations
Slide 6
Slide 6 text
‘Good’ REST
• Model resources, not actions:
• GET /ords/hr/employees/ - GOOD
• GET /ords/hr/delete_emp/ - BAD
• DELETE /ords/hr/employees/97 – GOOD
• Uniform operations on all resources:
GET, POST, PUT, DELETE
• Stateless requests, state transitions comm via hyper-links
• Throwing HTTP onto your API <> REST
Slide 7
Slide 7 text
• Oracle REST Data Services (ORDS)
• Auto REST enable your data
tables, views, SQL, PL/SQL
• Auto paged results
• Auto JSON responses
• Complete read-write functionality
• Full dev support (GUI, CLI, API)
• Oracle Cloud and On-Premises
REST Access to your Oracle Database
HTTPS/REST
JDBC
https://www.oracle.com/REST
Slide 8
Slide 8 text
• HTTPS vs SQL
• {json}
• Paged results
• Links for each record
• Meta-links for the collection
Example: EMPS of DEPT 50
GET HTTPS://.../ords/hr/employees/?={“departmentID”:50}
Slide 9
Slide 9 text
Getting Started with RESTul Services
I’m ready to build, now what?
Slide 10
Slide 10 text
Features
RESTful Services, AutoREST,
SQL Developer Web, DB API,
SODA, REST Driver
Slide 11
Slide 11 text
GET https://host/ords/human/peeps/ SELECT *
FROM EMP
HTTP/1.1 200 OK
{ "employee_id": 100,
"first_name": "Kris"
,"last_name": "Rice",
, “email": “[email protected]",
{json}
URI SQL & PLSQL
Map & Bind
HTTP Request
HTTP Response Transform to JSON SQL Result Set / Standard OUT
Oracle REST Data Services
ORDS REST Request & Response Workflow
Slide 12
Slide 12 text
Request, ask for something
Address: https://site.com/ords/thing/some
Method: GET
Header: Cache-Control: no-cache
Body: n/a
Response, what you get back
Status Code: 200 OK
Header: Content-Type: application/json
Body: {…}
Requests and Responses
Slide 13
Slide 13 text
curl --request POST \
--url http://localhost:8080/ords/hr/_/db-api/19.1.0/database/datapump/export \
--header 'authorization: Basic aHI6b3JhY2xl' \
--header 'content-type: application/json' \
--data '{
"datapump_dir": "DATA_PUMP_DIR",
"filter": "HOCKEY_STATS, UNTAPPD",
"job_mode": "TABLE",
"threads": 2
}'
An HTTP Request
Body
Header
Method
Slide 14
Slide 14 text
An HTTP Response
Header
Body
Slide 15
Slide 15 text
• ords – ords.war, Java servlet
• hr – schema (alias!), service handler code runs as this user
• odtug – module
• media/:id – template
• Methods supported
• GET, PUT, POST, DELETE
Unravelling an ORDS Request URI
Slide 16
Slide 16 text
Install &
Configure ORDS
REST Enable a
SCHEMA
Publish a RESTful
Service or REST
Enable a schema
object
1 2 3
Getting Started
Slide 17
Slide 17 text
• Endpoints defined per schema
• Services executed as that user
REST Enable Schema
Slide 18
Slide 18 text
• Automatic REST (tables, views, stored procs)
• RESTful Services
Schema Based Services
Slide 19
Slide 19 text
Oracle SQL Developer
• Full ORDS Integration
• Develop RESTful Services
• REST Enable Objects
• Manage ORDS Roles and
Privileges
IDE
Slide 20
Slide 20 text
CLI & PL/SQL APIs
Slide 21
Slide 21 text
Application Express REST Development
Slide 22
Slide 22 text
AUTO REST Advantages
• CRUD APIs, no code
• Single call to create
• Maintained by ORCL
• Lots of features
• Optimized
RESTful Service Advantages
• You’re in charge
• Inputs, outputs, error handling,
response codes, formatting
• Full access to SQL/PLSQL
• Easily exported, source
controlled
• Transparent
Choose your own adventure!
Slide 23
Slide 23 text
AUTO REST Features
Slide 24
Slide 24 text
• Auto REST Table
–Full CRUD API, Data Loading, Metadata (DESC)
• Auto REST View
–Read interface only (GET)
• Auto PL/SQL (RPC)
–POST to execute stored PL/SQL
–We accept {json} in, map to input params, grab output and
{json} out
Automatic – ORDS owns the code
Slide 25
Slide 25 text
• GET
• PUT
• POST
• DELETE
AutoREST Table /ords/hr/employees/
/ords/hr/employees/:id
Slide 26
Slide 26 text
No content
Slide 27
Slide 27 text
• No code
• SELECT * …
• Query Predicates with ?q={…}
• Paged results
• Links to items
GET on a REST Enabled TABLE
Slide 28
Slide 28 text
• Schema level Metadata
• Table Metadata
• Get ( Select )
• Query ( Filtering/Order/ASOF )
• Insert
• Update
• Delete
• Load CSV
Slide 29
Slide 29 text
• All rows /
• One row /:id – PK Value
–No PK, default to ROWID
–Multi-column PK /x,y,z
• Some rows /?q={json}
Query the TABLE
Slide 30
Slide 30 text
METHOD : PUT /:PK
REQUEST BODY : JSON
RESPONSE: 200 OK
• Location (Header)
• JSON (Body)
Table Columns in the POST
body {“column_name” :
column_value }
Update a ROW
Slide 31
Slide 31 text
• We auto-magically handle PL/SQL too
• RPC -> POST via HTTPS /ords/hr/procedureA
• Responses & Results (OUTs/RETURNs/REFCURSORs), in
{JSON}
Existing PL/SQL APIs?
Slide 32
Slide 32 text
ORDS takes parameters as JSON,
executes PL/SQL, grabs output,
sends back down as JSON
OUT INTEGER & SYS_REFCURSOR
{
"total_payroll": 631230,
"peeps_numbers": [
{
"id": 81,
"name": "Dummy4",
"salary": 0,
"hire_date": "2017-06-20T13:29:00Z"
},
{
"id": 65,
"name": "Bart",
"salary": 0,
"hire_date": "2017-06-20T13:29:00Z"
},
{
"id": 79,
…
}
RPC over HTTPS (POST)
Slide 33
Slide 33 text
PL/SQL TABLE API
RETURN REFCURSOR
Slide 34
Slide 34 text
RESTful Services
Slide 35
Slide 35 text
1. Define the URL Template /collection/:item
2. Define the Handler GET
3. Define the DB Work tied to said Request SELECT * FROM
4. Define Parameters for Request/Response Headers
read request data, write response output
5. Define the Response format {json}
RESTful Service Development Workflow
Slide 36
Slide 36 text
A RESTful Service in ORDS
Slide 37
Slide 37 text
A RESTful Service in ORDS
Slide 38
Slide 38 text
A RESTful Service in ORDS
Slide 39
Slide 39 text
ORDS Request
Slide 40
Slide 40 text
ORDS Response
Slide 41
Slide 41 text
• A collection of RESTful Services
• Secured as a group with a privilege
• Published or hidden as a group
• Default response pagination size
Modules (“packages”)
Slide 42
Slide 42 text
/ords/hr/test2/media
/ords/hr/test2/media/
/ords/hr/test2/media/:id
URI Templates
Tip: Model your templates, first!
Slide 43
Slide 43 text
Handlers – inputs, code, & response
Slide 44
Slide 44 text
• Collection Query
–Multiple records/paging
• Collection Query Item
–Single record only/no paging
Source Types - Shapes Responses
Slide 45
Slide 45 text
• Feed
–Multiple records/paging
–First column auto-generates $link
• PL/SQL
–Executes PL/SQL Block
• Media Resource
–First Column sets Mime Type
–Second Column sets raw content (no json-ification)
Feed Source Type
Source Types – Determines Responses
Slide 46
Slide 46 text
RESTful Service Examples
Slide 47
Slide 47 text
POST: Upload a file to a TABLE.BLOB
Slide 48
Slide 48 text
The Response
Slide 49
Slide 49 text
RESTful Service Handler Code
Slide 50
Slide 50 text
select title
,content_type
,id "$uri"
from gallery
order by title
GET: Print a list of Rows as LINKs
Slide 51
Slide 51 text
• POST request body
• :body (BLOB) or :body_text (CLOB)
• Who is accessing the end point
• Authenticated user is mapped to :current_user
• HTTP Response Status :status_code & Redirects
:forward_location
• :content_type, :fetch_size, :fetch_offset, :row_count, :page_offset
Helpers for your Code, Automatic :binds
Slide 52
Slide 52 text
RESTful Service Source
Slide 53
Slide 53 text
OpenAPI/Swagger
Slide 54
Slide 54 text
Source Control Support
• Source Control your RESTful Services via scripts
• Liquibase
SQLcl makes this easy!
Upgrades, rollbacks, dependencies,
history tracking and more.
Slide 55
Slide 55 text
Oracle
REST
Data
Services
5
5
2011 2015
APEX Listener
• APEX based RESTful
Services
ORDS v3.0
• APEX no longer required
• SODA
• AUTO REST
2012
APEX Listener v2
• ORDS based REST Services
• Standalone Jetty
2017
ORDS v17
• REST enabled SQL
• Type 3 JDBC Driver
• Open API (Swagger)
2018
ORDS v18
• PL/SQL Gateway
enhancements
2019
ORDS v19
• DB API
• SQL Developer Web
2020
ORDS 20.2
• Early Summer ETA
• Expanded DB-API
• SQL Developer Web
feature enhancements
Slide 56
Slide 56 text
• SlideShare
• Blogs
• GitHub
• Articles
UKOUG Scene Why REST, and What’s in it or Me?
Oracle Mag AUTO REST & REST Enabled SQL
• And don’t forget Oracle-Base!
ORDS on
Other Resources
Slide 57
Slide 57 text
No content