• Oracle™ Unleashed
• Oracle™ Unleashed
• Copyright © 1996 by Sams Publishing
• Overview
• Acknowledgments
• About the Authors
• Introduction
• It's a Database Jim, But Not As We Know It
• SELECT * FROM book
• "But I've used Oracle Before"
• Is this Book for Me?

Oracle™ Unleashed

Oracle™ Unleashed

SAMS Publishing

201 West 103rd Street, Indianapolis, Indiana 46290

Copyright © 1996 by Sams Publishing

FIRST EDITION

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.

All works in chapters 36-48, 54, 56-58 and Appendix E reproduced herein are Copyright [c] Oracle Corporation 1996. All rights reserved. All other chapters are Copyright [c] 1995 by Sams Publishing.

Oracle Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the programs or program segments that are included. This manual is not a publication of Oracle Corporation, nor was it produced in conjunction with Oracle Corporation.

All works in chapters 36-48, 54, 56-58 and Appendix E reproduced herein are Copyright (insert copyright symbol) Oracle Corporation 1996. All rights reserved.

ORACLE is a registered trademark of Oracle Corporation, Redwood Shores, California, U.S.A. Oracle Power Objects is a trademark of Oracle Corporation, Redwood Shores, California, U.S.A.

Oracle Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the programs or program segments that are included. This manual is not a publication of Oracle Corporation, nor was it produced in conjunction with Oracle Corporation.

All other chapters are Copyright (insert copyright symbol) 1996 by Sams Publishing.

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.

International Standard Book Number: 0-672-30872-X

Library of Congress Catalog Card Number: 95-72326

99-98-97-96----------4-3-2-1

Interpretation of the printing code: the rightmost double-digit number is the year of the book's printing; the rightmost single-digit, the number of the book's printing. For example, a printing code of 96-1 shows that the first printing of the book occurred in 1996.

Printed in the United States of America

All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark.

Publisher and President: Richard K. Swadley

Acquisitions Manager: Greg Wiegand

Development Manager: Dean Miller

Managing Editor: Cindy Morrow

Marketing Manager: Gregg Bushyeager

Acquisitions Editor

Rosemarie Graham

Development Editor

Todd Bumbalough

Software Development Specialist

Steve Straiger

Production Editors

Mary Inderstrodt
Kris Simmons

Copy Editors

Margeret Berson
David Bradford
Susan Christophersen
Mary Ann Faughnan
Greg Horman
Christine Prakel
Carla Randolph
Marla Reece
Elaine Sands
Tonya Simpson
Heather Stith
Joe Williams

Technical Reviewers

James Farmer, P. Eng Valcom Ltd.
Jeffrey W. George
Dr. Robert J. Goshen
David Kennedy
Thomas J. Luers
Len Sterrett

Editorial Coordinator

Bill Whitmer

Technical Edit Coordinator

Lynette Quinn

Formatter

Frank Sinclair

Editorial Assistants

Sharon Cox

Andi Richter

Rhonda Tinch-Mize

Cover Designer

Tim Amrhein

Book Designer

Alyssa Yesh

Production Team Supervisor

Brad Chinn

Production

Mona Brown, Michael Brumit,

Jeane Clark, Michael Dietsche,

Juudy Everly, Jason Hand,

Michael Henry, Aleta Howard,

Ayanna Lacey, Kevin Laseau,

Cheryl Moore, Casey Price,

Brian-Kent Proffit, Beth Rago

Bobbi Satterfield, Laura A. Smith

SA Springer, Josette Starks

Andrew Stone, Susan Van Ness

Mark Walchle, Colleen Williams

Overview

Introduction

Acknowledgments

Sams Publishing wishes to thank Hugo Toledo, Dave McCutcheon, John Moss, and Michelle Trapani of the Oracle Legal Department for their assistance with the book.

AIS wishes to thank the editors at Sams Publishing and all of the authors for their contributions. We offer our special thanks to the following authors: Lori Pleiness, Andrea Sprague, Hari Sankar, Steve Faris, and the folks from G2 Technologies. Special thanks to Brett Mark for providing technical research and overseeing the project. Our extra special thanks to Dr. Frederick Lenz, author of "Surfing the Himalayas."

Michael Richards CEO

Advanced Information Systems Inc.

(800) 327-9725

About the Authors

