Expert Oracle Application Express (cid:2) (cid:2) (cid:2) (cid:2) (cid:3) (cid:3) (cid:3)(cid:2) John Edward Scott Sharon Kennedy Dietmar Aust Denes Kubicek Martin Giffy D’Souza Raj Mattamal Doug Gault Dan McGhan Dimitri Gielis Francis Mignault Roel Hartman Anton Nielsen Michael Hichwa Expert Oracle Application Express Copyright © 2011 by John Edward Scott, Dietmar Aust, Martin Giffy D’Souza, Doug Gault, Dimitri Gielis, Roel Hartman, Michael Hichwa, Sharon Kennedy, Denes Kubicek, Raj Mattamal, Dan McGhan, Francis Mignault, Anton Nielsen 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 978-1-4302-3512-5 ISBN 978-1-4302-3513-2 (eBook) Printed and bound in the United States of America 9 8 7 6 5 4 3 2 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 Reviewers: Scott Wesley and Lan Tran Faroult 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: Anita Castro Copy Editor: Elizabeth Berry Compositor: Bytheway Publishing Services Indexer: BIM Indexing & Proofreading Services Artist: April Milne 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 (201) 348-4505, e-mail orders-ny@springer- sbm.com, or visit www.springeronline.com. 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. 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. The source code for this book is available to readers at www.apress.com. You will need to answer questions pertaining to this book in order to successfully download the code. This book is dedicated to Carl Backstrom and Scott Spadafore. All author royalties are donated equally to the charity trust funds of their respective families. (cid:3) CONTENTS AT A GLANCE Contents at a Glance (cid:3) Foreword..............................................................................................................xvi (cid:3) About the Authors...............................................................................................xxii (cid:3) About the Technical Reviewers.........................................................................xxvi (cid:3) Acknowledgments..............................................................................................xvii (cid:3) Chapter 1: OHS, EPG, and APEX Listener Compared..............................................1 (cid:3) Chapter 2: Oracle APEX 4.0 Charts Inside Out......................................................73 (cid:3) Chapter 3: Tabular Forms...................................................................................155 (cid:3) Chapter 4: Team Development............................................................................211 (cid:3) Chapter 5: Globalization .....................................................................................241 (cid:3) Chapter 6: Debugging.........................................................................................271 (cid:3) Chapter 7: Dynamic Actions...............................................................................309 (cid:3) Chapter 8: Security.............................................................................................349 (cid:3) Chapter 9: Lifecycle Management......................................................................377 (cid:3) Chapter 10: Working with APEX Collections.......................................................423 (cid:3) Chapter 11: Plug-Ins...........................................................................................441 (cid:3) Chapter 12: Architecture....................................................................................515 (cid:3) Chapter 13: Advanced Interactive Reporting......................................................533 (cid:3) Index...................................................................................................................571 iv (cid:3) CONTENTS Contents (cid:3) Foreword..............................................................................................................xvi (cid:3) About the Authors...............................................................................................xxii (cid:3) About the Technical Reviewers.........................................................................xxvi (cid:3) Acknowledgments............................................................................................xxvii (cid:3) Chapter 1: OHS, EPG, and APEX Listener Compared..............................................1 Why Should I Care About This Chapter?............................................................................1 Web Server Basics.............................................................................................................2 The Oracle HTTP Server (OHS)...........................................................................................3 Installing the OHS.....................................................................................................................................3 Configuring the OHS.................................................................................................................................8 Digging into HTTPD.CONF.......................................................................................................................13 Configuring Virtual Hosts........................................................................................................................16 Prefork or Multi-Processing Module?.....................................................................................................20 Web Server Compression.......................................................................................................................22 Expiry Headers........................................................................................................................................30 Embedded PL/SQL Gateway............................................................................................35 Compression and Expiry Headers with the EPG.....................................................................................38 Configuring SHARED_SERVERS..............................................................................................................39 Configuring EPG Parameters and DADS.................................................................................................42 Debugging Issues with the EPG..............................................................................................................46 Configuring Virtual Hosts with the EPG...................................................................................................48 The APEX Listener............................................................................................................50 Stand-Alone Installation.........................................................................................................................51 v (cid:3) CONTENTS Installing via Glassfish or Web Logic Server...........................................................................................60 Compression with the APEX Listener.....................................................................................................61 Interesting APEX Listener Features........................................................................................................64 Conclusion.......................................................................................................................71 (cid:3) Chapter 2: Oracle APEX 4.0 Charts Inside Out......................................................73 HTML Charts....................................................................................................................74 Using the HTML Charts Wizard...............................................................................................................74 The Result...............................................................................................................................................75 Debugging and Performance..................................................................................................................75 Behind the Scenes..................................................................................................................................76 Inline HTML Charts in Report..................................................................................................................76 Flash Charts.....................................................................................................................78 Background............................................................................................................................................78 Creating a Flash Chart............................................................................................................................79 The Result...............................................................................................................................................84 Understanding the Chart Region.............................................................................................................85 Understanding the Chart Attributes........................................................................................................89 Adding Multiple Series and Combined Charts.................................................................98 Different Flash Chart Types...........................................................................................103 Behind the Scenes.........................................................................................................105 Debug and Performance................................................................................................111 Upgrading Oracle APEX 3.x Flash and SVG Charts.........................................................116 Screen Reader Mode and Charts...................................................................................118 Extending Flash Charts..................................................................................................120 Customizing Charts by Using Custom XML...........................................................................................121 Customizing Charts by Using Custom XML, Dynamic Actions, and JavaScript...................................124 Creating Charts Manually.....................................................................................................................128 vi (cid:3) CONTENTS Drill-Down Charts, Dashboards, and Interactivity..........................................................134 Simple Dashboard with Submit............................................................................................................134 Simple Dashboard with JavaScript......................................................................................................137 Complex Dashboard with Actions.........................................................................................................141 Most Common Issues....................................................................................................150 Search for a Specific Feature...............................................................................................................150 Invalid #HOST# with Reverse Proxy or HTTPS......................................................................................150 Flash Security Error..............................................................................................................................151 Charts in the Future.......................................................................................................151 Resources......................................................................................................................152 Conclusion.....................................................................................................................153 (cid:3) Chapter 3: Tabular Forms...................................................................................155 Changes in APEX 4.0......................................................................................................155 Constraints.....................................................................................................................155 Purpose of Tabular Forms.............................................................................................156 Tabular Forms Are Not Spreadsheets............................................................................157 New Features in APEX 4................................................................................................157 New Item Types....................................................................................................................................157 Declarative Validations.........................................................................................................................166 Other Features......................................................................................................................................169 Future Features We’d Like to See........................................................................................................170 Custom Coding in Tabular Forms...................................................................................170 Processing the Correct Rows...............................................................................................................170 Data Integrity........................................................................................................................................174 Manual Tabular Forms..........................................................................................................................178 Tabular Forms and Collections.............................................................................................................185 Interesting Techniques..................................................................................................199 Simple Autocomplete...........................................................................................................................199 vii (cid:3) CONTENTS Autocomplete Returning Key Value......................................................................................................202 Clone Rows...........................................................................................................................................208 Summary.......................................................................................................................209 (cid:3) Chapter 4: Team Development............................................................................211 Milestones.....................................................................................................................211 The Basics............................................................................................................................................212 Extending Milestone Functionality........................................................................................................214 Features.........................................................................................................................215 The Basics............................................................................................................................................215 Extending the Features Functionality...................................................................................................219 To Do’s...........................................................................................................................220 The Basics............................................................................................................................................220 Extending the To Do Functionality........................................................................................................221 Bugs...............................................................................................................................227 Feedback.......................................................................................................................229 Feedback Process................................................................................................................................229 Enable Feedback..................................................................................................................................230 Exporting Feedback to Development....................................................................................................232 Importing Feedback into Development.................................................................................................233 Processing Feedback...........................................................................................................................233 Exporting Response to Deployment......................................................................................................235 Importing Response into Deployment...................................................................................................235 Extending Feedback: Create a Report..................................................................................................236 Extending Feedback: Feeding Back the Follow Up...............................................................................238 Further Enhancements.........................................................................................................................238 Wrap Up.........................................................................................................................240 (cid:3) Chapter 5: Globalization .....................................................................................241 Loading Languages........................................................................................................241 viii (cid:3) CONTENTS Translating Applications................................................................................................244 Application Language Derived From.....................................................................................................245 Mapping................................................................................................................................................247 CSV Encoding.......................................................................................................................................248 Translating an Application....................................................................................................................249 Manually Translate an Application.......................................................................................................253 Translating Data in the Database..................................................................................255 Dynamic Translations....................................................................................................255 Translating APEX Internal Text......................................................................................257 Copying Translations to Other Environments.................................................................260 Copying Only the Applications..............................................................................................................260 Copy the Primary Application and Publish............................................................................................260 Changing the Application ID.................................................................................................................261 Localization....................................................................................................................261 SINCE Format Mask..............................................................................................................................262 Numeric Formats..................................................................................................................................262 Time Zones and Territories...................................................................................................................263 Switching Languages....................................................................................................265 Translation logs.............................................................................................................267 Developer Log.......................................................................................................................................267 Dashboard............................................................................................................................................268 Conclusion.....................................................................................................................269 (cid:3) Chapter 6: Debugging.........................................................................................271 Principles of Code Instrumentation................................................................................271 Debugging Basics..........................................................................................................272 Page Processing and Rendering...........................................................................................................272 Enabling Debug....................................................................................................................................275 Debug Information................................................................................................................................276 ix (cid:3) CONTENTS Benefits of Debug Mode................................................................................................281 Graphs and Charts................................................................................................................................281 Interactive Reports...............................................................................................................................283 APEX Debugging API......................................................................................................285 APEX_DEBUG_MESSAGE......................................................................................................................286 Programmatic Debugging.....................................................................................................................291 Instrumenting Your Own Code..............................................................................................................294 The Data Behind Debugging..........................................................................................299 Tables and View...................................................................................................................................299 Examining the Debug Data...................................................................................................................301 Using Debug Data for More Than Debugging.......................................................................................303 Debugging Dynamic Actions..........................................................................................303 What Debug Doesn’t Capture.........................................................................................306 AJAX Calls.............................................................................................................................................306 FLASH Charts........................................................................................................................................306 Reports.................................................................................................................................................307 Summary.......................................................................................................................307 (cid:3) Chapter 7: Dynamic Actions...............................................................................309 Custom JavaScript vs. Dynamic Actions.......................................................................309 Manual (Old Method)............................................................................................................................310 Dynamic Action (New Method).............................................................................................................311 Dynamic Actions in Detail..............................................................................................315 Identification.........................................................................................................................................318 When....................................................................................................................................................318 Advanced..............................................................................................................................................322 Actions..................................................................................................................................................323 Dynamic Actions in Action.............................................................................................329 Business Case......................................................................................................................................329 x