Pro Oracle Application Express 4

724 Pages·2011·42.4 MB·English

by  Tim Fox

Pro Oracle Application
Express 4
Second Edition
(cid:3)
(cid:3)
(cid:3)
(cid:3)
(cid:3)
(cid:3)
(cid:2) (cid:2) (cid:2)(cid:3)
Tim Fox
John Edward Scott
Scott Spendolini
Pro Oracle Application Express 4, Second Edition
Copyright © 2011 by Tim Fox, John Edward Scott, and Scott Spendolini
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-3494-4
ISBN-13 (electronic): 978-1-4302-3495-1
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.
President and Publisher: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewer: Alex Fatkulin
Editorial Board: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick,
Jonathan Hassell, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey
Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade,
Tom Welsh
Coordinating Editor: Jessica Belanger
Copy Editor: Kim Burton-Weisman
Compositor: Bytheway Publishing Services
Indexer: BIM Indexing & Proofreading Services
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-ny@springer-
sbm.com, or visit www.springeronline.com.
For information on translations, please e-mail [e-mail], 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.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be caused
directly or indirectly by the information contained in this work.
I would like to dedicate this effort to my father, Louis Fox,
for introducing me to software development by bringing home a TRS-80 when I was 14.
—Tim Fox
Contents at a Glance
(cid:2) About the Authors.................................................................................................xv
(cid:2) About the Technical Reviewer............................................................................xvii
(cid:2) Acknowledgments.............................................................................................xviii
(cid:2) Foreword .............................................................................................................xix
(cid:2) Introduction.........................................................................................................xxi
(cid:2) Chapter 1: Development Best Practices.................................................................1
(cid:2) Chapter 2: Authentication and User Management...............................................41
(cid:2) Chapter 3: Conditions and Authorization Schemes ............................................101
(cid:2) Chapter 4: Data Security.....................................................................................137
(cid:2) Chapter 5: Navigation and Layout.......................................................................187
(cid:2) Chapter 6: Reports and Charts............................................................................241
(cid:2) Chapter 7: Dynamic Actions...............................................................................299
(cid:2) Chapter 8: Websheets.........................................................................................331
(cid:2) Chapter 9: Reporting and Printing......................................................................367
(cid:2) Chapter 10: Themes and Templates ...................................................................409
(cid:2) Chapter 11: Localization Issues..........................................................................455
(cid:2) Chapter 12: Plug-ins...........................................................................................495
(cid:2) Chapter 13: Web Services...................................................................................527
(cid:2) Chapter 14: Performance and Scalability...........................................................559
(cid:2) Chapter 15: Production Issues............................................................................599
(cid:2) Chapter 16: APEX Dictionary...............................................................................647
(cid:2) Index...................................................................................................................689
iv
Contents
(cid:2) About the Authors.................................................................................................xv
(cid:2) About the Technical Reviewer............................................................................xvii
(cid:2) Acknowledgments.............................................................................................xviii
(cid:2) Foreword .............................................................................................................xix
(cid:2) Introduction.........................................................................................................xxi
(cid:2) Chapter 1: Development Best Practices.................................................................1
APEX Installation Decisions...............................................................................................2
Application Development Considerations..........................................................................2

Users and Administrators.........................................................................................................................2

Workspaces and Schemas.......................................................................................................................3

Application Deployment............................................................................................................................9

Application Portability and Code Reuse..................................................................................................15
Performance Considerations...........................................................................................22

Bind Variables.........................................................................................................................................23

Report Pagination Style..........................................................................................................................24

Error and Exception Handling.................................................................................................................24

Packaged Code.......................................................................................................................................25
Team Development..........................................................................................................26

Using Team Development.......................................................................................................................26
New Development Features in APEX 4.0.........................................................................36
Summary.........................................................................................................................39
v
(cid:2) CONTENTS
(cid:2) Chapter 2: Authentication and User Management...............................................41
Preconfigured Authentication Schemes..........................................................................41
Open Door Credentials.....................................................................................................44
No Authentication............................................................................................................44
Application Express Account Credentials........................................................................45

Creating New Application Users.............................................................................................................45

Creating Groups......................................................................................................................................47

Controlling Authentication with Groups..................................................................................................48

Maintaining Cookie Users Within Your Application.................................................................................53
Database Account Authentication....................................................................................56
Custom Authentication....................................................................................................59

Creating an Application from a Spreadsheet..........................................................................................59

Running the Application.........................................................................................................................64

Creating the User Repository..................................................................................................................64

Creating a New Authentication Scheme.................................................................................................66

Regarding Index Usage...........................................................................................................................71

Hash Rather Than Crypt..........................................................................................................................74
Implementing Locked User Accounts..............................................................................80

Understanding the Login Process...........................................................................................................82

Modifying the Notification Message.......................................................................................................85

A Note About Session Management.......................................................................................................89
Automating User Registration..........................................................................................90

Modifying the User Repository...............................................................................................................91

Modifying the Authentication Method.....................................................................................................92

Creating a Registration Form..................................................................................................................93

Adding a Verification Link Table.............................................................................................................94

Adding a User Registration Procedure....................................................................................................94
vi
(cid:2) CONTENTS

Adding the Procedure to Send the Verification.......................................................................................96

Handling the Verification Link.................................................................................................................98
Managing Session Timeouts............................................................................................99
Summary.......................................................................................................................100
(cid:2) Chapter 3: Conditions and Authorization Schemes ............................................101
Specifying Condition Types...........................................................................................101

