Slide 1
Slide 1 text
REST APIs and
the Oracle Converged Database
Distinguished Product Manager
[email protected]
Tweets: @thatjeffsmith
Blogs: https://www.thatjeffsmith.com
Jeff Smith
Slide 2
Slide 2 text
• 75 minutes lecture
• Hands-On Lab introduction
• Questions? Zoom Q&A
• Recorded? YES
• SLIDES? YES
Format
Slide 3
Slide 3 text
• What makes an API, RESTful?
• Why should we care?
• How it works/Architecture
• examples
• Oracle Converged Database
• Database Management APIs
Agenda
Slide 4
Slide 4 text
• Representational Stateless Transfer
• Architectural Style for networked applications
• Communicated via HTTPS, but
• HTTPS <> REST
REST
Slide 5
Slide 5 text
No content
Slide 6
Slide 6 text
• 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
Slide 7
Slide 7 text
• Everything is HTTPS/JSON
• Developers will go around you
• Database needs CI/CD, automation, mon…
• Microservices!
Cool, but why do I care?
Slide 8
Slide 8 text
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
Slide 9
Slide 9 text
• 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
Slide 10
Slide 10 text
• 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
Slide 11
Slide 11 text
• 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
Slide 12
Slide 12 text
• CLI for configuration changes or..
• Edit pool XML and/or Standalone properties files
Configuration
Slide 13
Slide 13 text
Watch Later…
Slide 14
Slide 14 text
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)
Slide 15
Slide 15 text
Where APIs come from
1. REST Enable Schema
2. REST Enable Object OR
2. Publish a REST Module
Slide 16
Slide 16 text
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;
Slide 17
Slide 17 text
Step 1…you’re not done
Slide 18
Slide 18 text
Built-in Dev Tools
Slide 19
Slide 19 text
No content
Slide 20
Slide 20 text
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
Slide 21
Slide 21 text
AUTOREST: TABLE
Slide 22
Slide 22 text
CRUD APIs, for free
Slide 23
Slide 23 text
POST => INSERT
Slide 24
Slide 24 text
PUT => UPDATE
Slide 25
Slide 25 text
DELETE => DELETE
Slide 26
Slide 26 text
POST…BatchLoad
Slide 27
Slide 27 text
Oracle Confidential – Internal/Restricted/Highly Restricted
Batchloading 10M rows
Slide 28
Slide 28 text
GET /?q=…
Slide 29
Slide 29 text
No content
Slide 30
Slide 30 text
Like coding? Modules!
Slide 31
Slide 31 text
RESTful Service
•Your code -
•Inputs, outputs, error handling,
response codes, formatting
•Full access to SQL/PLSQL
•Easily exported, source controlled
•Transparent
Techniques/tricks
Slide 32
Slide 32 text
REST Module: 2+Tables
Slide 33
Slide 33 text
SQL: JOIN
Slide 34
Slide 34 text
Binds in the URI
Slide 35
Slide 35 text
SQL: CURSOR
Slide 36
Slide 36 text
SQL & Paging
Slide 37
Slide 37 text
ALT: AUTOREST a VIEW
Slide 38
Slide 38 text
REST is LINK DRIVEN
FAIL
Slide 39
Slide 39 text
GENERATING LINKS
Slide 40
Slide 40 text
Building your Templates
Avoid dead-ends
Slide 41
Slide 41 text
POST vs GET
Slide 42
Slide 42 text
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
Slide 43
Slide 43 text
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…
Slide 44
Slide 44 text
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
Slide 45
Slide 45 text
AUTO PL/SQL REFCUR
Slide 46
Slide 46 text
ANON Block REFCUR
Slide 47
Slide 47 text
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;
/
Slide 48
Slide 48 text
Uploading Files - BLOBS
Slide 49
Slide 49 text
No content
Slide 50
Slide 50 text
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
Slide 51
Slide 51 text
GET HTTPS://.../ords/hr/soda/latest/thatjeffsmith/123456...
JSON Documents
POST HTTPS://.../ords/hr/soda/latest/thatjeffsmith/
Slide 52
Slide 52 text
Spatial, GeoJSON
Slide 53
Slide 53 text
XML
Slide 54
Slide 54 text
Analytics & ML
Slide 55
Slide 55 text
No content
Slide 56
Slide 56 text
• 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
Slide 57
Slide 57 text
Secure APIS
Slide 58
Slide 58 text
Security
OAuth2
Slide 59
Slide 59 text
DBA. Me. Want. Stuff!
• Provide access, easily to your DBs
• Automation
• Monitoring
Slide 60
Slide 60 text
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
Slide 61
Slide 61 text
DB-API: Turn it on
true
Slide 62
Slide 62 text
Oracle Docs OpenAPI.JSON Endpoint
DB-API: Docs
Slide 63
Slide 63 text
Service Catalog
Slide 64
Slide 64 text
No content
Slide 65
Slide 65 text
Data Pump via HTTPS
{
"datapump_dir":"DATA_PUMP_DIR",
"filter" :"HOCKEY_STATS",
"job_mode" :"TABLE",
"threads" : 2
}
POST BODY Response
Slide 66
Slide 66 text
Web Tooling for Users
Slide 67
Slide 67 text
Homework
Slide 68
Slide 68 text
Extra Credit!
Slide 69
Slide 69 text
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!