ADVANCED INFORMATION SYSTEMS, Inc. Michael Richards is the CEO of Advanced Information Systems (AIS), Inc. AIS is a professional software development and consulting firm that offers a range of products and services specializing in Oracle development. A primary objective of AIS is to provide technical solutions in the form of Management Consulting, Project Staffing, Independent Consultants, and Training Services.

AIS offers a popular approach to client/server development called Rapid Application Foundation (RAF). RAF is a set of predeveloped code libraries coupled with skills transfer. RAF is offered as a one-or two-week training system that is tailored to develop an in-house application system. AIS clients receive a well-crafted client/server system that takes advantage of today's premier development systems including Oracle and Visual Basic. Oracle and these tools can be purchased at a discount from AIS. AIS can be reached at (800) 327-9725

Rachel Becker is a software consultant who has been developing administrative business applications since 1985. She has worked primarily with Oracle Tools since 1989, using Oracle's CASE tool since 1991. Her project experience up and down the east coast includes university, insurance, utilities, and medical environments. She is currently living in the Charlotte, NC area. Reach her via e-mail at wbecker709@aol.com.

Matthew Bennett is a Senior Technical Marketing Manager in the Web/Workgroup Systems Division at Oracle Corporation. He is currently responsible for a team of application developers working on creating World Wide Web applications using Oracle's newest Web technologies. Before working at Oracle, he was the founder of Data Smiths, a Provo, Utah-based systems integration firm. At Data Smiths, he was responsible for publishing articles in Network Computing, Software Development, and numerous technical journals.

Winnie In-Kuan Cheang (B.S., M.S., M.B.A.),is currently a Senior Technical Analyst in Oracle Corporation's Worldwide Support. She provides mission-critical support to database administrators and developers of Oracle products on a wide variety of systems and networks. She is also the NetWare administrator and DBA for the Desktop Support Group production system at Oracle. Winnie is an ECNE and has many years of computer networking and relational database experience as a programmer, technical analyst, instructor, and consultant. She has presented and published papers on relational database applications for the Human Genome Project in the IEEE proceedings.

Mike Conklin, an independent consultant working in Rochester, New York, has more than 17 years experience—the past 6 working with Oracle specific applications. Mike and his wife Pam, have enjoyed 21 years of marriage. Together, they home-educate their three children, studying along with them. By God's grace they have chosen to build their relationships and values upon Biblical principles. Renovating an 1800's farm house where they welcome frequent (and flexible!) guests is an added challenge.

Patrick Connors is the CASE analyst for the Arizona Department of Health Services. He is responsible for administering the Designer/2000 tool and acts as a consultant on Oracle CASE projects. He has been an Oracle programmer since 1990 and a computer professional since 1980, working on a variety of projects from accounting systems to video games. In his spare time, he is a cartoonist and kayaker. Patrick is single and resides in Phoenix.

Joe Greene has worked in a variety of fields, from driving submarines to spacecraft design. Along the way, he began to implement computer systems to meet various business needs. This has lead to a career as a computer consultant with a number of assignments working with Oracle databases. His experience ranges from very large data warehouses (with more than 100 gigabytes of data) to small scientific data collection systems, with a wide variety of databases in between. He is the author of the Oracle DBA Survival Guide, also from Sams Publishing.

Scott Hillegas is a senior consultant with Integrated Systems Consulting Group, located in Wayne, Pa. He has more than 10 years of Oracle DBA experience and more than 20 years of MIS experience on various platforms. His area of experience is the design, development, and implementation of relational database and client/server technologies, and most notably, Oracle on the UNIX platform. He began his Oracle career with version 5.1, building OLTP systems, and today is working with version 7.2, building DSS data warehousing systems.

Dan Hotka has 17 years in the computer business, 10 of those with the Oracle products. He first worked with Oracle products in the version 4 days, when SQL*Plus was called UFI (User Friendly Interface) and when Oracle Forms was nothing more than a product called Fastforms, which generated a basic INP file that was compiled with IAG and interpreted by IAP. There was also a neat little report writer called RPT. He worked with Oracle (including Oracle Corp) as a marketing presales support representative (more than four years) and in various consulting, development, and DBA roles (more than six years). He has written language source code filters that have converted entire applications written in other languages into the Oracle languages. These filters include both data and program conversions. He has worked extensively with each of the Oracle releases since Oracle 4, including the current Oracle 7.1 release.

