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

Pints with Oracle User Group (POUG) and Oracle REST Data Services

Pints with Oracle User Group (POUG) and Oracle REST Data Services

An overview of Oracle REST Data Services with all of the examples being BEER themed, thanks to our Polish friends and their awesome event and user group.

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

March 21, 2021
Tweet

Transcript

  1. RESTful Web Services for Oracle, POUG Edition ORDS Product Manager

    https://www.thatjeffsmith.com @thatjeffsmith Jeff Smith 🍻
  2. • Presented originally in 2019 for POUG in Wroclav, Poland

    • Pints with Oracle User Group • Combines two of my favorite things, ORDS & Beer (beeords!) • Please drink responsibly • I have non-alcoholic slides/content on my blog About these slides… Jeff annoys people on twitter @thatjeffsmith
  3. • product manager/storyteller • I help/bother people online • Working

    with Oracle Database since 1999 • Helping build database tools since 2001 • I ‘manage’ these products: whoami Jeff annoys people on twitter @thatjeffsmith
  4. Today’s Agenda Jeff annoys people on twitter @thatjeffsmith • Introduction,

    Terms, & Definitions • ORDS Architecture • Building RESTful Web Services
  5. Oracle REST Data Services Included for free with your Oracle

    Database!!! oracle.com/rest
  6. • REpresentational State Transfer • Architectural Style for networked applications

    • Communicates via HTTP, but.. • Using HTTP doesn’t magically make your APIs RESTful Terms: REST Jeff annoys people on twitter @thatjeffsmith
  7. • Model things, not actions • Use VERBS to interact,

    GET, PUT, POST, DELETE • Stateless, so be sure to use LINKs to guide your users POST beers/ => Adds beer, responds w/link GET beers/:id => Jeff annoys people on twitter @thatjeffsmith RESTful Architecture 🍻
  8. • Jay-Sun • Not just for js! • Skinnier than

    XML • Flexible • Link-friendly Terms: {JSON} Jeff annoys people on twitter @thatjeffsmith
  9. {JSON} Bonus Tip! Our tools make it easy to get

    your database data in JSON format, pretty or RAW Jeff annoys people on twitter @thatjeffsmith …
  10. ORDS Features and Architecture Jeff annoys people on twitter @thatjeffsmith

    • How are RESTful Web Services defined • How is ORDS deployed
  11. • Automatic • Your Code • SQL on the fly

    • Management APIs • SQLDev Web Delivery Options Jeff annoys people on twitter @thatjeffsmith
  12. GET https://host/ords/hr/beers/1 SELECT * FROM UNTAPPD WHERE ID = 1

    HTTP/1.1 200 OK { “id": 100, “name": “Bud Lite“, “score": “0", “comments": “yuck” } {json} URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT Oracle REST Data Services ORDS: HTTP to SQL and back Jeff annoys people on twitter @thatjeffsmith
  13. • Webserver layout • Java Servlet • Tomcat or WLS

    ORDS Architecture Option #1 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 Jeff annoys people on twitter @thatjeffsmith
  14. java –jar ords.war standalone • Supported for production! • ORDS

    is your webserver & web listener for your Oracle Databases ORDS Static Files -HTML , CSS, JS /db1/hr/emp/ JSON Binary HTML … HTTP(s) Response JDBC pool1 pool2 pool3 Request JDBC JDBC Jeff annoys people on twitter @thatjeffsmith ORDS Architecture Option #2 : Standalone
  15. • JDBC Conn Pools • 1 Pool Per Database •

    ORDS_PUBLIC_USER • Proxy Connects Enabled DB Users ORDS & Database Communication Jeff annoys people on twitter @thatjeffsmith
  16. • PUT – HTTP Verb • HTTPS – secure, encrypted

    version of HTTP (recommended!) • Server:8080 – network location where ORDS is running • ords – ords.war, Java servlet • hr – schema alias, where service is defined • examples – module, a collection of services • beers/:id – template, defines the resource Unwinding an ORDS Request Jeff annoys people on twitter @thatjeffsmith PUT https://server:8080/ords/hr/examples/beers/:id
  17. Install & Start ORDS REST Enable Application SCHEMA Publish RESTful

    Service or REST Enable object 1 2 3 Getting Started Steps Jeff annoys people on twitter @thatjeffsmith
  18. Jeff annoys people on twitter @thatjeffsmith Step 1, Install ORDS

    Video: I talk about the process, DO an install, use SQLDev Web to build a RESTful Web Service
  19. • Services defined in schema • Services executed as user

    Step 2. REST Enable Schema Jeff annoys people on twitter @thatjeffsmith Services constrained by Database User:
  20. • AUTO REST • RESTful Services Step 3. Define your

    Service Jeff annoys people on twitter @thatjeffsmith
  21. What RESTful Services could look like… Jeff annoys people on

    twitter @thatjeffsmith
  22. Twoje zdrowie! Or Cheers! • UNTAPPD: my personal beer diary

    • YES, these are MY pictures and MY comments • Examples will show building REST APIs for my diary 🍻 Jeff annoys people on twitter @thatjeffsmith
  23. Example – Beers w/pictures SELECT beer_name, brewery_name, created_at, photo_url "$pics",

    comments FROM untappd WHERE photo_url IS NOT NULL ORDER BY checkin_id DESC Jeff annoys people on twitter @thatjeffsmith GET all beers, with links to pictures
  24. Example – Using Query String Parameters ?q={"beer_type":{"$like":"Stout%25"}} Jeff annoys people

    on twitter @thatjeffsmith SAME SQL, but… ORDS injects the WHERE clause for us. No additional coding required!
  25. Example: More Query String Parameters ?q={"beer_type":{"$like":"%25Tripel%25"}} Jeff annoys people on

    twitter @thatjeffsmith
  26. Filtering, Sorting and More ORDS Handles the SQL for you

    Jeff annoys people on twitter @thatjeffsmith
  27. Example: Generating Links “$alias” => LINK Jeff annoys people on

    twitter @thatjeffsmith
  28. Example: Working w/JSON Data in the DB Jeff annoys people

    on twitter @thatjeffsmith BAD 
  29. Escaping JSON ☺ Jeff annoys people on twitter @thatjeffsmith

  30. Where/how to code your Services Jeff annoys people on twitter

    @thatjeffsmith
  31. Oracle SQL Developer • Full ORDS Integration • Develop RESTful

    Services • REST Enable Objects • Manage ORDS Roles and Privileges • Free! OS X, Windows, or Linux IDE Jeff annoys people on twitter @thatjeffsmith
  32. Database Actions/SQLDev Web • ORDS Single Page App • Develop

    RESTful Services • REST Enable Objects • Manage ORDS Roles and Privileges, OAuth2 Clients • Free! Any major browser. Browser Jeff annoys people on twitter @thatjeffsmith
  33. CLI & PL/SQL APIs Jeff annoys people on twitter @thatjeffsmith

    SQLcl Anywhere you can run PL/SQL
  34. Or Application Express (APEX) Jeff annoys people on twitter @thatjeffsmith

    APEX is NOT required
  35. Time to start building! Jeff annoys people on twitter @thatjeffsmith

  36. Home Brewing or Store Bought? Your Code Someone Else’s Jeff

    annoys people on twitter @thatjeffsmith
  37. AUTOREST • CRUD APIs, no SQL to write • Single

    ORDS call to create • Maintained by ORCL • Feature Packed • Optimized RESTful Services • You control: • Inputs, outputs, error handling, response codes, formatting • Your SQL/PLSQL code • Easily exported, source controlled • Transparent Choose your own adventure! Jeff annoys people on twitter @thatjeffsmith
  38. AUTOREST Database Objects TABLES VIEWS PROCEDURES FUNCTIONS PACKAGES Jeff annoys

    people on twitter @thatjeffsmith
  39. • Auto REST Table – Full CRUD API, Data Loading,

    Metadata (DESC) • Auto REST View – Read interface (GET), Write (PUT+POST+DELETE w/PK on VIEW) • 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 Jeff annoys people on twitter @thatjeffsmith
  40. • GET • PUT • POST • DELETE • DESC

    • DOCS AutoREST Table /ords/hr/beers/ /ords/hr/beers/:PK Jeff annoys people on twitter @thatjeffsmith
  41. Jeff annoys people on twitter @thatjeffsmith REST Enabling a TABLE

  42. REST Enabled TABLE Swagger API Doc • Table Metadata •

    SELECT Query Filtering/Order/ASOF SCN or Timestamp • INSERT • UPDATE • DELETE • BATCHLOAD Jeff annoys people on twitter @thatjeffsmith
  43. Jeff annoys people on twitter @thatjeffsmith Two Quick AUTOREST TABLE

    Examples 1. GET one or more rows with a query 2. PUT updates to an existing row
  44. • All rows / • One row /:id – PK

    Value – No PK, default to ROWID – Multi-column PK /x,y,z • Some rows /?q={json} SELECT (GET) Jeff annoys people on twitter @thatjeffsmith
  45. METHOD : PUT /:PK REQUEST BODY : JSON RESPONSE: 200

    OK • Location (Header) • JSON (Body) UPDATE (PUT) : adding comments REQUEST BODY Jeff annoys people on twitter @thatjeffsmith RESPONSE
  46. Jeff annoys people on twitter @thatjeffsmith We also support your

    PL/SQL Code! 1. Instant access to all of your application logic 2. ORDS ‘wrappers’ your existing PL/SQL with HTTPS
  47. • POST /ords/hr/procA • JSON Responses & Results (OUTs/RETURNs/REFCURSORs) Accessing

    your PL/SQL Code, Automatically Jeff annoys people on twitter @thatjeffsmith
  48. Remote Procedure Call over HTTP (POST) Jeff annoys people on

    twitter @thatjeffsmith
  49. Remote Procedure Call over HTTP (POST) Request p(arg =>input) Procedure

    output formatted {json} – links and all Jeff annoys people on twitter @thatjeffsmith
  50. RESTful Services, write your own code Jeff annoys people on

    twitter @thatjeffsmith • Modules • Templates • Handlers • Source Types
  51. • Base URI • Default pagination size • Can be

    protected by common privileges Modules Jeff annoys people on twitter @thatjeffsmith
  52. /ords/hr/untappd/beers/ /ords/hr/untappd/beers/:id URI Templates Jeff annoys people on twitter @thatjeffsmith

  53. Handlers: Mapping Verbs to the SQL/PLSQL Jeff annoys people on

    twitter @thatjeffsmith
  54. • Collection Query – Multiple records/paging • Collection Query Item

    – Single record only/no paging • Media – Streams the output directly • PL/SQL – Print HTML (HTP.P) or Code the Response yourself (headers and body) Source Types - Shapes Responses Jeff annoys people on twitter @thatjeffsmith
  55. RESTful Service Example #1 • a TABLE • a RESTful

    Webservice… • …that INSERTs a record Implementation • Template => media/ • Handler => POST • Source => PL/SQL Jeff annoys people on twitter @thatjeffsmith
  56. INSERT a record w/a BLOB Jeff annoys people on twitter

    @thatjeffsmith
  57. • ORDS defines request body & headers w/ :binds •

    ORDS defines response body & headers w/ :binds ORDS makes HTTP & SQL, easy! INSERT INTO table (blob) VALUES (:body); :status := 201; :location := ‘../’ || id; Jeff annoys people on twitter @thatjeffsmith
  58. • We got the BLOB from the HTTP request via

    :body • We got the other columns from HTTP request headers • We INSERT the record • We set HTTP LOCATION and STATUS …in ~3 lines of PL/SQL!!! What just happened? Jeff annoys people on twitter @thatjeffsmith
  59. RESTful Service Example #2 • a TABLE • a RESTful

    Webservice… • …that gives list of links Implementation • Template => beers/:style/pics • Handler => GET • Source => SQL Jeff annoys people on twitter @thatjeffsmith
  60. Print a list of Rows w/LINKs to pictures Jeff annoys

    people on twitter @thatjeffsmith
  61. • SQL SELECT to get the data • Predicate value

    pulled from URI via :type bind • We told ORDS to generate a link with an “$alias” What just happened? Jeff annoys people on twitter @thatjeffsmith
  62. Time to wind things down, Last Call! Jeff annoys people

    on twitter @thatjeffsmith • Source code, CI/CD • Documentation • Authorization
  63. Backup/Source Control your REST Code • GUI • SQL •

    CLI/Liquibase Generated via: Jeff annoys people on twitter @thatjeffsmith
  64. Document your APIs with OpenAPI • GUI • REST Generated

    via: Jeff annoys people on twitter @thatjeffsmith
  65. • See MY post on OAUTH2 w/ORDS • See Todd’s

    post on OAUTH2 w/ORDS & Micronaut Jeff annoys people on twitter @thatjeffsmith SECURE Your Services
  66. • Slides • Blogs • Articles UKOUG Scene Why REST,

    and What’s in it or Me? Oracle Magazine AUTO REST & REST Enabled SQL Other Resources Jeff annoys people on twitter @thatjeffsmith