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

Masterclass: REST APIs and the Oracle Converged Database for DBAs

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

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

June 16, 2021
Tweet

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
  5. None
  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

  19. None
  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=…

  29. None
  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

  49. None
  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

  55. None
  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

  64. None
  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!