Steve Hughes has been in data processing for more than 10 years. With a strong applications development background in Xbase languages, primarily using FoxPro with TRO as an application foundation, he has recently turned his experience and enthusiasm to Oracle. Recently promoted to the position of Oracle DBA at CMS Therapies in Charlotte, NC, his responsibilities include installing and upgrading client/server software, data backup and recovery, user access maintenance, and performance tuning. His experience includes Oracle running on Novell NetWare 3.12 and a DEC Alpha running DEC OSF/1. He can be reached via CompuServe at 74133,1327.

Dave Kinchen has been involved with Oracle for most of his professional career, spanning development, database administration, and design on many different platforms. On the rare occasions Dave is not working, you might find him with his family doing something fun in the Rockies.

Bruce Kostival holds a BSEE degree from The Pennsylvania State University and an MBA in Information Systems from The University of Colorado. Having been in the field of software development for more than 16 years, he has written software in many areas from real-time data acquisitions to Oracle RDBMS database development. He has developed Oracle software in both the VMS and UNIX environments. Currently, he is employed with a direct marketing company in private industry as a Senior Programmer/Analyst. His real-life interests are his two (going on three) children, and his lovely and talented wife Ruthann. In his little spare time, he enjoys Colorado's fabulous skiing, on- or off-piste!

Ronnie Lashaw has more than 10 years of experience developing and deploying client/server database applications for clients including the US Air Force, NASA, Veterans Administration, US Dept. of Education, Unisys, Boeing, and Lockheed Martin. He has a BS in Computer Science and a minor in Industrial Engineering from North Carolina State University. He has extensive knowledge of Oracle products with emphasis on Oracle Power Objects. His primary objective is to provide intuitive, visually appealing, user-friendly, graphical client/server applications. He can be reached on CompuServe at 74513,1076 or on the Internet at rlashaw@us.oracle.com for discussions about Power Objects.

Kelly Leigh, with more than eight years' experience in the systems administration world made the jump into database administration over three years ago. Starting out with Informix and moving to Oracle, he has had experience in everything from product installation to database layout to implementing custom applications. Currently working for a small company in Longmont, Colorado, he is also co-owner of Colorado Business Solutions, a company created to provide small and mid-range business consulting in a wide range of areas including office automation, network installation, database installation, and software training. Outside of work, Kelly is an avid hiker and camper, spending much of his free time in the foothills of the Rocky mountains.

Keith Majkut is a Senior Software Engineer in the Workgroup Solutions Division of Oracle Corporation. He has been with Oracle for five years, and in that time has worked on a variety of products including Oracle Reports, the Oracle Workgroup Server, Oracle Objects for OLE, and currently Personal Oracle7 for Windows 95. Keith grew up in Milford, Connecticut and graduated from Northeastern University of Boston Massachusettes in 1990 with a Bachelor of Science in Computer Science. When he's not working or spending time with his wife Karen, he can be found riding his motorcycle around the San Francisco Bay area, where he currently resides. Keith can also be found roaming the Oracle groups on the Internet and CompuServe, or you can contact him directly at kmajkut@netcom.com or on CompuServe at 74343,1566.

Byron Pearce is the Database Administrator for Nokia Mobile Phones U.S.-based operations in Fort Worth, Texas, where he has the dual capacity as the MIS Operations Supervisor. He is responsible for insuring the availability and overall health of all UNIX and Oracle RDBMS Systems that support Nokia's U.S.-based manufacturing and distribution systems. Byron has worked in a multi-functional capacity in the MIS Departments for several companies, including SONAT and Tandy Corporation, working as a systems operator, applications developer, systems analyst, project leader, database administrator, and UNIX system administrator. Recently, he completed technical editing of the Oracle DBA Survival Guide by Sams. He lives in Arlington, Texas with his wife, Cherie, and can be reached via Internet at Bpearce163@aol.com.

Lave Singh (BSc, ACGI, MBCS), is founder and head of Titanium Computers Ltd, a training and consulting firm, which over the last eight years has provided Oracle training and consulting services to organizations in Europe, North America, and the Middle East. They specialize in DBA, Tuning, Forms 4.5, Short-Term Technical Consulting, as well as running training courses for a number of blue-chip clients. More than 13 years ago, Lave began at Imperial College on a Computational Science course and since then has been working in the computing field. His wife Permjit and three young children (Benisha, Taejen, and Kashmir) provide a semblance (only just) of sanity away from machines. He can be reached on e-mail at 100135.1773@compuserve.com or by phone on +44(181) 692 5204.

