Beginning Oracle Application Express (Wrox Programmer to Programmer)

388 Pages·2008·14.67 MB·English

by  Rick Greenwald

spine=.768"
Wrox Programmer to ProgrammerTM Wrox Programmer to ProgrammerTM
Beginning
Greenwald
Oracle
®
Application Express
Even if you have limited programming experience, this What you will learn from this book Beginning
hands-on guide will get you up and running quickly with ● Steps to ensure that users enter correct data while using your
Oracle Application Express (APEX). It covers the concepts and
applications AO
practical steps involved in building a web application from the
● The powerful and flexible reports you can create with Oracle r
ground up using Oracle APEX. You’ll discover how to progress pa
APEX
through the complete development process with the help of c
● Advanced options for customizing user interfaces pl
detailed instructions on creating, customizing, and extending e
an Oracle database application. ● Techniques for using the power of PL/SQL to extend the logic l ®
i
in your application
Highly respected Oracle expert Rick Greenwald begins by c
● Ways to implement security for your APEX environment
giving you the necessary tools to build a useable order entry a
● The options for deploying an APEX application to another
system. He then explains how to enhance the system with
t
reports, charts, themes, and templates. You’ll also find out environment
i
how to use packaged applications and work with multiple ● How to migrate applications from Microsoft® Access® to o
applications and you’ll learn how to migrate and integrate Oracle Application Express
n
data with the Oracle environment.
Who this book is for
By the end of this book, you’ll have a strong understanding This book is for Access developers looking to step up to a more E
of Oracle APEX and the world of Oracle technology, which will
scalable and reliable environment, with reduced maintenance x
enable you to build your own applications and create further Beginning
overhead and greater integration between application systems.
refinements that meet your reporting needs. p
r
e
Wrox Beginning guides are crafted to make learning programming languages and technologies easier than you think, providing
a structured, tutorial format that will guide you through all the techniques involved. s Oracle
s
®
Application

Express
www.wrox.com
Recommended Database Management ISBN: 978-0-470-38837-2
Computer Book Rick Greenwald
Categories General
$39.99 USA
$43.99 CAN Updates, source code, and Wrox technical support at www.wrox.com
spine=.768"
Programmer to Programmer
™
Get more out of
WROX.com
Interact Chapters on Demand
Take an active role online by participating in Purchase individual book chapters in pdf
our P2P forums format
Wrox Online Library Join the Community
Hundreds of our books are available online Sign up for our free monthly newsletter at
through Books24x7.com newsletter.wrox.com
Wrox Blox Browse

Download short informational pieces and Ready for more Wrox? We have books and
code to keep you up to date and out of e-books available on .NET, SQL Server, Java,
trouble! XML, Visual Basic, C#/ C++, and much more!
Contact Us.
We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing [email protected]
Beginning Oracle® Application Express
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Part I: Introducing Oracle Application Express
Chapter 1: Welcome to Oracle! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Part II: Building an Application with Application Express
Chapter 2: Jumpstart Oracle Application Express . . . . . . . . . . . . . . . . . . . . . 17
Chapter 3: Extending Your APEX Application . . . . . . . . . . . . . . . . . . . . . . . . 53
Chapter 4: Validation, Calculation, Navigation, and Lists . . . . . . . . . . . . . . . . 87
Chapter 5: Reporting and Charting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Chapter 6: Components Working Together . . . . . . . . . . . . . . . . . . . . . . . . . 167
Chapter 7: Customizing User Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Chapter 8: PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Chapter 9: Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Part III: Deploying the Application
Chapter 10: Deployment and Administration . . . . . . . . . . . . . . . . . . . . . . . . 291
Chapter 11: Packaged Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Chapter 12: Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355

88372ffirs.indd 1 11/10/08 9:52:18 PM
88372ffirs.indd 2 11/10/08 9:52:18 PM
Beginning
Oracle® Application Express
Rick Greenwald

