Transcript here is the slide deck

Hidden Gems of APEX
David Gale
Software Engineer
Oracle Application Express
November, 2014
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 2
Oracle Application Express
“I didn’t know APEX could do that”
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 3
DAVID GALE
Software Engineer
Oracle Application Express
Database Tools | Server Technologies
1900 Oracle Way
Reston
Virginia 20190
phone
+1-703-364-2613
mobile
+1-703-268-9040
[email protected]
twitter
@david_s_gale
David Gale has been working
with Oracle Application Express
since 2009 (v. 4.0).
He has been part of the APEX
development team since joining
Oracle in 2011.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 4
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for
information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied upon
in making purchasing decisions. The development, release, and timing of any features or
functionality described for Oracle’s products remains at the sole discretion of Oracle.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 5
Oracle Application Express
Introduction
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 6
Oracle Application Express
•
•
•
•
•
•
Database-centric development tool for Oracle Database application development
Targeted at Web-based developers who know SQL and PL/SQL
Develop Desktop and Mobile applications
100% Browser-based Development and Runtime
Declarative framework for rapid application definition, and full application life cycle.
On premise, on laptop, on cloud, on RAC, any platform Oracle DB is available
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 7
History
Also known as Oracle APEX (a'peks)
HTML DB 1.5
 First Release
HTML DB 1.6
 UI Themes
2004
APEX 3.1
APEX 2.1
 Interactive
Reports
 Oracle XE
APEX 2.2




 Supporting DB Objects
for full app installation
and upgrade
2005
2006
HTML DB 2.0
 SQL Workshop
Development initiated August 1999
2007
APEX 3.0
 Flash Charts
 PDF Printing
 Access Migration
Websheets
Dynamic Actions
Plug-Ins
Team Development
 Mobile
 HTML5
 Packaged Apps
4.2.1
2008
2009
APEX 3.2
 Oracle Forms to
APEX Conversion
2010
APEX 4.2 Dot
Releases
APEX 4.2
APEX 4.0
2011
APEX 4.1
 Data Upload
 Error Handling
 ROWID
2012
 Improved
Packaged Apps
4.2.2, 3, 4
2013
4.2.5
2014 / 5
Application Express 5.0*




Universal Theme
Page Designer
UI Modernization
HTML5 Plugins
*Available in Early Adopter Only (apexea.oracle.com)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 8
Fully supported no-cost feature of the Oracle Database
• Supported as a no-cost feature of the Oracle Database
• No cost feature of database 10gR2 (10.2.0.4 +), 11gR1, 11gR2, 12c
• Any number of developers, applications, any number of end-users
• Specialized Oracle Support Team – Included with Database Maintenance Agreement
• All database editions: EE, SE, SE1, XE
• Exadata, ODA, RAC, Partitioning, Compression, InMemory, Multitenant…
• Distribution
• Distributed with all editions of Oracle database 11g and 12c
• Available as a download from OTN
• Oracle Cloud Services
• “Oracle Database Cloud Service” aka schema since October 2012.
Sold as a fully managed service with 1GB free trial, and 5, 20, 50 GB paid services.
• Available as “development only” customer evaluation service on
http://apex.oracle.com
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 9
Oracle Application Express
Hidden Gem: APEX Data Dictionary
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 10
APEX Data Dictionary
• Set of views of (most) metadata for Workspaces, Applications, etc.
• Accessible through SQL
• Application → Utilities → Application Express Views
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 11
APEX Data Dictionary
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 12
APEX Data Dictionary (Tree View)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 13
Oracle Application Express
Hidden Gem: APEX Advisor
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 14
APEX Advisor
• Automated test suite for common APEX errors and security vulnerabilities
• Application → Utilities → Advisor
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 15
APEX Advisor
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 16
Oracle Application Express
Hidden Gem: Application Search
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 17
Application Search
• Several shortcuts
– <page> - Open page in editor (current application)
– <application>:<page> - Open page in editor
– <application>:s – Application Shared Components (new in 5.0)
• Search prior to opening an application: full workspace search
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 18
Oracle Application Express
Hidden Gem: SQL Workshop Utilities
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 19
SQL Workshop Utilities
• Suite of tools for working with database objects
– Data loading
– DDL generation
– API package creation
– Schema comparison
– …and more…
• SQL Workshop → Utilities
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 20
SQL Workshop Utilities
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 21
Oracle Application Express
Hidden Gem: Query on Selected Table
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 22
Query on Selected Table
• Part of the Find (not search) functionality
• Automatically generates select statement with all column names
• Better than “select *” for usability and maintenance
• Find → Tables
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 23
Query on Selected Table
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 24
Oracle Application Express
Hidden Gem: PL/SQL Column Headings
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 25
PL/SQL Column Headings
• Classic Reports only
• Combine with “SQL Query (PL/SQL function body returning SQL query)”
report type for dynamic reports
• Function returning colon-separated list of column headings
• Can use apex_util.set_report_column_format(…) to specify format masks
at the same time
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 26
PL/SQL Column Headings
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 27
Oracle Application Express
Hidden Gem: Page Items to Submit
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 28
Page Items to Submit
• Automatically copy page item values from DOM to session state
• Attribute of reports and PL/SQL dynamic action steps
• Enables partial page refresh for reports, complex dynamic actions
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 29
Page Items to Submit
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 30
Page Items to Submit
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 31
Oracle Application Express
Hidden Gem: Packaged Applications
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 32
Packaged Applications
• Suite of pre-built applications available for immediate deployment and use
• Two types
– Sample Applications – demonstrate specific functionality
• Can be installed and inspected immediately
– Productivity Applications – designed to meet actual business needs
• Locked upon initial installation
• Fully supported by Oracle*
• Active development
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 33
Packaged Applications
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 34
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 35
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 36