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.

Transcript

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