ODTUG at Home: RESTful Services Development with ORDS

ODTUG at Home: RESTful Services Development with ORDS

How to build RESTful Services for your Oracle Database with Oracle REST Data Services.

Overview of good 'RESTful' practices, and how to apply those to your SQL and PL/SQL code to work with your Oracle Database via HTTP(s).

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

May 20, 2020
Tweet

Transcript

  1. 1.

    Master Product Manager Vice President, R&D Jeff.D.Smith@oracle.com Kris.Rice@oracle.com @thatjeffsmith @krisrice

    Oracle REST Data Services RESTful Services for Oracle Database Jeff Smith Kris Rice
  2. 2.
  3. 3.

    • What is REST? • Some quick HTTP basics •

    RESTful Development In 30 minutes…
  4. 4.

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

    Things you can do with REST APIs • Power applications

    – mobile, desktop, web… • HTTP(S) is ubiquitous • Continuous Integration & Delivery • Automation (BASH + cURL) • OCI comes with REST APIs for all tenancy and DBCS operations
  6. 6.

    ‘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 • Stateless requests, state transitions comm via hyper-links • Throwing HTTP onto your API <> REST
  7. 7.

    • Oracle REST Data Services (ORDS) • Auto REST enable

    your data tables, views, SQL, PL/SQL • Auto paged results • Auto JSON responses • Complete read-write functionality • Full dev support (GUI, CLI, API) • Oracle Cloud and On-Premises REST Access to your Oracle Database HTTPS/REST JDBC https://www.oracle.com/REST
  8. 8.

    • HTTPS vs SQL • {json} • Paged results •

    Links for each record • Meta-links for the collection Example: EMPS of DEPT 50 GET HTTPS://.../ords/hr/employees/?={“departmentID”:50}
  9. 11.

    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
  10. 12.

    Request, ask for something Address: https://site.com/ords/thing/some 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
  11. 13.

    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
  12. 15.

    • ords – ords.war, Java servlet • hr – schema

    (alias!), service handler code runs as this user • odtug – module • media/:id – template • Methods supported • GET, PUT, POST, DELETE Unravelling an ORDS Request URI
  13. 16.

    Install & Configure ORDS REST Enable a SCHEMA Publish a

    RESTful Service or REST Enable a schema object 1 2 3 Getting Started
  14. 19.

    Oracle SQL Developer • Full ORDS Integration • Develop RESTful

    Services • REST Enable Objects • Manage ORDS Roles and Privileges IDE
  15. 22.

    AUTO REST Advantages • CRUD APIs, no code • Single

    call to create • Maintained by ORCL • Lots of features • Optimized RESTful Service Advantages • You’re in charge • Inputs, outputs, error handling, response codes, formatting • Full access to SQL/PLSQL • Easily exported, source controlled • Transparent Choose your own adventure!
  16. 24.

    • Auto REST Table –Full CRUD API, Data Loading, Metadata

    (DESC) • Auto REST View –Read interface only (GET) • Auto PL/SQL (RPC) –POST to execute stored PL/SQL –We accept {json} in, map to input params, grab output and {json} out Automatic – ORDS owns the code
  17. 25.

    • GET • PUT • POST • DELETE AutoREST Table

    /ords/hr/employees/ /ords/hr/employees/:id
  18. 26.
  19. 27.

    • No code • SELECT * … • Query Predicates

    with ?q={…} • Paged results • Links to items GET on a REST Enabled TABLE
  20. 28.

    • Schema level Metadata • Table Metadata • Get (

    Select ) • Query ( Filtering/Order/ASOF ) • Insert • Update • Delete • Load CSV
  21. 29.

    • All rows / • One row /:id – PK

    Value –No PK, default to ROWID –Multi-column PK /x,y,z • Some rows /?q={json} Query the TABLE
  22. 30.

    METHOD : PUT /:PK REQUEST BODY : JSON RESPONSE: 200

    OK • Location (Header) • JSON (Body) Table Columns in the POST body {“column_name” : column_value } Update a ROW
  23. 31.

    • We auto-magically handle PL/SQL too • RPC -> POST

    via HTTPS /ords/hr/procedureA • Responses & Results (OUTs/RETURNs/REFCURSORs), in {JSON} Existing PL/SQL APIs?
  24. 32.

    ORDS takes parameters as JSON, executes PL/SQL, grabs output, sends

    back down as JSON OUT INTEGER & SYS_REFCURSOR { "total_payroll": 631230, "peeps_numbers": [ { "id": 81, "name": "Dummy4", "salary": 0, "hire_date": "2017-06-20T13:29:00Z" }, { "id": 65, "name": "Bart", "salary": 0, "hire_date": "2017-06-20T13:29:00Z" }, { "id": 79, … } RPC over HTTPS (POST)
  25. 35.

    1. Define the URL Template /collection/:item 2. Define the Handler

    GET 3. Define the DB Work tied to said Request SELECT * FROM 4. Define Parameters for Request/Response Headers read request data, write response output 5. Define the Response format {json} RESTful Service Development Workflow
  26. 41.

    • A collection of RESTful Services • Secured as a

    group with a privilege • Published or hidden as a group • Default response pagination size Modules (“packages”)
  27. 44.

    • Collection Query –Multiple records/paging • Collection Query Item –Single

    record only/no paging Source Types - Shapes Responses
  28. 45.

    • Feed –Multiple records/paging –First column auto-generates $link • PL/SQL

    –Executes PL/SQL Block • Media Resource –First Column sets Mime Type –Second Column sets raw content (no json-ification) Feed Source Type Source Types – Determines Responses
  29. 51.

    • POST request body • :body (BLOB) or :body_text (CLOB)

    • Who is accessing the end point • Authenticated user is mapped to :current_user • HTTP Response Status :status_code & Redirects :forward_location • :content_type, :fetch_size, :fetch_offset, :row_count, :page_offset Helpers for your Code, Automatic :binds
  30. 54.

    Source Control Support • Source Control your RESTful Services via

    scripts • Liquibase SQLcl makes this easy! Upgrades, rollbacks, dependencies, history tracking and more.
  31. 55.

    Oracle REST Data Services 5 5 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 2020 ORDS 20.2 • Early Summer ETA • Expanded DB-API • SQL Developer Web feature enhancements
  32. 56.

    • 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
  33. 57.