Beginning Oracle Application Express 4.2

433 Pages·2013·20.12 MB·English

by  Doug Gault

Beginning Oracle
Application Express 4.2
Doug Gault
Karen Cannell
Patrick Cimolini
Martin D’Souza
Timothy St. Hilaire
Apress·
Beginning Oracle Application Express 4.2
Copyright © 2013 by Doug Gault, Karen Cannell, Patrick Cimolini, Martin D’Souza, Timothy St. Hilaire
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the m aterial
is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting,
reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval,
electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed.
Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material
supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the
purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the
Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from
Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are
liable to prosecution under the respective Copyright Law.
ISBN 978-1-4302-5734-9
ISBN978-1-4302-5735-6(eBook)
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion
and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified
as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication, neither
the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may
be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewer: Warren Capps
Editorial Board: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel,
Jonathan Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham,
Matthew Moodie, Jeff Olson, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft,
Gwenan Spearing, Matt Wade, Tom Welsh
Coordinating Editor: Christine Ricketts
Copy Editor: Tiffany Taylor
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected], or visit
www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer
Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.
For information on translations, please e-mail [email protected], or visit www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook
versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook
Licensing web page at www.apress.com/bulk-sales.
Any source code or other supplementary materials referenced by the author in this text is available to readers
at www.apress.com. For detailed information about how to locate your book’s source code, go to
www.apress.com/source-code/.
To those in search of knowledge and better understanding I dedicate this effort.
Hopefully, as your skills grow, you too will continue to share the wealth.
—Doug Gault
To Gary and Kali, who tolerate my brain on APEX.
—Karen Cannell
I would like to dedicate this book to all those who have worked in computer
technology and taken the time to share their experiences and knowledge.
Only by teaching each other do we truly grow.
—Tim St. Hilaire
Contents at a Glance
About the Authors ..............................................................................................................xix
About the Technical Reviewer ...........................................................................................xxi
Acknowledgments ...........................................................................................................xxiii
(cid:78) Chapter 1: An Introduction to APEX 4.2 .............................................................................1
(cid:78) Chapter 2: A Developer’s Overview ...................................................................................7
(cid:78) Chapter 3: Identifying the Problem and Designing the Solution ......................................29
(cid:78) Chapter 4: SQL Workshop ................................................................................................37
(cid:78) Chapter 5: Applications and Navigation ..........................................................................55
(cid:78) Chapter 6: Forms and Reports—The Basics ....................................................................97
(cid:78) Chapter 7: Forms and Reports—Advanced ...................................................................153
(cid:78) Chapter 8: Programmatic Elements ..............................................................................203
(cid:78) Chapter 9: Security ........................................................................................................235
(cid:78) Chapter 10: Application Bundling and Deployment .......................................................263
(cid:78) Chapter 11: Understanding Websheets .........................................................................283
(cid:78) Chapter 12: A Websheet Example ..................................................................................311
(cid:78) Chapter 13: Extended Developer Tools ..........................................................................329
(cid:78) Chapter 14: Managing Workspaces ...............................................................................351
(cid:78) Chapter 15: Team Development .....................................................................................369
(cid:78) Chapter 16: Dynamic Actions ........................................................................................397
Index .................................................................................................................................413
v
Contents
About the Authors ..............................................................................................................xix
About the Technical Reviewer ...........................................................................................xxi
Acknowledgments ...........................................................................................................xxiii
(cid:78) Chapter 1: An Introduction to APEX 4.2 .............................................................................1
What Is APEX? ...............................................................................................................................1
A Brief History of APEX ..................................................................................................................2
Ancient History ......................................................................................................................................................2
More Recent History ..............................................................................................................................................2
APEX 4 and the Future ...........................................................................................................................................3
What You Need to Get Started .......................................................................................................4
Access to an APEX Instance ..................................................................................................................................4
Web Browser .........................................................................................................................................................5
SQL Developer .......................................................................................................................................................5
Summary .......................................................................................................................................5
(cid:78) Chapter 2: A Developer’s Overview ...................................................................................7
The Anatomy of a Workspace ........................................................................................................7
APEX Users ............................................................................................................................................................8
Applications, Pages, Regions, and Items ...............................................................................................................8
Workspaces, Applications, and Schemas ..............................................................................................................9
A Final Word on Workspaces ...............................................................................................................................11
A Tour of the APEX Modules ........................................................................................................11
The Home Page ...................................................................................................................................................13
Application Builder ..............................................................................................................................................15
vii
(cid:78) CONTENTS
SQL Workshop .....................................................................................................................................................17
Administration and Team Development ...............................................................................................................28
Summary .....................................................................................................................................28
(cid:78) Chapter 3: Identifying the Problem and Designing the Solution ......................................29
Identifying System Requirements ...............................................................................................29
Never a Clean Slate .............................................................................................................................................29
A Broken System .................................................................................................................................................30
How Do You Fix Things? .......................................................................................................................................30
System Design with APEX in Mind ..............................................................................................32
Table Definition and User Interface Defaults .......................................................................................................32
APEX and Primary Keys .......................................................................................................................................33
Business Logic vs. User Interface Logic ..............................................................................................................33
Placement of Database Objects ...........................................................................................................................34
Translating Theory to Practice .....................................................................................................34
Summary .....................................................................................................................................35
(cid:78) Chapter 4: SQL Workshop ................................................................................................37
Creating Objects with the Object Browser ..................................................................................37
Loading Data with the Data Workshop Utility ..............................................................................43
Creating a Lookup Table ..............................................................................................................47
Loading and Running SQL Scripts ...............................................................................................49
User Interface Defaults ................................................................................................................51
Understanding User Interface Defaults ................................................................................................................52
Defining UI Defaults for Tables ............................................................................................................................52
Summary .....................................................................................................................................54
(cid:78) Chapter 5: Applications and Navigation ..........................................................................55
The Create Application Wizard .....................................................................................................55
Sample and Packaged Applications ....................................................................................................................56
Websheet Applications ........................................................................................................................................58
Database Applications from Spreadsheets ..........................................................................................................59
Database Applications from Scratch ...................................................................................................................59
viii
(cid:78) CONTENTS
HTML Regions .............................................................................................................................68
Public Pages ................................................................................................................................71
Navigation Bar Entries .................................................................................................................73
Global Pages ................................................................................................................................76
Breadcrumb Regions ...................................................................................................................78
Breadcrumb Entries ....................................................................................................................82
Lists .............................................................................................................................................83
List Regions .................................................................................................................................87
Template Positions ......................................................................................................................90
Lists of Values .............................................................................................................................91
Static List of Values .............................................................................................................................................92
Dynamic List of Values.........................................................................................................................................93
Summary .....................................................................................................................................95
(cid:78) Chapter 6: Forms and Reports—The Basics ....................................................................97
APEX Forms .................................................................................................................................97
Form on a Table ...........................................................................................................................99
Creating a Form on a Table ..................................................................................................................................99
Modifying a Form on a Table .............................................................................................................................104
Looking Behind the Scenes ...............................................................................................................................108
Form on a Procedure .................................................................................................................110
Creating a Form on a Procedure ........................................................................................................................110
Modifying a Form on a Procedure .....................................................................................................................112
Looking Behind the Scenes ...............................................................................................................................113
Master-Detail Report and Form .................................................................................................114
Creating a Master-Detail Report and Form ........................................................................................................114
Modifying a Master-Detail Report ......................................................................................................................118
Session State ............................................................................................................................124
Understanding Session State ............................................................................................................................125
Sharing Database Connections ..........................................................................................................................125
ix
(cid:78) CONTENTS
Setting and Retrieving Session State ................................................................................................................126
Viewing Session State .......................................................................................................................................126
APEX Items ................................................................................................................................128
Page vs. Application Items.................................................................................................................................128
The Importance of Bind Variables ......................................................................................................................129
Built-In Items .....................................................................................................................................................129
APEX URL Syntax .......................................................................................................................130
Searchable APEX Reports ..........................................................................................................131
Creating a Searchable APEX Report...................................................................................................................131
Adding Reset Pagination....................................................................................................................................133
Looking Behind the Scenes—APEX Report .......................................................................................................134
Looking Behind the Scenes—APEX Master-Detail Forms .................................................................................135
More on APEX Forms .................................................................................................................137
Item Layout ........................................................................................................................................................137
Placing Multiple Items in the Same Row ...........................................................................................................139
Implementing LOVs ............................................................................................................................................140
Starting a New Grid ...........................................................................................................................................143
Master-Detail Cleanup .......................................................................................................................................144
APEX Help ..................................................................................................................................146
Adding a Help Text Region .................................................................................................................................146
Seeding Help Text ..............................................................................................................................................147
Declarative BLOBs .....................................................................................................................148
Summary ...................................................................................................................................151
(cid:78) Chapter 7: Forms and Reports—Advanced ...................................................................153
Tabular Forms ...........................................................................................................................153
Creating a Tabular Form ....................................................................................................................................153
Modifying a Tabular Form ..................................................................................................................................156
Looking Behind the Scenes ...............................................................................................................................159
Interactive Reports ....................................................................................................................159
Creating an Interactive Report ...........................................................................................................................160
Running an Interactive Report ...........................................................................................................................162
x
(cid:78) CONTENTS
Restricting Functionality by Report ...................................................................................................................164
Restricting Functionality by Column ..................................................................................................................165
Using the Column Heading Menu ......................................................................................................................165
Searching by Column .........................................................................................................................................166
Selecting Columns .............................................................................................................................................169
Filtering .............................................................................................................................................................169
Sorting ...............................................................................................................................................................171
Adding Breaks ...................................................................................................................................................172
Highlighting .......................................................................................................................................................172
Computing Columns ..........................................................................................................................................173
Adding Aggregates ............................................................................................................................................174
Adding Charts to Interactive Reports .................................................................................................................174
Grouping ............................................................................................................................................................175
Using Flashback ................................................................................................................................................176
Saving an Interactive Report .............................................................................................................................176
Resetting an Interactive Report .........................................................................................................................178
Getting Help .......................................................................................................................................................178
Adding a Subscription .......................................................................................................................................179
Downloading ......................................................................................................................................................179
Modifying an Interactive Report ........................................................................................................................181
Looking Behind the Scenes ...............................................................................................................................187
Calendars ..................................................................................................................................188
Understanding Calendar Types ..........................................................................................................................188
Creating a Calendar ...........................................................................................................................................188
Looking Behind the Scenes ...............................................................................................................................194
Charts ........................................................................................................................................195
Writing Queries for Charts .................................................................................................................................196
Creating a Chart .................................................................................................................................................196
Filtering Data Using a Chart ...............................................................................................................................199
Looking Behind the Scenes ...............................................................................................................................201
Summary ...................................................................................................................................202
xi