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.

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 [email protected] 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.