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. 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.
  10. • 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.
  11. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. SQL Developer Web Demo…LATER
  12. 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
  13. HTTP VERB IDE for your REST Modules TEMPLATE Database Code

    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  14. 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
  15. 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.
  16. • 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.
  17. Copyright © 2022, Oracle and/or its affiliates | All Rights

    Reserved. The Converged Database • JSON • XML • Spatial • ML & Analytics • All SQL/PLSQL
  18. DBA. Me. Want. Stuff! • Provide access, easily to your

    DBs • Automation • Monitoring Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  19. 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.
  20. 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.
  21. • Product Info & Downloads Basic & Complete • YouTube

    Overview • Blog Posts • ORDS on Twitter Resources Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
  22. 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.