Vic Stambaugh is a principal and Director of Technology Marketing for Integrated Systems Consulting Group, Inc. (ISCG), a Pennsylvania-based consulting firm that develops and integrates client/server systems for the pharmaceutical, health care, and life sciences industries. He has more than 16 years of software development experience, in both full life cycle applications development and database administration, and has worked exclusively with Oracle-based systems since 1986. Vic has designed and taught numerous database and Oracle courses, and is active in the Oracle community as a frequent presenter at international and regional user conferences. He currently serves on the Board of Directors for the Oracle CASE SIG and also served on the Board of Advisors for the Oracle Consultants Alliance.

Brian Twidt has been developing client/server database applications for five years. His experience includes applications designed for such clients as Oracle, NASA, FDA, US DOE, Boeing, and Lockheed Martin. The majority of his experience is related to using desktop development tools such as Oracle Power Objects, Microsoft Visual Basic, C++, and Oracle Card. He has a BS in Computer Science and a minor in Graphic Communications from North Carolina State University. His primary objective is to develop applications that are graphical, intuitive, innovative, and compelling. Any comments regarding Oracle Power Objects can be directed to Mr. Twidt via the Internet at btwidt@us.oracle.com.

Gigi Wadley has been involved with Oracle for approximately five years. Her experience has been obtained through government and private industries. The majority of the work she has done consists of Pro*C and SQL*Forms. She has been married for 13 years and has a 12-year-old daughter. Most of their lives have been spent in Colorado where they enjoy the lifestyle that the state provides.

Chester "Chet" West began his computer career nine years ago as a computer operator in the US Air Force. He later cross-trained into the computer programming career field where he developed and maintained real-time communications system software at both Offutt AFB and Tinker AFB. There he gained his first exposure to database programming, though home-grown. He also began development of an office configuration management system using FoxPro as his database engine. Upon completion of his BS in Computer Science, he went to work for Threads USA in Gastonia, NC, where he helped develop and maintain FoxPro applications. From there, he became a consultant for The Registry and was contracted by CMS Therapies, where he developed both FoxPro and Oracle systems. He is the senior programmer, leading all new Oracle development and assisting in the maintenance of legacy FoxPro systems. He can be reached by sending e-mail to 102046,517@compuserve.com.

Kevin Whitley has worked on everything from mainframe Fortran to micro assembler over the last 15 years. He is currently a lead architect at BBN Domain and uses C++ to develop client/server applications for the pharmaceutical and manufacturing industries.

Joe Zafian III has been working full-time in Information Systems since graduation from New Jersey Institute of Technology with a B.S. in Computer Science in 1981. Currently a consultant specializing in Oracle database applications, he has been working with all versions of the RDBMS and its associated tools since 1986. As a consultant, he has worked with a number of Fortune 500 clients in several industries across a vast array of hardware platforms. This experience qualified him to speak at the international Oracle Users' Week Conference in 1994 (Predicting Storage Requirements for Large, Dynamic Oracle Databases and Advanced Techniques for Dynamic, User-Driven Applications in SQL*Forms 3.0) and 1995 (Quality Assurance for Oracle Projects and Advanced Techniques for Oracle Forms 4.5 Applications). When not striving to deliver quality Oracle systems (or writing about it), Joe enjoys sports, fishing and other outdoor activities with his wife, Ruth. He can be contacted via CompuServe at 73744,2713.

Ken Zimmerman received his Bachelor of Science in Geology from Duke University in 1982. However, his interest in computers dates back as far as 1975 when he became intrigued with what was then that era's "information highway," available through his preparatory school's newly acquired Digital PDP-11/70 system. His work with computers, and especially with database management systems, continued through his undergraduate years as well as during his graduate work in Petrology as an ARCO fellow at USC. In 1985, Ken founded Chateaux Software Development, Inc., a company specializing in PowerBuilder, Oracle, Lotus Notes, and Progress solutions on UNIX, VMS, MS-Windows, Windows-NT Advanced Server, and Novel platforms. His company's primary goal has been to develop high-performance applications on a variety of platforms, ranging from low-end 80x86-based DOS and UNIX systems to midrange HP-9000 HP/UX, IBM AIX, and DEC VAX systems. His firm's strengths are especially geared toward producing highly efficient systems, primarily by developing compact code, will-designed data structures, and a user-oriented architecture. Chateaux is building on these strengths and provides enterprise-quality solutions to WWW/RDBMS integration problems. Ken Zimmerman and Chateaux can be reached at 9 Tierney Lane, Greens Farms CT 06436, by phone at (203) 259-9665, and through CompuServe at 74404,1401.

