1

2 Oracle Application Express 5.1Overview [Name] [Title] [Date]

3 Introduction Oracle Application ExpressIntroduction to Application Express to provide some base understanding of what APEX is and how it is architected. Introduction

4 Oracle Application ExpressDatabase-centric web application development framework Used to build desktop and mobile applications for the Oracle Database. Quickly build reports, forms, charts, calendars, etc. on top of the data in your database IT Developers and “citizen-developers” who know a little SQL can readily build applications. Application Express can also take advantage of the large majority of Oracle Database features. Develop desktop and mobile web apps Visualize and maintain database data Leverage SQL Skills and database capabilities

5 Oracle Application ExpressDistinguishing Characteristics Simply use a web browser and the URL to access the development environment, or the URL and user credentials to run the application You don’t need any client software Declarative framework stores application definitions in Oracle database tables within the APEX Engine No need for file-based compilation or code generation All of the data (page) processing is performed by PL/SQL acting directly on the data schemas in the Oracle Database. Therefore, very efficient as data manipulated directly in the database and results sent back as HTML pages Page generation is efficient with only one request and one response. Data processing done in the Database App Development IDE is a web browser. No client software needed App definitions are stored in the database as meta data. Declarative – No code generation

6 Oracle Application ExpressDevelop faster, release more frequently Pre-built controls for security, authentication, database interaction, validation, session management and more… At the core of Application Express is an engine that provides a variety of fundamental application capabilities and operations. Application authentication, page/object-level access control, database interaction (queries/updates etc), form validation, session management and protection, and more is available as standard components that can be utilised from every application without custom development. Application objects such as forms, reports, charts, navigation etc are defined declaratively, enabling applications to be functionally complete in a short period of time, increasing agility in application development. Go from prototype to production in minutes Develop Customize Deliver

7 Oracle Application Express Declarative high productivity App Dev with optional snippet and procedural coding for high control Component Library + Wizards pages, reports, charts, grids, forms Component Properties SQL Analytics, Multimedia Text, Spatial REST Plug-in Development Components & Data Access Page Branching Menu Navigation Lists Component Drill Down Links Modal Dialogs Conditional Flows & Branching Punch-outs and Call Backs Navigation & Page Flow Simple Validations and Computations Conditional Display Logic PL/SQL Snippets PL/SQL Business Logic Authentication Automatic Session Management Authorizations Collections Custom Authentication Access Control & Session Management Drag & Drop Layout Input Fields text area, select list, checkbox, switches, shuttle, radio group Declarative Client Actions JavaScript Snippets 3rd Party JavaScript Programs + Libraries Integration Browser Client Interaction User Interface Themes Icon Library Integration Declarative Styling Declarative Theming CSS Snippets Template and Theme Development HTML & CSS User Interface No Code High Productivity Citizen Developer 4GL meta-data driven development Low Code More Control Power User / Departmental Developer Extend using code snippets and plug-ins Programmatic High Control IT Professional 3GL Procedural Code / Libraries

8 Oracle Application ExpressUse Cases Widely used to rapidly build applications that are needed ASAP to meet changing business requirements and maximize competitive advantage. Primarily designed to be used by all employees / partners / customers rather than back-office systems. Used for extending COTS Software to streamline for your business processes or meet organization specific requirements Webify spreadsheets and MS Access applications to provide true multi-user, secure, robust, “single-source of truth” applications. Also commonly used to modernizing old legacy client-server applications Developing opportunistic & self service web apps Extending enterprise application solutions Migrating file based and client server apps to the web

9 Oracle Application ExpressNo cost feature of the Oracle Database No-cost fully supported feature Any number of apps, developers & end-users Specialized Oracle Support Team 11gR1, 11gR2, 12c All DB editions: EE, SE, SE1, XE Included with Oracle Cloud Services Schema Service Exadata Express Service Database as a Service No cost evaluation Easy to install Included by default with all editions of Oracle database Download latest release from Oracle Application Express is a feature of the Oracle Database with no licensing costs for development or deployment of Application Express applications. The Oracle Database Maintenance Agreement also covers support for Application Express. When you sign up for the ODCS – Schema Service you are provided with a slice of the Oracle Database with Application Express as the development environment (already pre-configured and ready to start developing with) Application Express is released more frequently than the Oracle DB so important to get the latest version of Oracle Application Express from OTN.