No Condition.........................................................................................................................................102

Exists (SQL Query Returns at Least One Row)......................................................................................102

NOT Exists (SQL Query Returns No Rows)............................................................................................106

SQL Expression.....................................................................................................................................107

PL/SQL Expression................................................................................................................................108

PL/SQL Function Body Returning a Boolean.........................................................................................111

Request = Expression 1........................................................................................................................111

Request != Expression 1......................................................................................................................115

Request Is Contained Within Expression 1...........................................................................................115

Value of Item in Expression 1 = Expression 2......................................................................................117

Value of Item in Expression 1 Is NULL..................................................................................................118

Current Page = Expression 1................................................................................................................119

Current Page Is Contained Within Expression 1 (Comma Delimited List of Pages)..............................120

User Is Authenticated (Not Public)........................................................................................................121

User Is the Public User (User Has Not Authenticated)..........................................................................121

Current Language Is Contained Within Expression 1............................................................................122

Never....................................................................................................................................................123
Using Conditions Appropriately......................................................................................123
Authorization Schemes..................................................................................................126

Creating an Authorization Scheme.......................................................................................................126

Protecting Your Resources...................................................................................................................130

To Cache or Not to Cache.....................................................................................................................132
vii
(cid:2) CONTENTS

Resetting the Caching..........................................................................................................................133
Summary.......................................................................................................................135
(cid:2) Chapter 4: Data Security.....................................................................................137
URLs and Security..........................................................................................................137

Understanding the URL Syntax.............................................................................................................137

Manipulating the URL...........................................................................................................................139
Session State Protection................................................................................................147

Enabling Session State Protection........................................................................................................148

Configuring Session State Protection...................................................................................................149
Virtual Private Database................................................................................................154

Implementing VPD................................................................................................................................155

Using Contexts with VPD......................................................................................................................164

Using Advanced VPD Features..............................................................................................................171

VPD Best Practices...............................................................................................................................179
Auditing.........................................................................................................................179

Enabling Auditing..................................................................................................................................180

Viewing Audit Data...............................................................................................................................181
Summary.......................................................................................................................185
(cid:2) Chapter 5: Navigation and Layout.......................................................................187
Tabs...............................................................................................................................187

Understanding Tab States: Current and Noncurrent.............................................................................189

Using Standard Tabs............................................................................................................................190

Using Parent Tabs.................................................................................................................................193
Navigation Bars.............................................................................................................197

Accessing Navigation Bar Entries.........................................................................................................197

Creating Navigation Bar Entries............................................................................................................200

Performing an Action on the Current Page...........................................................................................204
viii
(cid:2) CONTENTS
Breadcrumbs.................................................................................................................206

Accessing Breadcrumb Entries............................................................................................................206

Using Dynamic Breadcrumb Entries.....................................................................................................210

Displaying Breadcrumbs......................................................................................................................211
Lists...............................................................................................................................213

Accessing List Entries..........................................................................................................................213

Creating a Menu Using a List................................................................................................................217

Tracking Clicks on List Entries.............................................................................................................220

Using User-Defined Attributes for List Entries......................................................................................221
Trees..............................................................................................................................223

Creating a Table for the Tree Entries....................................................................................................223

Creating the Tree Component...............................................................................................................225

Enabling and Disabling Tree Entries.....................................................................................................227
Page Zero.......................................................................................................................229

Creating Page Zero...............................................................................................................................229

Adding Regions to Page Zero...............................................................................................................231
Layout............................................................................................................................233

Positioning Regions..............................................................................................................................233

Positioning Page Items.........................................................................................................................236

Drag-and-Drop Positioning...................................................................................................................238
Summary.......................................................................................................................239
(cid:2) Chapter 6: Reports and Charts............................................................................241
Reports..........................................................................................................................241

Report Headers.....................................................................................................................................245

Named Columns vs. Generic Columns..................................................................................................250

Report Pagination.................................................................................................................................261

Break Formatting..................................................................................................................................265

Column Formatting...............................................................................................................................269
ix
(cid:2) CONTENTS

Columns as Links.................................................................................................................................273
Charts............................................................................................................................274

Chart Query Types................................................................................................................................275

HTML Charts.........................................................................................................................................277

Flash Charts..........................................................................................................................................283

Generic Charting...................................................................................................................................293
Summary.......................................................................................................................297
(cid:2) Chapter 7: Dynamic Actions...............................................................................299
Implementing a Dynamic Search...................................................................................300
Calling On Demand Processes.......................................................................................307

Understanding Your Choices................................................................................................................307
Showing and Hiding Page Elements..............................................................................310

Showing and Hiding Fields Using Advanced Dynamic Actions.............................................................311

Showing and Hiding Items Using Standard Dynamic Actions...............................................................317

Ajax within APEX...................................................................................................................................325
Summary.......................................................................................................................330
(cid:2) Chapter 8: Websheets.........................................................................................331
Preparing for Websheet Development...........................................................................331
Building a Websheet Application...................................................................................334
Running a Websheet Application...................................................................................339

Logging In.............................................................................................................................................339

Building a Data Grid..............................................................................................................................341

Using the Buglist Websheet..................................................................................................................353

Contributing Content to a Websheet.....................................................................................................361
Summary.......................................................................................................................365
(cid:2) Chapter 9: Reporting and Printing......................................................................367
Choosing a Print Server.................................................................................................367
x