$30 off During Our Annual Pro Sale. View Details »

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. Master Product Manager Jeff.D.Smith@oracle.com @thatjeffsmith Oracle REST Data Services 101

    for the Oracle Database Professional Jeff Smith
  2. • HTTP/S • REST • JSON • ORDS 101: Install,

    Config, & Dev Today’s Topics HTTPS/REST JDBC {JSON}
  3. • 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?
  4. • 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?
  5. JDBC OCI/SQLNET Listener Pre-Internet • STATEFUL • Persistent Connections •

    Oracle Client / JDBC Driver required
  6. The Way of the Web Listener • Stateless • Each

    operation, new connection • Just make HTTP calls
  7. TNSPING, Dammit!

  8. • 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
  9. • 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
  10. Chrome Tools…peek behind the curtain!

  11. 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
  12. 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
  13. An HTTP Response Header Body

  14. 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
  15. A RESTful Service in ORDS

  16. A RESTful Service in ORDS

  17. A RESTful Service in ORDS

  18. ORDS Request

  19. ORDS Response

  20. • 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
  21. ORDS & HTTPS • Jetty listens on 443 vs 8080

    • ORDS generates a self-signed cert • You should get a ‘real’ certificate (Lets Encrypt)
  22. 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
  23. 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
  24. • 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
  25. • 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 
  26. Creating a Jetty User

  27. • Services or Patterns can be protected by Privileges •

    Privileges are tied to ROLES • ROLES are granted to authenticated USERS Authorization in ORDS
  28. 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)
  29. 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
  30. 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
  31. ‘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
  32. Tip: Model your Templates first! No Verbs! ☺

  33. 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
  34. JavaScript Object Notation • Jay-Sun • Not just for js

    • Skinnier than XML • Flexible • Link-friendly
  35. • 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
  36. ORDS JSON Magic

  37. 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
  38. 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
  39. Example: PL/SQL web listener

  40. • Collection or Item Template /collection/:item Handler GET, PUT, POST,

    DELETE, powered by SQL/PLSQL • Organized into modules • Defined for a SCHEMA REST Services
  41. GET https://host/ords/human/peeps/ SELECT * FROM EMP HTTP/1.1 200 OK {

    "employee_id": 100, "first_name": "Kris" ,"last_name": "Rice", , “email": “krisrice@oracle.com", {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
  42. • 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
  43. • 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
  44. • 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