10 Oracle Application Express3 Tier Architecture Data Schemas Simple architecture where page requests and submissions made from the browser are tunnelled through the mid-tier to execute in the Oracle Database and returned as HTML responses to the browser. No data manipulation or processing is performed in the mid-tier, instead the APEX engine (inside the Oracle DB) accepts the page, and interacts with the data schemas in the DB. The mid-tier can be either the preferred Oracle REST Data Services (ORDS) running in a Java Server {either Oracle Web-Logic Server (WLS), Oracle Glassfish, or Tomcat} or Oracle Embedded PL/SQL Gateway (EPG) which is part of the Oracle DB, or Oracle HTTP Server (OHS) with mod_plsql. It is common for OHS to be installed in front of ORDS to act as a proxy and serve images. Oracle APEX Engine Oracle Database Browser Mid Tier Database Tier ORDS = Oracle REST Data Services

11 Oracle Application ExpressHistory 1.5 2.0 3.0 4.0 5.0 Originally named HTML DB when first released over 10 years ago. Application Express is released approximately once a year. See Statement of Direction for future direction on OTN under Learn More [http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-sod html] 2.1 3.1 3.2 4.1 4.2 5.1 2.2 Exadata Service Schema Service Exadata Express DBaaS Oracle Database Cloud Services with Application Express 2012 2013 2014 2015 2016 2017

12 Application Express in the Oracle CloudDatabase Cloud Service – Multitenant and Single Tenant Schema / PDB Full Instance Customers who want a “slice” of the database for development and deployment of departmental apps. Don’t want to manage any aspects of the database. Database Schema or PDB available as a monthly Subscription by Size (5, 20, 50, 250*, 500GB*, 1TB*) Oracle Managed Database Highly available and scalable infrastructure Also underpins the BI, Document, Mobile, Java and other Cloud offerings Customers who want a dedicated database for dev/test or production applications. Want control of database while automating many routine tasks. Full database available as a metered service (Hourly or Monthly) Choice of customer or Oracle Managed Database Choice of general purpose or mission-critical infrastructure Choice of editions to meet different customer requirements Schema Servicer Underpins BI Service (APEX) Documents Mobile Cloud Service Developer Service Java SaaS Extension Planning and Budgeting Developer Messaging (just infrastructure DB) Data (just infrastructure DB) Process Integration Social Network Application Builder (formerly known as Breeze) Enterprise Performance Reporting (formerly known as Financial Reporting) Data Enrichment * Planned for a future release.

13 Oracle Database Cloud Services Full Spectrum of Database Cloud Offerings2017* 2017* 2017* Free Schema / Exadata Express Service Standard & Enterprise Managed Enterprise Exadata Managed Editions EE with lockdown EE with Lockdown SE, EE, HP, EP EE, HP, EP EP Implementation 1 Schema 1 Schema / PDB 1 Dedicated DB 1+ Dedicated DBs Management Oracle Customer Max Storage 100MB 5, 20, 50GB, 250GB* 50GB – 4.6TB 42TB – 168TB Max CPU < 1 ~1 1 – 32 4 – 32 28 – 272 Price / month $0 $175 - $2,000 $200 - $5,000 TBD* $70K – $560K More Storage N/A Up to 1TB* $0.50 per GB Availability 2017* Q / 2016 Q3 2014 Q3 2015 Access HTTPs Full Access Nearly Full Access * Future Release

14 Data Sources Utilize the Oracle Database / Web ServicesApplication Express is best suited to being co-located with the data it is maintaining / reporting on. You can also integrate into a SOA environment by consuming Web Services (both REST and SOAP) or utilize DB Links to other databases. If using ORDS you can also emit Web Services from the Oracle Database.

15 Single Database Instance / Multiple WorkspacesEasily managed with optional self-service provisioning Workspaces used to define application definitions / Schemas hold data Many-to-many relationship between Workspaces and Schemas Instance Administrators manage the environment and schema access Departments can request more space, and access to a new schema For example, has over 3,000 Workspaces for every line of business in Oracle There is a many-to-many relationship between workspaces and schemas Within your organization you can have a single Oracle Database support many departments with each having their own workspaces where they build applications Each of these workspaces can be granted access to one or more schemas as appropriate DBAs manage the infrastructure <--> Departments responsible for building their own applications