88372ffirs.indd 3 11/10/08 9:52:27 PM
Beginning Oracle® Application Express
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-38837-2
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Library of Congress Cataloging-in-Publication Data

Greenwald, Rick.
Beginning Oracle Application express / Rick Greenwald.
p. cm.
Includes index.
ISBN 978-0-470-38837-2 (paper/website)
1. Oracle Application express. 2. Application software--Development. I. Title.
QA76.76.A65G7437 2009
005.1--dc22
2008045553
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of
the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through
payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978)
750-8400, fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley
Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at http://www
.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to
the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation
warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The
advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the
publisher is not engaged in rendering legal, accou nting, or other professional services. If professional assistance is required, the
services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising
herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further
information does not mean that the author or the publisher endorses the information the organization or Web site may provide or
recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or
disappeared between when this work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the
United States at [phone], outside the United States at [phone] or fax [phone].
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are
trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other coun-
tries, and may not be used without written permission. Oracle is a registered trademark of Oracle Corporation. All other trade-
marks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any product or vendor
mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in
electronic books.
88372ffirs.indd 4 11/10/08 9:52:27 PM
Everything I do benefits from the love and support I get from my family — LuAnn, Ellie, Josie, and Robin.
I am truly a lucky guy to be surrounded by such extraordinary people.

88372ffirs.indd 5 11/10/08 9:52:27 PM
About the Author
Rick Greenwald is the author of more than 15 books, primarily focusing on Oracle technology, including
Professional Oracle Programming from WROX and Oracle Essentials from O’Reilly, as well as several books about
some of the ancestors of Oracle Application Express. Rick is a veteran of over 20 years in the IT industry,
as a technologist and evangelist, but his real preoccupations are his family and music.

88372ffirs.indd 6 11/10/08 9:52:27 PM
Credits
Executive Editor Vice President and Executive Group Publisher
Robert M. Elliott Richard Swadley
Development Editor Vice President and Executive Publisher
Ed Connor Joseph B. Wikert
Senior Production Editor Project Coordinator, Cover
Debra Banninger Lynsey Stanford
Copy Editor Compositor
Foxxe Editorial Services Craig Johnson, Happenstance Type-O-Rama
Editorial Manager Proofreader
Mary Beth Wakefield Nancy Carrasco
Production Manager Indexer
Tim Tate Johnna Van Hoose Dinse

88372ffirs.indd 7 11/10/08 9:52:27 PM
Acknowledgments
I would like to thank two people from Oracle Corporation who were the guiding lights of this project:
Judson Althoff, who was the executive sponsor of this book, and whose vision of introducing Oracle
Application Express to a broader audience led to the volume you are holding; and Nick Kritikos, who
not only took that vision and made it a reality, but was kind enough to include me as a partner in the
creation of that reality, and guided the process through the labyrinth that the Oracle Corporation can
sometimes be. Thanks, Judson and Nick.
Certainly, David Peake has been the most important contributor to this book. David is the product
manager for Oracle Application Express, and he has been a constant presence throughout the long pro-
cess that led to the publication of the book. David has been there for technical advice and guidance, as
well as being a sounding board for ideas and direction. In fact, David is nothing less than the “hidden”
coauthor of this book. It has been my real pleasure not only working with David but also coming to
know him as a friend. Thanks, David — without a doubt, this would not have happened without you.
I would also like to call out two people from Wiley who have been instrumental in this book. I will
always remember the first time I spoke to Bob Elliot more than 15 years ago from a pay phone in Hawaii. It
took a decade for that initial introduction to result in a book, and a few more years to get to this one.
Bob has always been there to listen to ideas, guide them in the right direction and offer practical and
accurate advice. You should all be so lucky as to have a friend and colleague like Bob.
Finally, I would like to thank Ed Connor for his stewardship of the project and editing contribution.
This book was not the easiest effort, but Ed brought it through the process with a smooth and steady
hand. Thanks, Ed, hope we get to do this again.

88372ffirs.indd 8 11/10/08 9:52:27 PM