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

Pints with Oracle User Group (POUG) and Oracle ...

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.

thatjeffsmith

March 21, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

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. • 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
  6. • 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 🍻
  7. • Jay-Sun • Not just for js! • Skinnier than

    XML • Flexible • Link-friendly Terms: {JSON} Jeff annoys people on twitter @thatjeffsmith
  8. {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 …
  9. ORDS Features and Architecture Jeff annoys people on twitter @thatjeffsmith

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

    • Management APIs • SQLDev Web Delivery Options Jeff annoys people on twitter @thatjeffsmith
  11. 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
  12. • 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
  13. 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
  14. • JDBC Conn Pools • 1 Pool Per Database •

    ORDS_PUBLIC_USER • Proxy Connects Enabled DB Users ORDS & Database Communication Jeff annoys people on twitter @thatjeffsmith
  15. • 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
  16. 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
  17. 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
  18. • Services defined in schema • Services executed as user

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

    Service Jeff annoys people on twitter @thatjeffsmith
  20. 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
  21. 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
  22. 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!
  23. Filtering, Sorting and More ORDS Handles the SQL for you

    Jeff annoys people on twitter @thatjeffsmith
  24. 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
  25. 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
  26. Home Brewing or Store Bought? Your Code Someone Else’s Jeff

    annoys people on twitter @thatjeffsmith
  27. 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
  28. • 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
  29. • GET • PUT • POST • DELETE • DESC

    • DOCS AutoREST Table /ords/hr/beers/ /ords/hr/beers/:PK Jeff annoys people on twitter @thatjeffsmith
  30. 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
  31. 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
  32. • 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
  33. 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
  34. 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
  35. • POST /ords/hr/procA • JSON Responses & Results (OUTs/RETURNs/REFCURSORs) Accessing

    your PL/SQL Code, Automatically Jeff annoys people on twitter @thatjeffsmith
  36. Remote Procedure Call over HTTP (POST) Request p(arg =>input) Procedure

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

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

    protected by common privileges Modules Jeff annoys people on twitter @thatjeffsmith
  39. • 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
  40. 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
  41. • 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
  42. • 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
  43. 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
  44. Print a list of Rows w/LINKs to pictures Jeff annoys

    people on twitter @thatjeffsmith
  45. • 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
  46. Time to wind things down, Last Call! Jeff annoys people

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

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

    via: Jeff annoys people on twitter @thatjeffsmith
  49. • 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
  50. • 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