16 Development / Deployment OptionsLocal Install on stand-alone laptops using Oracle Express Edition (XE) or full database version Simply upgrade APEX to the required version Can work completely disconnected On-Premise Typically run by the IT Department IT generally both production operations service, and a service provider Departments responsible for application development Cloud Deploy Internet applications Leveraged for fast application development, user acceptance and training. Prototyping & Proof-of-Concept Consulting companies develop for deployment on customer premise Given APEX can run ‘anywhere you can install the Oracle Database’ you have great flexibility  Simply provide the appropriate URL to develop / run applications Import an APEX application into any other Oracle Database where you have the same version or later of Application Express installed Start developing on your laptop running Oracle XE or on the cloud and then simply export the application Deploy on the Oracle Database Cloud Service and then once your application gets wide utilization move it to your private cloud. Browser based application development framework enables efficient development & deployment Shoot someone a URL – Check this out – Application Development

17 Oracle Application ExpressCommunity Over 400,00 developers worldwide Estimated from support requests, downloads, conferences, discussion forum activity More than 100 active bloggers Consulting companies, books, success stories, quotes, commercial apps

18 Oracle Application ExpressInternet Sites built with Oracle APEX Showcases large diversity of types of applications and different themes Launched October, 2016 > 100 sites published, more being added daily

19 Oracle Application ExpressCommunity site built by the community for the community Community-authored site providing: Plug-ins Slack discussion channels Twitter feeds News Jobs Conferences Meetups

20 Useful Links Oracle Technology Network http://otn.oracle.com/apexCollateral, Resources and Hosted Services Oracle Technology Network APEX Community Oracle Learning Library Hosted evaluation site APEX Cloud Service These are the primary URLs you need to get more information on Oracle Application Express!

21 Product Components Oracle Application Express SkipOverview of the major APEX Components – What is included in the tool at a high level Product Components

22 Home Page Home page where developers can define applications, maintain their database objects, manage the development lifecycle, and maintain Packaged Apps. Comprehensive Development IDE, Web Application Development, and SQL Database Development

23 Features tight SQL and PL/SQL integrationApplication Builder Developers will spend the majority of their time in the Application Builder defining, enhancing and maintaining applications. Integrated Development Environment (IDE) with Application Development Graphical “Page Designer” Features tight SQL and PL/SQL integration

24 Application Builder - Page DesignerPowerful browser-based IDE Multiple trees to view the page components Visualization of the page within the Grid Layout, and associated Gallery, allowing you to drag and drop components Update multiple attributes without leaving the page in the Property Editor Advanced Code Editor for defining SQL, HTML, CSS and text sections Highly configurable user interface: Customize and refine view of page specific to current focus Slideable panels Drag and drop tabs Page Designer revolutionizes the way developers can enhance and maintain page definitions within Application Express. The left panes has numerous trees to show the rendering, dynamic actions, processes and shared components. The middle pane includes the Grid Layout and Gallery where you can easily drag and drop new components The right pane holds the Property Editor where you can update any of the select4ed components’ attributes, without needing to bring up a separate page For text sections used to enter SQL, HTML, CSS or text you can pop-up the Code Editor which includes find/replace, auto-completion and even SQL validation There are numerous ways you can customize the user interface to make it easier to concentrate on the sections you are currently working on.

25 Property Editor - Right PanelDesign Principles Create new elements using drag & drop or menu shortcuts Update multiple page elements at once Utilize “Undo” and “Redo” to revert changes made “Save” and “Run” commits all pending changes to database Left Panel Rendering, Processing and Shared Components grouped in an accordion in the left panel instead of horizontally across the page Dynamic Actions introduced as a new grouping Create, move and duplicate elements using drag & drop or the improved context sensitive menus All element types can be deleted directly from the tree Click on one or more elements to highlight elements in the Grid Layout and to populate common element details in the Property Editor Central Panel Grid Layout: Create, move, copy and delete components using drag and drop Gallery: shows components that can be dragged into the page Messages: Display errors and warnings associated with changes made Page Search: Allows performing a page search. Help: Displays context sensitive help based on the current focus Property Editor - Right Panel Displays the details for the currently selected element(s) Bulk editing of common properties when multiple elements are selected Improved usability for defining properties, including instant input feedback cascading context sensitive selections easier definition of common property types Improves productivity by making the definition of elements easier and more intuitive

