Professional Oracle Programming

793 Pages·2005·23.799 MB·English

by  Rick Greenwald, Robert Stackowiak, Gary Dodge, David Klein, Ben Shapiro, Christopher G. Chelliah

01_574825 ffirs.qxd 5/13/05 12:52 PM Page iii
Professional Oracle® Programming
Rick Greenwald, Robert Stackowiak, Gary Dodge, David Klein, Ben Shapiro,
Christopher G. Chelliah
01_574825 ffirs.qxd 5/13/05 12:52 PM Page ii
01_574825 ffirs.qxd 5/13/05 12:52 PM Page i
Professional Oracle® Programming
01_574825 ffirs.qxd 5/13/05 12:52 PM Page ii
01_574825 ffirs.qxd 5/13/05 12:52 PM Page iii
Professional Oracle® Programming
Rick Greenwald, Robert Stackowiak, Gary Dodge, David Klein, Ben Shapiro,
Christopher G. Chelliah
01_574825 ffirs.qxd 5/13/05 12:52 PM Page iv
Copyright © 2005 by Wiley Publishing Inc. All rights reserved.
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
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 Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permis-
sion of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright
Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA01923, [phone], 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 ACCURACYOR COMPLETENESS
OF THE CONTENTS OF THIS WORK AND SPECIFICALLYDISCLAIM ALLWARRANTIES, INCLUDING
WITHOUT LIMITATION WARRANTIES OF FITNESS FOR APARTICULAR PURPOSE. NO WARRANTY
MAYBE CREATED OR EXTENDED BYSALES OR PROMOTIONALMATERIALS. THE ADVICE AND
STRATEGIES CONTAINED HEREIN MAYNOT BE SUITABLE FOR EVERYSITUATION. THIS WORK IS
SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING
LEGAL, ACCOUNTING, OR OTHER PROFESSIONALSERVICES. IF PROFESSIONALASSISTANCE IS
REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT.
NEITHER THE PUBLISHER NOT THE AUTHOR SHALLBE LIABLE FOR DAMAGES ARISING HERE-
FROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS A
CITATION AND/OR APOTENTIALSOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT
THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR
WEBSITE MAYPROVIDE OR RECOMMENDATIONS IT MAYMAKE. FURTHER, READERS SHOULD BE
AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAP-
PEARED 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 Publishing logo, Wrox, the Wrox logo, and Programmer to Programmer are
trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. All other trademarks
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.
Library of Congress Cataloging-in-Publication Data:
Professional Oracle programming / Rick Greenwald ... [et al.].
p. cm.
Includes indexes.
ISBN-13: 978-0-7645-7482-5 (paper/website)
ISBN-10: 0-7645-7482-5 (paper/website)
1. Oracle (Computer file) 2. Relational databases. I. Greenwald, Rick.
QA76.9.D3P76646 2005
005.75'85--dc22
2005010511
ISBN 13: 978-076-457482-5
ISBN 10: 0-7645-7482-5
Printed in the United States of America
10 9 8 7 6 5 4 3 2 1
1B/QS/QW/QV/IN
01_574825 ffirs.qxd 5/13/05 12:52 PM Page v
About the Authors
Rick Greenwaldhas been in the technology industry for over 20 years and is the author of 12 previous
books, most of them on Oracle. He has been involved with development and databases for his entire
career, including stops at Data General, Cognos, Gupta Technologies, and his current employer, Oracle.
Computers and computing are a sideline for Rick — his real job is father to his three wonderful girls,
with a primary hobby of music appreciation.
Robert Stackowiakis Senior Director of Business Intelligence (BI) in Oracle’s Technology Business Unit.
He is recognized worldwide for his expertise in business intelligence and data warehousing in leading
the North American BI team at Oracle. His background includes over 20 years in IT related roles at
Oracle, IBM, Harris Computer Systems, and the U.S. Army Corps of Engineers including management
of technical teams, software development, sales and sales consulting, systems engineering, and business
development.
Gary Dodge has been focused on database technology since his first COBOLprogramming job with IMS
DB/DC in 1976. He joined Oracle Corporation in 1987 and has served in various management and
technical positions within both the sales and consulting divisions. He has been a frequent speaker on
database topics at many local and national information technology conferences. In addition to several
magazine articles, he is co-author (with Tim Gorman) of Oracle8 Data Warehousingand Essential Oracle8i
Data Warehousing, both published by John Wiley & Sons.
David Kleinhas been in the technology industry for over 20 years with a variety of companies, including
Data General, Wang Laboratories, Gupta Technologies, Oracle, and a few consulting services companies.
He has had many roles, including management of application development and database design teams,
sales and sales consulting, systems engineering and marketing. Recently, he has focused on developing
classroom and online training courses. An active wife and two boys and a 200-year-old house take up any
free time.
Ben Shapirois the president of ObjectArts Inc., a New York City-based technology consulting company,
and has been designing database systems with Oracle since 1997. ObjectArts has worked with many
large corporate clients developing XML-based publishing tools and web-based applications. Before
ObjectArts, Ben worked with several NYC-based startup companies as a technical lead building content
management software.
Christopher G. Chelliahjoined Oracle as a Consultant in 1995. He brings with him project management,
architecture, and development experience from a number of large, multinational sites in the mining, oil
and gas, and telecom industries. Chris has been involved with emerging and database technologies for
his entire career and is an accomplished software architect with a flair for business development. His
expertise has been actively sought by a number of major Oracle clients in Australia, Europe, and the
United States. Chris, his wife and two kids are currently in Singapore, where he leads a team defining
and executing on innovative E-Government strategies for Oracle’s Public Sector industry in Asia Pacific.
01_574825 ffirs.qxd 5/13/05 12:52 PM Page vi
01_574825 ffirs.qxd 5/13/05 12:52 PM Page vii
Credits
Vice President and Executive Group Publisher Development Editor
Richard Swadley Sharon Nash
Vice President and Publisher Production Editor
Joseph B. Wikert Felicia Robinson
Executive Editor Technical Editors
Robert Elliott Michael Ault, Tim Gorman,
Wiley-Dreamtech India Pvt Ltd
Editorial Manager
Mary Beth Wakefield Text Design & Composition
Wiley Composition Services
Senior Production Editor
Geraldine Fahey