Oracle's Free RAD Stack: Build Applications Rapidly with Oracle Database 18c XE, APEX, ORDS, and SQL Developer

1. July 18th, 2019 Alfredo Abate Senior Oracle Systems Architect Brake Parts Inc @HeyAlfredoDBA Ready to be RAD? No Problem with Oracle's Free Rapid Application Development (RAD) Stack!

2. About Me • Oracle DBA since 1999 (7.3.4 – 12cR1) • Oracle Apps DBA since 2006 (11i/R12) • President of the Chicago Oracle Users Group (COUG) • President of the Real Application Cluster Special Interest Group (RAC SIG) • Oracle ACE • My Oracle blog www.heyalfredo.com • Twitter @HeyAlfredoDBA

4. Oracle Rapid Application Development (RAD) StackFitting the Pieces TogetherExamplesSummary Agenda

5. Not this RAD ! A unit of absorbed radiation dose

6. More Like This RAD Awesome! COOL! EXCELLENT!

7. “Being rad is like being cool without having to work for it”

8. John Developer: "We're awesome developers!"

9. "No we're not, we're better! We're #$@!*&% RAD developers!" Jane Developer:

10. RAPID APPLICATION DEVELOPMENT • James Martin • Iterative Development • Prototyping

11. Oracle Rapid Application Development (RAD) Stack (*As defined by Oracle - https://apex.oracle.com/en/platform/architecture/)

12. RAD Stack Excludes One Component REST Data Services (ORDS) APEX Database SQL Developer

13. Oracle Database is the foundation of RAD

14. Introducing Oracle Database 18c XE After 7 long years, 18c version of Oracle Database Express Edition (XE) available!

15. Oracle Database 18c Express Edition (XE) • Linux – RPM (5 minute install/config!) • Windows • Docker • VirtualBox

16. Oracle Application Express (APEX) • Only need a web browser to develop • Low code application development • Easy to learn with built-in wizard driven tools • Deploy something that is secureand scalable

17. Oracle Rest Data Services (ORDS) Image Source (https://www.oracle.com/database/technologies/appdev/rest.html)

18. SQL Developer • GUI SQL*Plus • Manage Database Objects • Run SQL Statements • Edit/Debug PL/SQL • Create and view reports • Manage REST Data Services • Develop RESTful Services • 3rd Party DB Connectors

19. Best of all!

20. Oracle Rapid Application Development (RAD) StackFitting the Pieces TogetherExamplesSummary Agenda

21. Fitting the Pieces Together

22. ORDS – APEX - DATABASE

23. Oracle Database 18c XE: Where to use? Development/sandbox environments Web Developers using “local” instances Proof of concept projects Non-profit or Education Vendors can embed and redistribute for free

24. Oracle Database 18c XE Key Features • Multitenant: Get isolation, agility, and economies of scale by managing multiple Pluggable Databases • In-Memory:Support real-time analytics, business intelligence, and reports by keeping your important data in the Oracle Database In-Memory column store • Partitioning: Enhance performance, availability, and manageability of your database with data partitioning • Advanced Analytics: Data Mining SQL, R programming, and the Oracle Data Miner UI • Advanced Security: Oracle Transparent Data Encryption and Data Redaction

25. Oracle Database 18c XE: Pluggable Database (PDB) What is a pluggable database? (Image Source https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/introduction-to-the-multitenant-architecture.html)

26. Oracle Database 18c XE: Pluggable Database (PDB) 18c XE allows up to 3 pluggable databases

27. Oracle Database 18c XE: Pluggable Database (PDB) The ability to plug and unplug a PDB from one XE environment to another.

28. Oracle Database 18c XE: Pluggable Database (PDB) Create a new pluggable database and test upgrade of APEX to the latest version!

29. Create a “hot” pluggable database SQL> CREATE PLUGGABLE DATABASE xepdb2 FROM xepdb1 2 FILE_NAME_CONVERT = ('C:\app\Oracle\product\18.0.0\oradata\XE\XEPDB1', 'C:\app\Oracle\product\18.0.0\oradata\XE\XEPDB2') 3 STORAGE (MAXSIZE 2G) 4 SERVICE_NAME_CONVERT = ('xepdb1','xepdb2'); PLUGGABLE DATABASE CREATED

30. Oracle Database 18c XE: Pluggable Database (PDB) With new pluggable database test upgrade of APEX to the latest version! PDB2 Upgrade to APEX 19.1

31. Two APEX Versions Running Simultaneously!

32. Oracle Database 18c XE Annual release going forward No security patches/updates Oracle Application Express no longer installed by default!

33. Oracle Database 18c XE: License

34. Oracle Application Express (APEX) • Low code development framework in the Oracle database • Fully supported and no additional licensing costs to use APEX • Runs in the database so can scale with the database • Works with Exadata, Real Application Clusters (RAC), and In-Memory option • Built in wizards that allow rapid development of applications

35. APEX Web Listener Options Recommended • Oracle REST Data Services (ORDS) • Oracle REST Data Services Listener is a Java-based Web server. It features file system caching, offers improved file upload capability, and is certified with Oracle WebLogic Server, Apache Tomcat, or Standalone (built-in Jetty). • Oracle HTTP Server and mod_plsql (Deprecated) • Oracle HTTP Server uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database. • Embedded PL/SQL Gateway (Legacy) • Oracle XML DB Protocol Server with the Embedded PL/SQL Gateway installs with Oracle Database. It provides the database with a Web server and the necessary infrastructure to create dynamic applications.

36. ORDS Supported Java EE Application Servers • WebLogic - Best of class but licensing can be expensive. • Apache Tomcat – Leader in open source web application server. • Standalone- Uses built in web server called Jetty

37. What can you do with APEX? • Create data entry or lookup forms • Create interactive reports with the ability to drill down, filter, and create charts • Create beautiful and intuitive dashboards • Eliminate Excel spreadsheets and Access databases! • Convert them to APEX so they can be easily accessible by everyone, secured and backed up. A true single source of truth!

38. Drowning in Spreadsheets? APEX is the Spreadsheet Killer!

39. Oracle Rest Data Services (ORDS) Not just your APEX Listener!

40. Oracle Rest Data Services (ORDS) REpresentational State Transfer GET POST PUT DELETE

41. Oracle Rest Data Services (ORDS)

42. Manage REST Data Services Install Oracle Rest Data Services (ORDS) Manage Rest Connections

43. REST Enable a Schema in SQL Developer This example is using the HR schema

44. REST Enable a Schema in SQL Developer Enable in Production!

45. REST Enable a Schema in SQL Developer

46. REST Enable a Schema - SQL DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'personnel', p_auto_rest_auth => FALSE); commit; END;

47. Rest Enable a Table in SQL Developer

48. Rest Enable a Table in SQL Developer Enable in Production!

49. Rest Enable a Table in SQL Developer

50. Rest Enable a Table – SQL DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN ORDS.ENABLE_OBJECT(p_enabled => TRUE, p_schema => 'HR', p_object => 'JOBS', p_object_type => 'TABLE', p_object_alias => 'positions', p_auto_rest_auth => FALSE); commit; END;