Masterclass: REST APIs and the Oracle Converged Database for DBAs

What YOU WILL LEARN in this session:
+ What does it mean for a service or API to be 'RESTful?'
+ HTTPS and JSON basics, and how they come together to make REST APIs possible
+ Best Practices for deploying Oracle REST Data Services (ORDS) for Oracle Database
+ How to build a SQL and PL/SQL based REST API
+ How to use AUTO features for existing tables, views, and PL/SQL
+ How to keep your APIs performant and secure
+ How to make your APIs developer friendly

More details at thatjeffsmith.com

Transcript

1. REST APIs and the Oracle Converged Database Distinguished Product Manager Jeff.D.Smith@oracle.com Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith
2. • 75 minutes lecture • Hands-On Lab introduction • Questions? Zoom Q&A • Recorded? YES • SLIDES? YES Format
3. • What makes an API, RESTful? • Why should we care? • How it works/Architecture • examples • Oracle Converged Database • Database Management APIs Agenda
4. • Representational Stateless Transfer • Architectural Style for networked applications • Communicated via HTTPS, but • HTTPS <> REST REST
6. • Predictable, familiar for developers • Model nouns not verbs • Proper Responses • LINKS RESTful APIs RESTful Ugh! POST things/ GET delete_things/ GET things/:id POST things/new_thing RESTful Ugh! Request: POST things/ Request: POST things/ Response: 201 Created Response: 200 OK Location /things/123
7. • Everything is HTTPS/JSON • Developers will go around you • Database needs CI/CD, automation, mon… • Microservices! Cool, but why do I care?
8. HTTPS > SQL > HTTPS GET https://host/ords/hr/beers/1 SELECT * FROM EMPS WHERE ID = 1 HTTP/1.1 200 OK { "id": 1, "name": "Jeff", "job": "PM", "salary": 100 } {json} URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT Oracle REST Data Services
9. • Webserver layout • Java Servlet • Tomcat/WLS or • Standalone Java app Tomcat, WLS ORDS Apache Static Files -HTML, CSS, JS /ords/… /db1/hr/emp/ JSON Binary HTML … HTTP(s) Results JDBC pool1 pool2 pool3 Request JDBC JDBC ORDS Architecture
10. • Each database gets a connection pool • CDBs, PDBs, or single instances • Each database gets a mapping pattern ords/ ords/db2/… ords/db3/… TIP 1: 3 ORDS front-ended with a load balancer TIP 2: Data Guard/Scan Listener for your DB, ORDS will immediately pick up DatabaseS and HA Tips
11. • JDBC Conn Pools • Default Size: 10 • 1st pool => ords/ • ORDS_PUBLIC_USER .. Tip 1 : Customers MUST tune connection pools Tip 2: Bigger Pools <> FASTER! Whitepaper: Real World Perf Sizing Guide Video: Office Hours Session Connection Pools
12. • CLI for configuration changes or.. • Edit pool XML and/or Standalone properties files Configuration
13. Watch Later…
14. ORDS & Your Database • ORDS_PUBLIC_USER • ORDS_METADATA • ORDS.ENABLE_SCHEMA() • APIs published to SCHEMA • APIs exec as USER Modeling ORDS_METADATA with Database Actions (ORDS)
15. Where APIs come from 1. REST Enable Schema 2. REST Enable Object OR 2. Publish a REST Module
16. REST Enabling BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'peeps', p_auto_rest_auth => TRUE); commit; END;
17. Step 1…you’re not done
18. Built-in Dev Tools
20. AUTOREST •CRUD APIs, no code •Maintained by ORCL •Feature rich •Optimized RESTful Service •Your code - •Inputs, outputs, error handling, response codes, formatting •Full access to SQL/PLSQL •Easily exported, source controlled •Transparent No or Low Code
21. AUTOREST: TABLE
22. CRUD APIs, for free
23. POST => INSERT
24. PUT => UPDATE
25. DELETE => DELETE
26. POST…BatchLoad
27. Oracle Confidential – Internal/Restricted/Highly Restricted Batchloading 10M rows
28. GET /?q=…
30. Like coding? Modules!
31. RESTful Service •Your code - •Inputs, outputs, error handling, response codes, formatting •Full access to SQL/PLSQL •Easily exported, source controlled •Transparent Techniques/tricks
32. REST Module: 2+Tables
33. SQL: JOIN
34. Binds in the URI
35. SQL: CURSOR
36. SQL & Paging
37. ALT: AUTOREST a VIEW
38. REST is LINK DRIVEN FAIL
39. GENERATING LINKS
40. Building your Templates Avoid dead-ends
41. POST vs GET
42. POST/PUT Tips • Request Mime Type: Application/JSON • :x – looks for {"x" :…} • Parameters • :bind is the VAR in the blog, name is the HEADER label • Use to work with REQUEST or RESPONSE elements of your API
43. PL/SQL 1. ORDS Enable PL/SQL (package, proc, func) 2. OR Publish a REST Module AUTO • RPC for PL/SQL program • Input params in req body • OUT/RETURNs transformed and returned as JSON response RESTful Service • Your code - • Inputs, outputs, error handling, response codes, formatting • Print HTML, return JSON, download files…
44. PL/SQL & REFCURSORs 1. SELECT – func with OUT sys_refcursor 2. ANON Block 3. Call SP, send OUT to a RESULTSET OUT PARAM 4. AUTO
45. AUTO PL/SQL REFCUR
46. ANON Block REFCUR
47. PL/SQL Download File CREATE OR REPLACE PROCEDURE download_file ( media_id NUMBER ) AS vMIMETYPE VARCHAR2(256); vLENGTH NUMBER; vFILENAME VARCHAR2(2000); vBLOB BLOB; BEGIN SELECT file_name, content_type, content INTO vFILENAME, vMIMETYPE, VBLOB FROM media WHERE id = media_id; vLENGTH := DBMS_LOB.GETLENGTH(vBLOB); owa_util.mime_header(NVL(vMIMETYPE, 'application/octet'), FALSE); htp.p('Content-length: ' || vLENGTH); htp.p('Content-Disposition: attachment; filename=' || SUBSTR(vFILENAME, INSTR(vFILENAME, '/') + 1) || ‘’); owa_util.http_header_close; wpg_docload.download_file(vBLOB); END download_file; /
48. Uploading Files - BLOBS
50. Converged Database • One DB engine – ALL data & workloads • Support for Microservices, Events, REST, SaaS… • Accessed/Managed with SQL * PL/SQL Read more on Converged Databases with Maria Colgan
51. GET HTTPS://.../ords/hr/soda/latest/thatjeffsmith/123456... JSON Documents POST HTTPS://.../ords/hr/soda/latest/thatjeffsmith/
52. Spatial, GeoJSON
53. XML
54. Analytics & ML
56. • Requests must be authenticated & authorized! • APIs are protected via required roles • Authentication can be managed by Web Server OR Use ORDS BASIC Auth Database Auth Built-in OAuth2 Workflow Secure APIS
57. Secure APIS
58. Security OAuth2
59. DBA. Me. Want. Stuff! • Provide access, easily to your DBs • Automation • Monitoring
60. DB-API: 500+ Endpoints Performance Monitoring PDB Lifecyle General Data Dictionary Reports • tables/, tables/{table} • indexes/… Multitenant Management • Create • Clone • Change State • Drop • Reports Problematic Activity • Sessions • Locks • Waits • Alert Log What isn’t running well? • ASH • AWR • RTSM • Top SQL Database Operations • DBCA • Data Pump
61. DB-API: Turn it on <entry key="database.api.enabled">true</entry>
62. Oracle Docs OpenAPI.JSON Endpoint DB-API: Docs
63. Service Catalog
65. Data Pump via HTTPS { "datapump_dir":"DATA_PUMP_DIR", "filter" :"HOCKEY_STATS", "job_mode" :"TABLE", "threads" : 2 } POST BODY Response
66. Web Tooling for Users
67. Homework
68. Extra Credit!
69. Resources • SlideShare/SpeakerDeck • Blogs (thatjeffsmith.com) • 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!