Oracle REST Data Services: POUG Edition

An overview of ORDS for building RESTful Web Services and your Oracle Database with BEER examples!

Thanks and credit to the POUG organization for making this possible.

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 P ints with O racle U ser G roup 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. O racle R EST D ata S ervices Included for free with your Oracle Database!!! oracle.com/rest

6. RE presentational S tate T ransfer 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 JDBC connection pool 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 Transform s 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 Privileges Resources Step 2. REST Enable Schema Jeff annoys people on twitter @thatjeffsmith Services constrained by Database User:

20. AUTO REST RESTful Services REST => SQL 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 GET one or more rows with a query 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! Instant access to all of your application logic ORDS ‘wrappers’ your existing PL/SQL with HTTPS Breathe new life into your apps!

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) Simply enable the Program 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 Packaging related services Jeff annoys people on twitter @thatjeffsmith

52. /ords/ hr / untappd / beers/ /ords/ hr / untappd / beers/:id URI Templates Addressing our Entities Jeff annoys people on twitter @thatjeffsmith

53. Handlers: Mapping Verbs to the SQL/PLSQL Jeff annoys people on twitter @thatjeffsmith The code behind a service

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