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.

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