Pro Oracle Application
Express
■■■
John Edward Scott and Scott Spendolini
Pro Oracle Application Express
Copyright © 2008 by 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-10 (pbk): 1-59059-827-X
ISBN-13 (pbk): 978-1-59059-827-6
ISBN-13 (electronic): 978-1-4302-0205-9
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Jonathan Gennick
Technical Reviewer: Peter Linsley
Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan
Gennick, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann, Ben Renow-Clarke,
Dominic Shakeshaft, Matt Wade, Tom Welsh
Project Manager: Sofia Marchant
Copy Editor: Marilyn Smith
Associate Production Director: Kari Brooks-Copony
Production Editor: Jill Ellis
Compositor: Pat Christenson
Proofreaders: Linda Seifert and Liz Welch
Indexers: Carol Burbo and Ron Strauss
Artist: April Milne
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail
[email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail
[email protected], or visit http://
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 http://www.apress.com/info/bulksales.
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.
Contents at a Glance
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■CHAPTER 1 Development Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
■CHAPTER 2 Migrating to APEX from Desktop Systems . . . . . . . . . . . . . . . . . . . . . 25
■CHAPTER 3 Authentication and User Management . . . . . . . . . . . . . . . . . . . . . . . . . 65
■CHAPTER 4 Conditions and Authorization Schemes . . . . . . . . . . . . . . . . . . . . . . . 119
■CHAPTER 5 Data Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
■CHAPTER 6 Navigation and Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
■CHAPTER 7 Reports and Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
■CHAPTER 8 Ajax and JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
■CHAPTER 9 File Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
■CHAPTER 10 Reporting and Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
■CHAPTER 11 Themes and Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
■CHAPTER 12 Localization Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
■CHAPTER 13 LDAP and Single Sign-On . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
■CHAPTER 14 Performance and Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
■CHAPTER 15 Production Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
■CHAPTER 16 APEX Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
iii
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■CHAPTER 1 Development Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
APEX Installation Decisions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Application Development Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Users and Administrators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Workspaces and Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Application Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Application Portability and Code Reuse . . . . . . . . . . . . . . . . . . . . . . . . 13
Performance Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Bind Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Report Pagination Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Error and Exception Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Packaged Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
■CHAPTER 2 Migrating to APEX from Desktop Systems . . . . . . . . . . . . . . . . 25
Excel Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Creating a New Application Based on a Spreadsheet . . . . . . . . . . . . 27
Running the New Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Customizing the Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Exporting Data to a Spreadsheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Access Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Using the Access Export Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Using the ODBC Database Export Method . . . . . . . . . . . . . . . . . . . . . 42
Using Oracle Migration Workbench . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Migrating the Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
v
vi ■C O N T E N T S
Migration from Other Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Migrating from a System Using an Oracle Database . . . . . . . . . . . . 57
Migrating from a System Using Another Database . . . . . . . . . . . . . . 57
Migration with SQL Developer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Connecting to a Migration Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Running the Migration Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Verifying the Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
■CHAPTER 3 Authentication and User Management . . . . . . . . . . . . . . . . . . . . 65
Preconfigured Authentication Schemes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Open Door Credentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
No Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Application Express Account Credentials . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Creating New Application Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Creating Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Controlling Authentication with Groups . . . . . . . . . . . . . . . . . . . . . . . . 71
Maintaining Cookie Users Within Your Application . . . . . . . . . . . . . . 76
Database Account Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Custom Authentication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Creating the User Repository. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Creating a New Authentication Scheme . . . . . . . . . . . . . . . . . . . . . . . 83
Regarding Index Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Hash Rather Than Crypt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Implementing Locked User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . 97
Automating User Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Implementing Session Timeouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
■CHAPTER 4 Conditions and Authorization Schemes . . . . . . . . . . . . . . . . . . 119
Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Specifying Condition Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Using Conditions Appropriately . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Authorization Schemes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Creating an Authorization Scheme . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Protecting Your Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
To Cache or Not to Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Resetting the Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
■C O N T E N T S vii
■CHAPTER 5 Data Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
URLs and Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Understanding the URL Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Manipulating the URL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Session State Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Enabling Session State Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Configuring Session State Protection . . . . . . . . . . . . . . . . . . . . . . . . 165
Virtual Private Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Implementing VPD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Using Contexts with VPD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Using Advanced VPD Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
VPD Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Enabling Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Viewing Audit Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
■CHAPTER 6 Navigation and Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Understanding Tab States: Current and Noncurrent . . . . . . . . . . . . 205
Using Standard Tabs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Using Parent Tabs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Navigation Bars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Accessing Navigation Bar Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Creating Navigation Bar Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Performing an Action on the Current Page . . . . . . . . . . . . . . . . . . . . 216
Breadcrumbs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Accessing Breadcrumb Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Using Dynamic Breadcrumb Entries . . . . . . . . . . . . . . . . . . . . . . . . . 220
Displaying Breadcrumbs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Lists. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Accessing List Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Creating a Menu Using a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Tracking Clicks on List Entries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Using User-Defined Attributes for List Entries . . . . . . . . . . . . . . . . . 230
Trees . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Creating a Table for the Tree Entries . . . . . . . . . . . . . . . . . . . . . . . . . 232
Creating the Tree Component . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Enabling and Disabling Tree Entries . . . . . . . . . . . . . . . . . . . . . . . . . 236
viii ■C O N T E N T S
Page Zero . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Creating Page Zero . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Adding Regions to Page Zero . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Positioning Regions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Positioning Page Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Drag-and-Drop Positioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
■CHAPTER 7 Reports and Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Report Headers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Named Columns vs. Generic Columns . . . . . . . . . . . . . . . . . . . . . . . 255
Report Pagination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Break Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Column Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272
Columns As Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276
Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Chart Query Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
HTML Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
SVG Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Flash Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Generic Charting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
■CHAPTER 8 Ajax and JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Implementing an Ajax Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Setting Up the New Search Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Adding JavaScript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Examining the Ajax Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Calling On Demand Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Showing and Hiding Page Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Showing and Hiding Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Showing and Hiding Report Columns . . . . . . . . . . . . . . . . . . . . . . . . 315
Disabling Page Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Setting the Value of Form Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
■C O N T E N T S ix
Implementing Third-Party Ajax Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Using the YUI Library AutoComplete Control . . . . . . . . . . . . . . . . . . 322
Using the YUI Library Tooltip Control . . . . . . . . . . . . . . . . . . . . . . . . . 328
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
■CHAPTER 9 File Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Database or File System? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Using Standard Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Standard Upload Procedure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Standard Download Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Issues with the Standard Procedures . . . . . . . . . . . . . . . . . . . . . . . . 344
Creating Custom Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Custom Upload Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Custom Download Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Security for Download Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Image Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Checking for Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Adding Expiry Headers to the Custom Download Procedure . . . . . 360
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
■CHAPTER 10 Reporting and Printing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Choosing a Print Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Configuring APEX to Use a Print Server . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Printing Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Enabling Printing for a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Troubleshooting Print Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
Configuring Some Simple Print Options . . . . . . . . . . . . . . . . . . . . . . 371
Creating Custom Report Layouts with BI Publisher. . . . . . . . . . . . . . . . . . . 374
Installing the Client-Side Layout Tool . . . . . . . . . . . . . . . . . . . . . . . . 375
Creating a New Report Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Adding Graphics and Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
Generating Reports Through Apache FOP . . . . . . . . . . . . . . . . . . . . . . . . . 389
Installing Apache FOP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
Creating a New Layout Using XSL-FO . . . . . . . . . . . . . . . . . . . . . . . . 390
Adding Graphics to a Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
x ■C O N T E N T S
■CHAPTER 11 Themes and Templates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Themes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Associating a Theme with an Application . . . . . . . . . . . . . . . . . . . . . 403
Viewing Theme Details and Reports . . . . . . . . . . . . . . . . . . . . . . . . . 404
Performing Theme Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
Defining Theme Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Switching Themes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Removing Unused Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Viewing Template Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Understanding Template Types and Classes . . . . . . . . . . . . . . . . . . 415
Managing Template Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Choosing a Template Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Template Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Setting Up a Theme Subscription System. . . . . . . . . . . . . . . . . . . . . 438
Refreshing Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
Tools for Working with Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 442
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
■CHAPTER 12 Localization Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Localizing Application Builder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Choosing a Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Installing a Language File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Localizing Your Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
A Simple Currency Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452
User-Dependent Localization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
NLS Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Fully Translating Your Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460
Defining the Primary Application Language and Derived
From Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
Creating Translated Versions of an Application . . . . . . . . . . . . . . . . 462
Translating On the Fly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Translating the Standard Messages . . . . . . . . . . . . . . . . . . . . . . . . . 475
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 476
■C O N T E N T S xi
■CHAPTER 13 LDAP and Single Sign-On . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
LDAP Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 477
Benefits of Using LDAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479
Centralized User Repository . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 479
Including Other Resources and Attributes. . . . . . . . . . . . . . . . . . . . . 480
Centralized Authentication and Authorization. . . . . . . . . . . . . . . . . . 481
Off-Loading Repository Maintenance and Administration . . . . . . . 482
Authentication with LDAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482
Authenticating with Oracle Internet Directory . . . . . . . . . . . . . . . . . 484
Authenticating with Microsoft Active Directory . . . . . . . . . . . . . . . . 488
Integrating with Legacy LDAP Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
Using the LDAP Username Edit Function . . . . . . . . . . . . . . . . . . . . . 492
Using a Custom LDAP Authentication Function . . . . . . . . . . . . . . . . 494
Working with Groups in OID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Checking Group Membership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Checking Nested Group Membership . . . . . . . . . . . . . . . . . . . . . . . . 501
Checking Groups with MEMBER_OF and MEMBER_OF2 . . . . . . . . 508
Turning Groups into Table Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511
Gaining Efficiency and Resiliency Through Materialized Views . . 515
Working with Groups in Microsoft Active Directory . . . . . . . . . . . . . . . . . 516
Examining Active Directory’s Group Structure . . . . . . . . . . . . . . . . . 518
Checking Group Membership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525
Querying and Updating LDAP Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Querying LDAP Attributes in OID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
Querying LDAP Attributes in Active Directory . . . . . . . . . . . . . . . . . 533
Modifying LDAP Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
Using Single Sign-On . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 542
External Applications vs. Partner Applications . . . . . . . . . . . . . . . . . 543
External Application Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . 543
Partner Application Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 551
xii ■C O N T E N T S
■CHAPTER 14 Performance and Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
Diagnosing Performance Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
Viewing Application Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554
Using Debug Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566
Using SQL Tracing and TKProf . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
Giving Timing Information to the Users . . . . . . . . . . . . . . . . . . . . . . . 574
Making Your Applications More Scalable . . . . . . . . . . . . . . . . . . . . . . . . . . 575
Image Caching Revisited . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
Page and Region Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576
HTTP Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
■CHAPTER 15 Production Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
Managing URLs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
Using a Location Redirect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593
Using Frames . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
Using Apache mod_rewrite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596
Proxying Requests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Backing Up Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
Manual Exports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
Easy Backups the Database Way . . . . . . . . . . . . . . . . . . . . . . . . . . . . 602
Automated Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 606
As-Of Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Migrating Between Environments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
Upgrading Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614
Cloning an Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639
■CHAPTER 16 APEX Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
Accessing the APEX Dictionary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 641
Using the Application Builder Interface . . . . . . . . . . . . . . . . . . . . . . . 641
Using the apex_dictionary View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647
Uses for the APEX Dictionary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651
Quality Assurance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652
Self-Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660
Automated Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
■C O N T E N T S xiii
Using the API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
Adding Items to Your Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
Creating Text Fields Programmatically . . . . . . . . . . . . . . . . . . . . . . . 677
Generating Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680
A Final Warning! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 681
■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683
Foreword
I consider myself a pragmatic person—one who uses the right tools for a job and employs
the most straightforward and easy way to accomplish a task. To that end, I’ve been a great sup-
porter and fan of Oracle’s Application Express (APEX) from before the day it was introduced. I
say “before the day” because I’ve had the honor and pleasure of using APEX long before it was
released to the public at large. My web site, http://asktom.oracle.com/, is one of the first ever
built with the software that was to become known as APEX.
APEX is one of the most pragmatic database development tools I know of. It does one thing
and one thing well: it rapidly implements fully functional database applications—applications
that are used to predominantly access, display, and modify information stored in the database
(you know, the important applications out there). It facilitates using the database and its fea-
ture set to the fullest, allowing you to implement some rather complex applications with as
little work (code) as possible. It is possible to build extremely scalable applications with a huge
user base (http://metalink.oracle.com/, for example, is built with APEX). It is possible to build
extremely functional applications, with seriously powerful user interfaces (APEX itself is writ-
ten in APEX, as proof of this). It is easy to build applications rapidly. For example, the current
version of http://asktom.oracle.com was developed in a matter of days by two developers—in
their spare time; it was not a full-time job.
While it all sounds wonderful and easy so far, APEX is a rather sophisticated tool with many
bits of functionality and a large degree of control over how the generated application will look
and feel. To fully utilize the power of APEX, you need to have a guide and a mentor, to show you
how to do so, very much akin to what I do with people regarding the Oracle database.
This book, Pro Oracle Application Express, is that guide. The authors, Scott Spendolini and
John Scott, are those mentors. The book walks you through the steps you need to understand
after you’ve installed and started using APEX, to go beyond the sample applications. Covering
diverse topics such as using the database features to full advantage (one of my favorite topics),
to SQL injection attacks (what they are and how to avoid them in APEX), to printing, you’ll find
many real-world issues you will be faced with explained, demystified, and solved in this book.
For example, Chapter 5 “Data Security,” covers a wide breadth of topics regarding securing
your database application. There is a section on URL injection issues that discusses what they
are, how they are exploited, why you care about them, and how to protect yourself from them.
There is a section on session state protection that follows the same format: what it is, how it is
exploited, why you care, and how to protect yourself. The same mentoring occurs with data-
level access, where the authors introduce how to use Virtual Private Database, a core database
feature (not really an APEX feature) to protect your data from unauthorized access. Lastly, a
critical application feature, auditing, is discussed in depth using the same “what it is, why it is,
why you care, and then how to do it” approach. While some of the content in this chapter is not
specific to APEX, it is needed to give you a holistic view to building database applications,
which is what this book is about.
This book covers not just the nitty-gritty details of building a secure application, but also
covers all you need to know to build database applications with APEX. When they are finished xv
xvi ■F O R E W O R D
with security, the authors move on to other necessary topics, such as how to perform screen
layout and application screen navigation, how to integrate reports and charts, how to integrate
web services—enabling you to perform application integration—in an APEX environment, and
much more.
If you are an APEX developer just starting out, or an APEX developer with experience under
your belt and want to learn more about the environment you are using, this book is for you. It
describes from start to finish how to build secure, functional, scalable applications using the
APEX application development environment.
Thomas Kyte
http://asktom.oracle.com/
About the Authors
■JOHN EDWARD SCOTT has been using Oracle since version 7 (around
1993) and has used pretty much every release since then. He has had the
good fortune to work on a wide range of projects for a varied group of
clients. He was lucky enough to start working with Oracle Application
Express when it was first publicly released, and has worked with it nearly
every day since (and loves it).
John is an Oracle ACE and was named Application Express
Devel-oper of the Year 2006 by Oracle Magazine. He is also the
cofounder of ApexEvangelists (http://www.apex-evangelists.com),
a company that specializes in providing training, development, and
consulting specifically for the Oracle Application Express product. You can contact John at
[email protected].
■SCOTT SPENDOLINI has been using Oracle since version 7.3 (around
1996) and has also used pretty much every version since then on a
number of different projects.
From 1996 until 2005, Scott was employed at Oracle Corporation
in the greater Washington, DC area. For the first few years, he was a
sales consultant who focused on the Oracle E-Business Suite. Around
2002, he changed jobs and became a senior product manager for
Oracle Application Express. For the next three and a half years, he
worked with the Application Express development team in designing
features of the product, as well as with Oracle customers, helping them
to get started with Oracle Application Express.
In October 2005, Scott decided to start his own company, Sumner Technologies, LLC, and
focus on Oracle Application Express training and consulting. Since then, he has worked with a
number of different clients on a wide variety of products, each one as different and challenging
as the next. He has also presented on the benefits and technical aspects of Application Express
at Oregon Development Tools User Group events, Independent Oracle User Group events,
Oracle OpenWorld, APEXposed, and a number of smaller user group conferences.
Currently, Scott resides in Ashburn, Virginia, with his wife Shannon and two children,
Isabella and Owen.
xvii
About the Technical Reviewer
■PETER LINSLEY discovered the wondrous virtues of Application Express while employed at
Oracle in 2004. He remains a steadfast advocate of Application Express for rapid development
of enterprise applications and is yet to be impressed by similar offerings. Peter currently works
at Google Inc. in California.
xix
Acknowledgments
I would like to thank many people for helping me complete this book. I have the good fortune
to know many people in the “APEX world” and can freely bounce ideas around with them. Most
notably, I would like to thank Dimitri Gielis for being an excellent friend and an excellent devel-
oper. His enthusiasm for Application Express development is contagious.
I would like to thank Tyler Muth in relation to the LDAP chapter. I corresponded with Tyler
when I found that some legacy code I had for working with LDAP was similar to some code he
had. While I genuinely cannot remember where the inspiration for that code came from, it
stands more than a fleeting chance that it was due to something I saw from Tyler many years
ago (before I even knew him). So Tyler, thank you for sharing your work.
Tim Hall, who runs the Oracle-Base web site (http://www.oracle-base.com/), also deserves
a mention. I frequently refer to Tim’s site for reference material. While it is not directly related
to this book, I have certainly used his site to refresh my memory for some of the examples.
I would also like to thank Scott Spendolini for helping with this book by contributing a
chapter. Scott is one of the most knowledgeable APEX developers around, and his experience
has definitely added to the quality of this book.
I would also like to thank the Oracle team behind Application Express, including Mike
Hichwa, Joel Kallman, Carl Backstrom, David Peake, and many others (sorry I can’t name you
all, but you know who you are), for not only creating such a great product, but also being so
approachable to end users, answering questions and responding to comments.
Also deserving of a mention are all the people in the OTN APEX Forum, who helped me
to discover that I really do enjoy challenges when replying to questions. The OTN forums are a great
source of information, and I use them just as much to find answers as I do to answer questions.
Finally, most importantly, I’d like to thank my family for the incredible support over the
years. My parents for helping me to get to where I am now in life; I hope I’ve made them proud.
My wife Pamela for being understanding about how much time I sit in front of a glowing screen.
Without her years of love and support, I wouldn’t be where I am now. Thank you, Pamela.
Oh, and also, a final mention of our cat. Without her sleeping by my feet each day when I
was writing the book, the days would have seemed so much longer and less furry.
John Edward Scott
F irst off, I’d like to thank John Scott for asking me to help with this book. John is a brilliant
APEX developer, and his knowledge of the tool is perhaps surpassed only by his willingness to
help others learn it, as evidenced by his frequent postings in the OTN forums.
I’d be remiss if I also did not mention the Oracle APEX developers for initially giving me
the opportunity to work with such a talented team and then continuing to support me as I
launched my own company. I simply would not be where I am today if it were not for them.
I’d also like to thank my family, particularly my wife Shannon, who would tend to the kids
while I was in the office after-hours trying to finish my chapter.
Scott Spendolini
xxi
Preface
T he inspiration for the material in this book comes from my experience developing Oracle
Application Express applications and working with the Oracle database for many years. I use
the products every day, and each day I find new or better ways of doing things.
There was no way I could cover everything in a single book. However, I hope that this
book provides a “checklist” of the most common scenarios that people encounter when
developing applications with Application Express. Unfortunately, due to time and page
constraints, sometimes I could not go into as much detail as I would like. I hope the reader
can forgive me for that. And where I might not go into detail in one area, I try to make sure
I go into sufficient detail in others.
I also have the pleasure of knowing Scott Spendolini and asked him to contribute a chapter
to the book. Since his own experiences complement my own, the book is all the richer for
Scott’s contribution.
John Edward Scott
xxiii
CHAPTER 1
■■■
Development Best Practices
O racle Application Express (APEX) makes it extremely easy to quickly prototype and develop
a web application. However, as a software developer, you should be aware that speed of devel-
opment is only one of a number of criteria that will contribute to the perceived success (or
failure) of your project.
The perception of the project success can vary depending on viewpoint. For example, a
typical project might be viewed by developers, testers, managers, production support, and end
users. The developers may feel like the project was a success because they developed the appli-
cation quickly, Production support may feel like the project was a failure because no one has a
clear strategy on how to perform application upgrades. The end users may dread using the
application because it runs incredibly slowly. Clearly, for the project to be considered a suc-
cess, you need to satisfy the expectations of all these people (or as many as you reasonably
can). Ideally, you should strive for an application that has the following characteristics:
• Easy to develop
• Easy to deploy and upgrade
• Easy to maintain and debug
• Enjoyable for end users to use
• Fast enough for the users’ requirements
• Stable from the end users’ perspective
• Secure enough to protect your data from unauthorized access
You should never end up feeling like developing, deploying, maintaining, or (even worse)
using the application is seen as a chore. Each of these areas can often benefit from the adoption
of some best practices to ensure that all the people who will be involved with it see your appli-
cation as a success.
Chapter 1 is the best place to introduce and discuss best-practice techniques, since they
should form the foundation of every significant development you undertake. You can certainly
create applications without using any of the techniques mentioned in this chapter, but adopt-
ing techniques like these will make your job as a developer easier, and your applications will be
considerably more successful.
1
Copyright by webtailieu.net