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

Oracle REST Data Service: APEX Office Hours

Oracle REST Data Service: APEX Office Hours

From our Webinar on Oracle APEX Office Hours.

Go to www.thatjeffsmith.com to see the Q&A Answers and the video recording from our session.

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

June 17, 2022
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. Oracle REST Data Services Things an APEX Dev Should Know

    Distinguished Product Manager Jeff.D.Smith@oracle.com Tweets: @thatjeffsmith Blogs: https://www.thatjeffsmith.com Jeff Smith Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  2. …you’re running Oracle REST Data Services (ORDS) If you’re running

    APEX… Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  3. APEX/PLSQL Gateway REST APIs SQL Developer Web Database Management SODA

    for REST ORDS…? Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  4. If you build it, they will come “it” = User

    Friendly APIs“they” = Developers APIs come first, making all resources available Developers then build their apps around your APIs Add value for your customers & partners API Driven Development Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  5. • Python Web Apps • Spring Boot • NodeJS •

    Golang • Django… REST APIs are popular with Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  6. • HTTPS is compatible with any: • programming language (even

    Fortran!) • framework (Angular, React.js, jQuery…) • environment (GUIs like Postman and CLI via cURL) • No Oracle Home to install • No Oracle drivers to configure Easier Oracle Access Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  7. • Insert some data? SQL INSERT • Collect statistics? PL/SQL

    DBMS_STATS If you can write SQL, you can build REST APIs! Oracle Speaks SQL & PLSQL Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  8. HTTPS > SQL > HTTPS GET https://host/ords/hr/emps/1 SELECT * FROM

    EMPS WHERE ID = 1 HTTP/1.1 200 OK { "id": 1, "name": "Jeff", "job": "PM", "salary": 100 } URI SQL & PLSQL Marshalls to Database HTTP Request HTTP Response Transforms to JSON SQL Result Set / Standard OUT SQL HTTPS {JSON} Oracle REST Data Service (ORDS) Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  9. 1:Many

  10. ords --config /path/to/config install Enter a number to select additional

    feature(s) to enable: [1] Database Actions (Enables all features) [2] REST Enabled SQL and Database API [3] REST Enabled SQL [4] Database API [5] None Choose [1]: ORDS detects if APEX is installed in the database and if the database user APEX_PUBLIC_USER exists, then it prompts for the location of the APEX images. Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  11. • ORDS is installed automatically, and managed by Oracle •

    Highly Available: Multiple Mid-Tiers with load balancing • Flexible: Optionally install your own Mid-Tier • Web IDE for easy dev, test, & doc Autonomous Experience Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  12. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. SQL Developer Web Demo…LATER
  13. Let’s talk about REST Features Copyright © 2022, Oracle and/or

    its affiliates | All Rights Reserved. AUTOREST •CRUD APIs, no code •Maintained by ORCL •Feature rich •Optimized RESTful Service •Your code - •Inputs, outputs, error handling, response codes, formatting •Full access to SQL/PLSQL •Easily exported, source controlled •Transparent
  14. AUTOREST: TABLE Copyright © 2022, Oracle and/or its affiliates |

    All Rights Reserved.
  15. POST => INSERT Copyright © 2022, Oracle and/or its affiliates

    | All Rights Reserved.
  16. PUT => UPDATE Copyright © 2022, Oracle and/or its affiliates

    | All Rights Reserved.
  17. DELETE => DELETE Copyright © 2022, Oracle and/or its affiliates

    | All Rights Reserved.
  18. POST…BatchLoad Copyright © 2022, Oracle and/or its affiliates | All

    Rights Reserved.
  19. Batchloading 10M rows Copyright © 2022, Oracle and/or its affiliates

    | All Rights Reserved.
  20. GET /?q=… Copyright © 2022, Oracle and/or its affiliates |

    All Rights Reserved.
  21. None
  22. OpenAPI View

  23. HTTP VERB IDE for your REST Modules TEMPLATE Database Code

    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  24. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. Can I, How do I…? • Join tables • Use :binds in URI Templates • Create hierarchies • Generate links • Work with REFCURSORs • Up/Download Files
  25. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. Joins
  26. :binds Copyright © 2022, Oracle and/or its affiliates | All

    Rights Reserved.
  27. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. Hierarchies
  28. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. Generating Links
  29. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. REFCURSORs
  30. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. Uploading files
  31. Using PL/SQL to Download a File CREATE OR REPLACE PROCEDURE

    download_file ( media_id NUMBER ) AS vMIMETYPE VARCHAR2(256); vLENGTH NUMBER; vFILENAME VARCHAR2(2000); vBLOB BLOB; BEGIN SELECT file_name, content_type, content INTO vFILENAME, vMIMETYPE, VBLOB FROM media WHERE id = media_id; vLENGTH := DBMS_LOB.GETLENGTH(vBLOB); owa_util.mime_header(NVL(vMIMETYPE, 'application/octet'), FALSE); htp.p('Content-length: ' || vLENGTH); htp.p('Content-Disposition: attachment; filename=' || SUBSTR(vFILENAME, INSTR(vFILENAME, '/') + 1) || ‘’); owa_util.http_header_close; wpg_docload.download_file(vBLOB); END download_file; / Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  32. Secure, Test, & Doc Copyright © 2022, Oracle and/or its

    affiliates | All Rights Reserved.
  33. • Requests must be authenticated & authorized! • APIs are

    protected via required roles • Authentication can be managed by Web Server OR Use ORDS BASIC Auth Database Auth Built-in OAuth2 Workflow Secure APIS Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  34. Secure APIS Copyright © 2022, Oracle and/or its affiliates |

    All Rights Reserved.
  35. Security Copyright © 2022, Oracle and/or its affiliates | All

    Rights Reserved. OAuth2
  36. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. The Converged Database • JSON • XML • Spatial • ML & Analytics • All SQL/PLSQL
  37. SQLDev Web Demo…LIVE, Now ☺ Copyright © 2022, Oracle and/or

    its affiliates | All Rights Reserved.
  38. DBA. Me. Want. Stuff! • Provide access, easily to your

    DBs • Automation • Monitoring Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  39. DB-API: 600+ Endpoints Performance Monitoring PDB Lifecyle General Data Dictionary

    Reports • tables/, tables/{table} • indexes/… Multitenant Management • Create • Clone • Change State • Drop • Reports Problematic Activity • Sessions • Locks • Waits • Alert Log What isn’t running well? • ASH • AWR • RTSM • Top SQL Database Operations • DBCA • Data Pump Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  40. DB-API: Turn it on <entry key="database.api.enabled">true</entry> Copyright © 2022, Oracle

    and/or its affiliates | All Rights Reserved.
  41. Oracle Docs OpenAPI.JSON Endpoint DB-API: Docs Copyright © 2022, Oracle

    and/or its affiliates | All Rights Reserved.
  42. Service Catalog

  43. Data Pump via HTTPS { "datapump_dir":"DATA_PUMP_DIR", "filter" :"HOCKEY_STATS", "job_mode" :"TABLE",

    "threads" : 2 } Request BODY Response Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  44. End of Presentation

  45. • Product Info & Downloads Basic & Complete • YouTube

    Overview • Blog Posts • ORDS on Twitter Resources Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  46. Modern Tooling Empowers Users, Increases Productivity Use the right tool

    for the right job Desktop REST APIs CLI WEB Copyright © 2021, Oracle and/or its affiliates | All Rights Reserved.