26 Application Builder – Code EditorAvailable for maintaining SQL, PL/SQL, JavaScript, HTML, CSS, and large text sections Syntax Highlighting Undo / Redo Find/Replace Auto completion Code validation Used in Page Designer, Component View and SQL Workshop The Code Editor provides many of the features of advanced editors, such as syntax highlighting, undo and redo, and find / replace functions. However it also includes ‘auto completion’ and ‘code validation’, which obtain valid database object names from the data dictionary of your underlying schema(s).

27 Application Builder - Universal ThemeOverview New default theme for APEX applications Uses responsive HTML5 templates Grid-layout for HTML forms Modern flat-look Ships with a variety of pre-built theme styles Older themes still available as legacy themes, and will remain part of the product for backwards compatibility Rather than having numerous themes with a large number of templates in each, there is just a single desktop theme and a consolidated number of templates. With the Universal Theme it is now quicker and easier to build beautiful, responsive, elegant applications out-of-the-box.

28 Application Builder - Universal ThemeTemplate Options Declarative CSS modifiers for HTML5 templates Enables developers to declaratively apply styles like borders, padding, icons, colors, size, etc. Uses easy-to-understand, descriptive names Available for most template types: Page, Region, Report, Breadcrumb, List, Item, Button Significantly reduces the number of required templates for a theme Rather than having a large array of different templates, and developers needing to define new templates, to meet how a page is displayed - now you can declaratively select different template options to easily meet your layout requirements.

29 Application Builder - Universal ThemeTheme Styles and Theme Roller Theme Styles provide a variety of different color schemes and styles for a single theme Defined as CSS file that is included in addition to the theme’s base CSS file Universal Theme includes several pre-built styles Additional styles can be easily generated using the built-in Theme Roller utility Theme Styles allow a single theme to have various color schemes. Developers can readily define different color schemes and modify certain display characteristics, such as button and item rounding using Theme Roller. Hey can then readily save the style and apply it to their application without needing to write any CSS. Developers can even allow end-users to select different styles as shown in P-Track Administration.

30 Browser based maintenance of database objects and dataSQL Workshop SQL Workshop is sometimes the only means developers can maintain DB objects as they may not have direct access to the schema(s) via SQL*Net, especially if they are using a hosted service. The functionality available through SQL Workshop is a subset of SQL Developer and is designed specifically for developers to be able to maintain their DB objects. Browser based maintenance of database objects and data Designed to meet application developers’ needs, especially in hosted environments

31 Team Development Team Development provides the ability to manage the full lifecycle of your application development. Features, To-Dos, and Bugs can be assigned to specific applications and pages. Feedback allows developers to easily gather comments, enhancements, and bugs directly from their user community and includes important session state information to assist the developers with diagnosis. Team Development is used extensively by the APEX Development Team to manage the development of new releases of Application Express which itself is built with APEX. Collaborate with colleges, track project details with associations to application pages. Life cycle development: design, develop, deploy, view customer feedback, enhance, re-deploy.

32 Packaged ApplicationsRobust suite of productivity and sample applications. Illustrates best practices and design patterns. Template to start customization (e.g. unlock your packaged app). Upgraded with every release. Install with just a few clicks and start using or “unlock” and customize to meet specific needs. When “locked” then fully supported by Oracle Support and can upgrade as new releases made available. Use the apps as learning tools to understand how to implement specific functionality and utilize within your own applications. There are over 30 plug-ins across all of the apps which you can easily use in your own applications.

33 Features Oracle Application Express SkipBreakdown of major feature functions within APEX Features

34 Interactive Reports Powerful Reporting capabilities  End-user customizable Developers define the base report using a SQL statement. Then end-user have can readily manipulate the data displayed to meet their own reporting requirements. They can add filters, computations, aggregations, highlights and breaks. End users can also define charts, group-by and pivot reports based on the data. The Oracle Database option Audit Vault replaced 300 reports with just 30 Interactive Reports, yet provided a huge increase in reporting features.

35 Interactive Grid Modern, rich & interactive multi-row editing component Use of existing Item types Declarative support for Cascading LOVs and Dynamic Actions Upgrade utility for Tabular Forms Master Detail / Detail

