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

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. 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. None
  3. • What is REST? • Some quick HTTP basics •

    RESTful Development In 30 minutes…
  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. 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. ‘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. • 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. • 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. Getting Started with RESTul Services I’m ready to build, now

    what?
  10. Features RESTful Services, AutoREST, SQL Developer Web, DB API, SODA,

    REST Driver
  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
  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
  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
  14. An HTTP Response Header Body

  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
  16. Install & Configure ORDS REST Enable a SCHEMA Publish a

    RESTful Service or REST Enable a schema object 1 2 3 Getting Started
  17. • Endpoints defined per schema • Services executed as that

    user REST Enable Schema
  18. • Automatic REST (tables, views, stored procs) • RESTful Services

    Schema Based Services
  19. Oracle SQL Developer • Full ORDS Integration • Develop RESTful

    Services • REST Enable Objects • Manage ORDS Roles and Privileges IDE
  20. CLI & PL/SQL APIs

  21. Application Express REST Development

  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!
  23. AUTO REST Features

  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
  25. • GET • PUT • POST • DELETE AutoREST Table

    /ords/hr/employees/ /ords/hr/employees/:id
  26. None
  27. • No code • SELECT * … • Query Predicates

    with ?q={…} • Paged results • Links to items GET on a REST Enabled TABLE
  28. • Schema level Metadata • Table Metadata • Get (

    Select ) • Query ( Filtering/Order/ASOF ) • Insert • Update • Delete • Load CSV
  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
  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
  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?
  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)
  33. PL/SQL TABLE API RETURN REFCURSOR

  34. RESTful Services

  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
  36. A RESTful Service in ORDS

  37. A RESTful Service in ORDS

  38. A RESTful Service in ORDS

  39. ORDS Request

  40. ORDS Response

  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”)
  42. /ords/hr/test2/media /ords/hr/test2/media/ /ords/hr/test2/media/:id URI Templates Tip: Model your templates, first!

  43. Handlers – inputs, code, & response

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

    record only/no paging Source Types - Shapes Responses
  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
  46. RESTful Service Examples

  47. POST: Upload a file to a TABLE.BLOB

  48. The Response

  49. RESTful Service Handler Code

  50. select title ,content_type ,id "$uri" from gallery order by title

    GET: Print a list of Rows as LINKs
  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
  52. RESTful Service Source

  53. OpenAPI/Swagger

  54. Source Control Support • Source Control your RESTful Services via

    scripts • Liquibase SQLcl makes this easy! Upgrades, rollbacks, dependencies, history tracking and more.
  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
  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
  57. None