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

ODTUG at Home: RESTful Services Development wit...

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

thatjeffsmith

May 20, 2020
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. Master Product Manager Vice President, R&D [email protected] [email protected] @thatjeffsmith @krisrice

    Oracle REST Data Services RESTful Services for Oracle Database Jeff Smith Kris Rice
  2. • What is REST? • Some quick HTTP basics •

    RESTful Development In 30 minutes…
  3. 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)
  4. 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
  5. ‘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
  6. • 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
  7. • 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}
  8. GET https://host/ords/human/peeps/ SELECT * FROM EMP HTTP/1.1 200 OK {

    "employee_id": 100, "first_name": "Kris" ,"last_name": "Rice", , “email": “[email protected]", {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
  9. 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
  10. 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
  11. • 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
  12. Install & Configure ORDS REST Enable a SCHEMA Publish a

    RESTful Service or REST Enable a schema object 1 2 3 Getting Started
  13. Oracle SQL Developer • Full ORDS Integration • Develop RESTful

    Services • REST Enable Objects • Manage ORDS Roles and Privileges IDE
  14. 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!
  15. • 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
  16. • GET • PUT • POST • DELETE AutoREST Table

    /ords/hr/employees/ /ords/hr/employees/:id
  17. • No code • SELECT * … • Query Predicates

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

    Select ) • Query ( Filtering/Order/ASOF ) • Insert • Update • Delete • Load CSV
  19. • 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
  20. 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
  21. • 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?
  22. 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)
  23. 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
  24. • A collection of RESTful Services • Secured as a

    group with a privilege • Published or hidden as a group • Default response pagination size Modules (“packages”)
  25. • Collection Query –Multiple records/paging • Collection Query Item –Single

    record only/no paging Source Types - Shapes Responses
  26. • 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
  27. • 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
  28. Source Control Support • Source Control your RESTful Services via

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