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.

    View full-size slide

  2. …you’re running Oracle REST Data Services (ORDS)
    If you’re running APEX…
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  5. • Python Web Apps
    • Spring Boot
    • NodeJS
    • Golang
    • Django…
    REST APIs are popular with
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  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.

    View full-size slide

  11. Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
    SQL Developer Web Demo…LATER

    View full-size slide

  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

    View full-size slide

  13. AUTOREST: TABLE
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  20. OpenAPI View

    View full-size slide

  21. HTTP VERB
    IDE for your REST Modules
    TEMPLATE
    Database Code
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  22. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  29. 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.

    View full-size slide

  30. Secure, Test, & Doc
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  31. • 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.

    View full-size slide

  32. Secure APIS
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

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

    View full-size slide

  34. Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.
    The Converged Database
    • JSON
    • XML
    • Spatial
    • ML & Analytics
    • All SQL/PLSQL

    View full-size slide

  35. SQLDev Web Demo…LIVE, Now ☺
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  36. DBA. Me. Want. Stuff!
    • Provide access, easily to your DBs
    • Automation
    • Monitoring
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  37. 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.

    View full-size slide

  38. DB-API: Turn it on
    true
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

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

    View full-size slide

  40. Service Catalog

    View full-size slide

  41. 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.

    View full-size slide

  42. End of Presentation

    View full-size slide

  43. • Product Info & Downloads
    Basic & Complete
    • YouTube Overview
    • Blog Posts
    • ORDS on Twitter
    Resources
    Copyright © 2022, Oracle and/or its affiliates | All Rights Reserved.

    View full-size slide

  44. 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.

    View full-size slide