Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle REST Data Services 101

Oracle REST Data Services 101

REST, HTTP, JSON...oh my!

A deep dive on these topics and how Oracle REST Data Services (ORDS) ties it all together for your applications and the Oracle Database.


May 14, 2020

More Decks by thatjeffsmith

Other Decks in Programming


  1. • HTTP/S • REST • JSON • ORDS 101: Install,

    Config, & Dev Today’s Topics HTTPS/REST JDBC {JSON}
  2. • 70+% of all APIs are REST Based • Based

    on HTTP, which is easy and ubiquitous • No drivers to install or configure • Your entire IT stack can be managed by REST APIs… • …do you want your enterprise data to be left out? Why would the database care about REST?
  3. • Web listener for PL/SQL (APEX) • RESTful Services for

    Oracle Database • SQL Developer Web • Database Management REST API • Mongo style API for Oracle Database (SODA) Why ORDS?
  4. The Way of the Web Listener • Stateless • Each

    operation, new connection • Just make HTTP calls
  5. • HTTP: network protocol, built on TCP • HTTPS: secured,

    end-to-end version of HTTP • REST: architectural style • JSON: open, easy to read, interchange format Words with Friends
  6. • HyperText Transfer Protocol • Invented, along with HTML, by

    Tim Berners-Lee at CERN (1989) • Requests (GET, PUT, POST, HEAD, OPTIONS, DELETE…) • Status Codes (200, 201, 401, 403, 404, 500…) • Security (Authentication) HTTP
  7. Request, ask for something Address: https://site.com/collection/item 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
  8. 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
  9. 1. Define the URL Template 2. Define the Handler 3.

    Define the DB Work tied to said Request 4. Define Parameters for Request/Response Headers 5. Define the Response format Implementing this in ORDS
  10. • HTTP + TLS = HTTPS • The communication is

    encrypted • HTTP, anyone on your network can see EVERYTHING you pass back and forth from your server Passwords! Pictures! EVERYTHING HTTPS always, HTTP never…mostly
  11. ORDS & HTTPS • Jetty listens on 443 vs 8080

    • ORDS generates a self-signed cert • You should get a ‘real’ certificate (Lets Encrypt)
  12. Authentication – are you who you say you are? Authorization

    – are you allowed to do what you’re asking? Authenticated User: JEFF Roles: admin, dev, user, test, god Requested Resource: ‘user’ role required Security
  13. BASIC Auth • you provide a username/password on EVERY request

    • Not recommended Cookie Based • One time user/password, session info returned in a cookie • Each request now includes this cookie in its request header • Cookies ‘expire’ after X seconds OAUTH2 • Waaay too complicated, we’ll cover this ‘next time’ Authentication
  14. • WLS, Tomcat makes sure you are who you say

    you are • They pass your identity and roles down to ORDS • ORDS makes sure your role matches up to what’s required to access a REST Service Authentication is handled by the Webserver
  15. • Worst Case: ORDS Jetty Basic Auth • Next Best:

    ORDS OAUTH2 BUT MY APP MUST HAS ORACLE USER/PWD…. …OK, but it’s not GOOD. But I don’t have anything for AUTH 
  16. • Services or Patterns can be protected by Privileges •

    Privileges are tied to ROLES • ROLES are granted to authenticated USERS Authorization in ORDS
  17. 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)
  18. Database won’t always be available via SQLNet Scenario: APEX needs

    to pull data from a different DB • DB Links aren’t always available • ORDS powered REST API • APEX can pull data in via HTTP Real World Example
  19. Things you can do with REST APIs • Power applications

    – mobile, desktop, web… • HTTP(S) is ubiquitous, even avail in COBOL • Continuous Integration & Delivery • Automation (BASH + cURL) • OCI comes with REST APIs for all tenancy and DBCS operations
  20. ‘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, OPTIONS, HEAD • Stateless requests, state transitions comm via hyper-links • Throwing HTTP onto your API <> REST
  21. HTTP & REST Examples HTTP(S) client 1. Client makes request

    GET http://fridge/beers/beer 2. Server responds 200 http://fridge/beers/beer/meta 1. Client makes request POST http://fridge/beers/ {"id":1, "name":"Premium Ice Lite",…} 2. Server responds 201 Created http://fridge/beers/beer/1 Webserver
  22. JavaScript Object Notation • Jay-Sun • Not just for js

    • Skinnier than XML • Flexible • Link-friendly
  23. • Database output =>JSON • PLSQL OUT params, RETURNs, stdout

    / feedback => JSON • JSON on HTTP requests => :BIND parameters, PL/SQL input parameters • SQL data types and user defined types supported ORDS JSON Magic
  24. Oracle REST Data Services 3 7 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
  25. The PL/SQL Gateway ORDS started out as the APEX Listener,

    a way to call PL/SQL programs via HTTP A replacement for MOD_PLSQL GET /ords/user/package.procedure runs PL/SQL prints HTML back to the browser
  26. • Collection or Item Template /collection/:item Handler GET, PUT, POST,

    DELETE, powered by SQL/PLSQL • Organized into modules • Defined for a SCHEMA REST Services
  27. 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
  28. • Simple HTTP call w/query params • {json} response •

    Page the results • Links for each item • Handle the data types for me Example: EMPS of DEPT 50
  29. • Oracle 11gR2 and higher databases • Support tied to

    your Database license (MOS) • Single Instance, Container, or Pluggable deployments • On Premise or Oracle Cloud Infrastructure • Built by the Oracle Database Team FOR the Oracle Database All of this is available at no additional cost
  30. • 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