Pro Oracle Application.Express P1

Chia sẻ: Vong Phat | Ngày: | Loại File: PDF | Số trang:40

0
112
lượt xem
35
download

Pro Oracle Application.Express P1

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle 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 development is only one of a number of criteria that will contribute to the perceived success (or failure) of your project

Chủ đề:
Lưu

Nội dung Text: Pro Oracle Application.Express P1

  1. Pro Oracle Application Express ■■■ John Edward Scott and Scott Spendolini
  2. 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 orders-ny@springer-sbm.com, 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 info@apress.com, 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.
  3. 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
  4. 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
  5. 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
  6. ■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
  7. 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
  8. ■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
  9. 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
  10. ■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
  11. 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
  12. ■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
  13. 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
  14. 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/
  15. 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 john.scott@apex-evangelists.com. ■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
  16. 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
  17. 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
  18. 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
  19. 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
Đồng bộ tài khoản