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

Masterclass: REST APIs and the Oracle Converged...

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

thatjeffsmith

June 16, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. REST APIs and the Oracle Converged Database Distinguished Product Manager

    [email protected] 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. • 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
  5. • Everything is HTTPS/JSON • Developers will go around you

    • Database needs CI/CD, automation, mon… • Microservices! Cool, but why do I care?
  6. 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
  7. • 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
  8. • 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
  9. • 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
  10. • CLI for configuration changes or.. • Edit pool XML

    and/or Standalone properties files Configuration
  11. 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)
  12. Where APIs come from 1. REST Enable Schema 2. REST

    Enable Object OR 2. Publish a REST Module
  13. 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;
  14. 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
  15. RESTful Service •Your code - •Inputs, outputs, error handling, response

    codes, formatting •Full access to SQL/PLSQL •Easily exported, source controlled •Transparent Techniques/tricks
  16. 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
  17. 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…
  18. 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
  19. 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; /
  20. 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
  21. XML

  22. • 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
  23. 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
  24. 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!