36 Interactive Grid Master Detail Layout – N-levels wide or deep

37 Charting Engine Based on Oracle JET Data Visualization ComponentsDeclarative support for Oracle JET Data Visualization Components Based on Oracle JET Data Visualization Components Oracle Jet (JavaScript Extension Toolkit) is open source JavaScript-based solution, data being provided in JSON format Highly Customizable Using ‘JavaScript code’ to provide function to be called when chart is initialized Using Dynamic Actions to add interactive behavior Built-in Accessibility Support Built-in Responsive Design Support

38 Charting Engine Declarative support for Oracle JET Data Visualization Components

39 Calendars Different views, drag and drop capability, with mobile specific templates The built-in Calendar region is based on FullCalendar. You can define duration based events and use drag and drop to update the underlying table directly from the calendar.

40 List-based Navigation MenusAlternative to using traditional tabs Available as top navigation menu and side menu Implemented as standard APEX lists Supports multi-level hierarchical menu structures Provides accessible pull-down menus Type of navigation determined by themes – Universal Theme uses lists Migration of older one-level tabs to lists when switching themes APEX 5 introduces the ability to define Navigation Lists Multi level drop-down menus can be based on either static or dynamic lists.

41 Dynamic Actions Declaratively define rich client-side interactivity without writing JavaScript or AJAX Not every developer is an expert in JavaScript and AJAX, in fact the majority do not have that skillset. However, Dynamic Actions allow anyone to *declaratively* define client side behaviors. Rather than writing numerous lines of JS developers can simply enter conditions, specify the actions, and then select the elements affected. Behind the scenes the APEX engine defines the required JS and AJAX to implement the desired behavior.

42 Modal Dialogs Replacement for Popup WindowsReadily switch between normal, modal, and non-modal page mode Replacement for Popup Windows A modal dialog is a stand-alone page, not a region on a page Any page be created as a dialog page Supports all the functionality of a regular page, incl. computations, validations, processes, and branches Customizable dimensions and positioning Dialog pages can branch to full pages (close dialog and standard branch) or to other dialog pages (e.g. wizard) Dialog pages can open other dialogs (stacked dialogs) Dynamic actions on parent pages created to refresh content after closing of modal dialog form

43 Plug-Ins Extend applications with custom components such as items and regions Plug-ins allow you to readily incorporate custom item types, regions, authentication, authorization, and dynamic actions. There are a number provided by the APEX Development Team, and over 150 developed by developers from the APEX Community.

44 RESTful Web Services Emit RESTful Web Services from the database for use in your SOA environment Interaction with data sources through use of Uniform Resource Identifiers (URIs) Standard method of accessing data in the Oracle Cloud Ability to create services which implement any SQL statement or PL/SQL procedure Data returned in JSON or CSV format, or additionally through PL/SQL For those using Oracle REST Data Services (ORDS) to serve their APEX Pages, then you can also use ORDS to emit Web Services from the Database.

45 Websheets Allow end-users to build / maintain WIKI like pages with database capabilities Database enabled WIKI Annotations (easily add files, links, notes, and tags) Use [[SQL ]] tag Presentation mode Multi user Websheets are similar to WIKIs in that they are designed for end-users to collaborate on content. However, Websheets also includes the ability to define reports, and incorporate SQL that runs on the underlying Database schema(s).

46 Examples of how Oracle uses Oracle APEXSkip Oracle Application Express Application Express is critical to Oracle’s own business practices. The following are some of the examples of how APEX is used by Oracle itself. Examples of how Oracle uses Oracle APEX