Introduction

When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products. If you were an experienced Oracle developer three years ago and since then have been locked away—not able to see the new Oracle versions of the database and tools—there will be very little that you will recognize today. Along with changes in other computing technologies, the rate of change with Oracle is constantly increasing, which is all the more interesting for us who get turned on by the software. You'll get a running start with the pages that follow.

Oracle Unleashed is one of the most comprehensive books on Oracle and its tools available today. It's written by authors who have real-life experience using the Oracle tools (most of whom are members of the Oracle Business Alliance Program). We will show you what's important, the pitfalls, tips from real-life experiences, and code examples—The kind of information that can give you the cumulative experience of many years of expertise.

It's a Database Jim, But Not As We Know It

Oracle Corporation, headquartered in Redwood Shores, California, was best known for being a database company that also provided its own tools for working with the database. Many sites used the power of the Oracle database but used better third-party tools as their interface to the database.

Today, the Oracle tools are among the best and most powerful development tools available; if you want it, Oracle can do it. Oracle now provides the database, multiple choices of tools, and utilities to connect to the Oracle database from third-party products, applications (the best known of which is Financials), and a host of other products. In the next year Oracle will release an Internet machine, making the scope of Oracle products mushroom. Not just a database provider but a tools company, software house writing applications, hardware manufacturer, and Internet service provider? (Just give them time.)

SELECT * FROM book

The beginning of the book lays the foundation on top of which all the tools will be built—the architecture of the database and the two languages used in most tools, SQL and PL/SQL. If you don't (yet) want to know how the electricity works but just want to switch on the light, skip over the architecture section.

For database administrators and others who need to know how to keep the database humming, the database administration section will detail the most common functions of looking after the database and traps to watch out for. The remaining sections look at the tools of most interest to developers.

But how is Oracle going to be useful in producing your system? It gives you a multitude of choices to work with; in fact, at times it seems like too many choices. You have choices with the database itself about which database options you want to use. There are choices about which Oracle tools to use: Forms, Reports, Graphics, Power Objects, and more. If you don't want to use the Oracle tools, you can connect PowerBuilder, SQLWindows, Visual Basic, Delphi, and other third-party tools. You can even use the Oracle tools with non-Oracle data sources using ODBC, User Exits written in third-generation languages, or the gateway database products. Whatever you want to do, you can do it in one way or another. Choices, choices, and even more choices.

In addition to using the database and tools, you'll want to manage the development life cycle right from the strategy stage through to implementation and maintenance. What better tool to use than the Designer/2000 product. You can even generate your programs from this tool. Check out the Designer/2000 and Developing Applications sections of the book for designing and developing applications using Designer/2000.

Today, for any application development project, making the right selection of tools and database is even harder and more important. Pick the wrong options, and the time and effort needed can be vastly increased. The information available in this book will make sifting through the plethora of choices easier.

"But I've used Oracle Before"

If you've used previous versions of Oracle products, you'll find it easier to get a grip on the pertinent points of the new tools and components, and to add more background to areas such as the architecture, database administration, Designer/2000, and the other tool options available. If you already have experience with the newer products, then discover how the authors are using the newest tools and options now available.

Is this Book for Me?

Now you know what's going to be covered in the book, the next question you'll probably be asking is whether it's the right book for me.

This book is aimed at

• Experienced Oracle Developers
  
  
• Database administrators
  
  
• Intermediate developers, especially those who have used previous versions of the products
  
  
• Those who have used similar technologies and want to quickly get a grip on Oracle and its tools
  
  
• Technical managers who want to know about the available options
  
  

The book covers SQL and PL/SQL in enough detail to allow you to get up and running quickly with developing with Oracle. Because it is geared toward an intermediate to advanced audience, however, this book quickly covers the essential basics of these two languages and shows you how to start producing real-life applications.

Take a while to play around with the products while you're reading the book. Look at the examples, try them out, and explore. You'll gain a far deeper understanding by using the products while reading.

Open your mind, open the book, and get ready to unleash the power of Oracle!

Lave Singh

December, 1995