Advanced APEX

1
Advanced APEX

• Michael Hichwa
• Vice President, Database Tools
• Oracle

2
The preceding 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
Oracles products remain at the sole discretion
of Oracle.
3
Agenda

• Data Model Design
• APEX Application Design
• Packaging
• Performance
• Source Code Control
• http//www.oracle.com/technology/products/database
  /application_express/pdf/apex_best_practices.pdf

4
Data Model Design

• Use Referential Integrity, use on delete cascade
  when needed
• Not null constraints, Check constraints, dont
  rely on application to enforce data integrity
  always name constraints
• Use Mainstream datatypes
• Object Naming
• Columns singular
• Tables plural
• Standard prefixes

5
Data Model Design

• Single Column Primary Key, name all ID
• Use Packages and not stand-alone stored
  procedures and functions
• Standard Columns
• created_by, created_on,
• updated_by, updated_on
• Use Triggers to Maintain PK
• Comments all tables and Columns
• Use Bulk Collect in PL/SQL

6
Trigger Example

• CREATE OR REPLACE TRIGGER EBA_BIU_ASSET_CATEGORIE
  S
• BEFORE INSERT OR UPDATE ON EBA_ASSET_CATEGORIES
• FOR EACH ROW
• BEGIN
• IF INSERTING THEN
• IF NEW.ID IS NULL THEN
• SELECT to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXX
  XXXXXXXXXXXXX')
• INTO NEW.ID FROM DUAL
• END IF
• NEW.CREATED_ON SYSDATE
• NEW.CREATED_BY nvl(v('APP_USER'),USER)
• END IF
• IF UPDATING THEN
• NEW.UPDATED_ON SYSDATE
• NEW.UPDATED_BY nvl(v('APP_USER'),USER)
• END IF
• END
• /

7
Application Design Before You Start

• Define Top Scenarios
• End User Functionality
• Application Administration
• Choose Navigation Style
• Tabs
• Breadcrumbs
• Wizards
• AJAX
• Lists (menus)
• Home Page
• Themes use corporate standard, or pick one

8
Application Design Examples
Icon based home page design
9
Application Design Examples
Project Based Home Page with Simple Chart
10
Application Design Best Practices

• Use Shared Components
• Use Shared LOVs
• Use Shared Authorization Schemes
• Buttons
• Use Native Buttons
• Use Redirect to URL when possible
• Keep Home Page Simple and Fast
• Use simple or no-region templates for report
  regions
• Separate Report Query Parameters from non-query
  related buttons using Search Region

11
Application Design Examples
Distinguish Search Parameters from Action Buttons
12
Application Design Examples
APEX 3.0 Uses Icons to Show Page Function (find
the home page)
13
Application Design Examples
Use Shared Components
14
Application Design Best Practices

• Attractive artwork with matching colours
• Consistency
• Report Columns
• Button Labels, button positions
• Breadcrumbs / Page Names / Region Names
• Item Labels, label position, help text,
  validations
• Error messages
• No data found messages
• Region templates
• Scroll controls

15
Application Design Best Practices

• Stub out Application First
• Create empty pages
• Layout Tabs
• Build Navigation Pages
• Build a Few Representative Pages
• Understand Key Flows and Usage Patterns
• Top Use Cases
• Understand User Browser Competence
• Account for Browser Versions
• Usage Frequency monthly, weekly, daily
• Segment Application
• Use Page Groups
• Page Ranges

16
Application Design Best Practices

• Use Wizards
• Reports with Forms Wizard
• Report
• From on a Table
• Layout Data Entry Forms
• Use Button Bar Region for all Form Buttons
• Group Similar Form Fields Using Regions
• Text Areas use Above Label Positions and colspans
• Page Zero
• Logic on Page Zero Displays on Every Page
• Show Standard Footers things you dont want in a
  page template
• Use Lists to Implement Tabs

17
Application Design Best Practices

• Validations
• Provide short and meaningful error messages
• Branches
• Create as Few Branches as Possible
• Check the include success message option

18
Application Design Examples
Example of images that have too much detail
19
Application Design Examples
Another Simple Application with Icon Based Tasks
20
Application Design End Game

• Item and Page Level Help
• Gather End User Feedback
• Watch Users Use Your Application
• Do Formal Usability Studies
• Baseline common scenarios
• Retest new versions
• Review at usability
• Compliance (508)
• Globalization

21
Application Design Security

• Hacker Proofing
• Cross site scripting
• SQL Injection
• URL Tampering
• APEX Controls
• Authentication
• Authorization
• Session State Protection
• Application Items
• Use restricted may only be set from browser

22
Application Design Examples
In Report Column Definitions Escape Special
Characters
23
Application Design Examples
Application Item, Restricted Access (not from URL)
24
Application Design Examples
Setting Session State Protection Enable, then
set per page
25
Packaging
26
Application Design - Packaging

• Build Options
• Allow components or modules to be turned on or
  off
• Installation Scripts
• Create tables, indexes, packages
• Populate static data and seed tables
• Populate sample data scripts
• Deinstallation Scripts
• Drop all objects used by application
• Use cascade constraints when dropping tables

27
Application Design - Packaging

• Upgrade Scripts
• Alter Tables
• Recreate Packages, Procedures, and Functions
• New Indexes
• New with APEX 3.0
• Documentation
• Overview Purpose
• Installation Instructions
• Single file install
• Image installation option
• Build option choices

28
Performance
29
Performance Tips Application Developement

• Use Bind Variables, do not use substitutions in
  SQL or PL/SQL
• Dont Over-Use Page 0 and Application Processes
• Use Declarative Conditions, not PL/SQL conditions
  validations
• Images on file system or in XDB
• Keep Page Sizes Reasonable (view page size)
• Use Images Appropriately, Review Image Sizes
• Move Larger Processes PL/SQL Regions to PL/SQL
  Procedures
• Limit use of Shortcuts and Application Items
• Use PPR Reporting Templates

30
Performance Tips System Settings

• SGA Sized Correctly
• Gather Statistics on APEX
• Limit Resources
• Ensure image directory is cached
• One database running per OS
• Apache configuration

31
Performance Tools

• Run pages in Debug Mode
• Reduce page rendering time to lt 0.2 Seconds
• PL/SQL Profiler
• TKPROF
• Monitor page performance
• APEX_ views
• Weighted page performance
• Determine peek pages per second
• Oracle Magazine Article
• http//www.oracle.com/technology/oramag/oracle/07-
  jan/o17browser.html

32
Source Code Control
33
Source Code Control

• Export Application
• Manually
• APEX App Export
• SQL Developer 1.2.1
• Automated
• Shell Script
• Pull only changed applications
• Use splitter
• Recovery
• Export as of, uses flashback
• Pull source from source code control
• Copy Applications (0.8, 0.9, 1.0, 1.1)

34
Q

A
35
(No Transcript)