Oracle REST Data Service: APEX Office Hours

Slide 1

Slide 1 text

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.

Slide 2

Slide 2 text

Slide 3

Slide 3 text

Slide 4

Slide 4 text

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.

Slide 5

Slide 5 text

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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.

Slide 9

Slide 9 text

1:Many

Slide 10

Slide 10 text

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.

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

No content

Slide 22

Slide 22 text

OpenAPI View

Slide 23

Slide 23 text

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

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.

Slide 32

Slide 32 text

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

Slide 39

Slide 39 text

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.

Slide 40

Slide 40 text

Slide 41

Slide 41 text

Slide 42

Slide 42 text

Service Catalog

Slide 43

Slide 43 text

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.