47 Oracle Store [Internet Application] The Oracle Store and Partner Store [http://partner.oracle.com] are two great examples of APEX applications running on the Internet. The Oracle Store handles orders for over 150 countries and can be shown in 13 languages. When you choose a country different business rules are invoked within the ordering process. The APEX application interfaces with over 12 major back-office systems such as Manufacturing & Distribution, Marketing, EBS, etc.

48 Oracle Learning Library [Internet Application] The Oracle Learning Library provides a wealth of training collateral for Oracle customers. The application includes an extensive set of administrative screens to allow content owners to upload and maintain various types of content and links.

49 HR Directory [Internal Only – Intranet Application]ARIA People Nightly download of HR data Employees can update profile, upload picture, add links ... Used to contact employees, see Org. Chart, etc. Average ~ 1.5 million page views / day ( > 50 page views / sec for hours ) Median execution time of seconds Arguably one of the most used applications within Oracle – this APEX applications allows any Oracle employee to search the organization to find contacts, etc.

50 Oracle Application Express – Success StoryORACLE TEAM USA Portals and Mobile Solutions “Sailors have access to an Oracle Application Express–based mobile app that automates the 250-item checklist necessary to prep the boat for sailing. “It’s a perfect use of database and mobile technology,” says Burns. “Oracle Application Express is really a powerful tool for widespread mobile data access.” Ian “Fresh” Burns Design Team Coordinator Oracle Team USA Since Oracle first started an America’s Cup team with BMW Oracle Racing right thru to the latest ORACLE TEAM USA which successfully defended the America’s Cup In SF (2104) Application Express has been used extensively by the teams to help manage the large volumes of data, and complex processes, they deal with to race these high performance yachts. html?ssSourceSiteId=otnen

51 Leveraging Oracle Database FeaturesSkip Oracle Application Express Given Application Express lives inside the Oracle Database it can readily utilize other Database features Leveraging Oracle Database Features

52 Oracle Application Express (APEX 5)Security features of the Oracle Database Works with APEX Transparent Data Encryption Database Vault Audit Vault Oracle Database 12c provides multi-layered security spanning preventive, detective, and administrative controls. This includes transparent data encryption, data redaction, data masking, privileged user controls, privilege usage analysis, conditional auditing and real application security. Combined with Oracle Audit Vault and Database Firewall, Oracle Database 12c provides unprecedented controls to help organizations address existing and emerging security and compliance requirements. Database Firewall Key Vault Data Redaction Data Masking Auditing 52

53 Oracle Database as a ServiceHigh Availability features of the Oracle Database Works with APEX Real Application Clusters Data Guard Active Data Guard Golden Gate Secure Backup Online Reorganization Edition-Based Redefinition Flashback, Total Recall Automated Rolling Upgrade of Database Failover to the Cloud 53

54 Oracle Application Express (APEX 5)High Performance features of the Oracle Database Works with APEX Multi version read consistency Row level locking In-Memory Compression Partitioning Hadoop, Big Data SQL, R Cost based query optimizer Scale-out with Oracle RAC Exadata Smartscan & Infiniband 54

55 Oracle Application Express (APEX 5)Simplified consolidation and deployment with Oracle Multitenant Works with APEX PDB Deployment PDB Cloning PDB Self Service App A new feature of Oracle Database 12c, Oracle Multitenant, delivers a new architecture that allows a multitenant container database to hold many pluggable databases (PDBs). An existing database can simply be adopted with no application changes required. PDB Migration High Density Consolidation Automated Provisioning and Configuration PDB Resource Management 55

56 Oracle Application Express (APEX 5)Database Application Development Features Works with APEX SQL and PL/SQL Java in Database Analytics Text Regular Expressions Spatial Pattern Matching Database XML SQL PL/SQL Database JSON AQ 56

57 Oracle Application ExpressClosing Summary on key take-aways Summary

58 Oracle Application ExpressSummary Used by real customers for real applications: Used for both opportunistic and mission critical apps that service tens of thousands of users. Well Established Product : First released in 2004 Continually growing: Oracle install base is adopting Oracle APEX for increasing numbers of projects, and is increasingly an approved corporate IT standard. Used extensively; with a long successful history > 10 years-old Rather than being a tool that grows from the ground-up thru developer word-of-mouth, increasingly Corporate Architects are using APEX as a strategic tool being pushed down to their development communities

59 Oracle Application ExpressSummary Aligned with industry trends: Strong support for HTML5 development frameworks, Modern & responsive CSS3 UI, integrated JavaScript libraries, Browser-based, self-service provisioning, flexible development and deployment including cloud services. Consistent with Oracle developers skill set: SQL, PL/SQL, Oracle Database. Proficiency in the Oracle database easily translates into an ability to develop web applications. Given APEX is released approximately once a year, as new trends arise, APEX can quickly adopt these best (proven) trends Most organizations have sizable teams of developers with SQL / Oracle Database skills. These people can readily start building APEX applications given how easy and quick it is for them to learn how APEX works.

60

61

62