Oracle™ Unleashed
Oracle™ Unleashed
Overview
Acknowledgments
About the Authors
Introduction
Part I
1 What Is an RDBMS?
2 Oracle and Client/Server
3 Overview of the Oracle Architecture
Part II
4 SQL
5 PL/SQL—SQL Procedural Language Extension
6 SQL*Plus
7 SQL*DBA
8 SQL*Loader
9 Import and Export
Part III
10 Installation
11 Managing the Database
12 Managing Disk Space
13 Managing Users
14 Backup and Recovery
15 Performance Tuning and Optimizing
16 Database Security
Part IV
17 Designing a Database
18 Object-Oriented Programming with Packages
19 Transaction Processing
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
20 Enforcing Integrity

21 Application Security
Part V
22 Designer/2000
23 Installing Designer/2000
24 Repository Administration
25 Process Modeler
26 Systems Modeling
27 Systems Design
28 Client Application Generation
29 Server Generation
30 Using Designer/2000 with Developer/2000
Part VI
31 Introduction to Developer/2000
32 Oracle Forms 4.5
33 Oracle Reports 2.5
34 Oracle Graphics 2.5
35 Developer 2000 Integration
Part VII
36 Introduction to Oracle Power Objects
37 Connecting to Database Sources Through Sessions
38 Managing the Database
39 Understanding the Application Model
40 Objects Within Oracle Power Objects
41 Building an Application with the Form Designer
42 Creating Reports
43 Using the Debugger
44 Oracle Basic
45 Libraries, User-Defined Properties and Methods, and Classes
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Part VIII

46 Introduction to Oracle Objects for OLE
47 Oracle OLE Automation Server
48 The Oracle Data Control
49 Oracle Objects for OLE C++ Class Library
Part IX
50 Oracle Precompilers
51 ODBC Applications
52 Parallel Processing
53 Networking
54 Oracle PowerBrowser
55 Oracle WebSystem and the World Wide Web Interface Kit
Part X
56 Personal Oracle7
57 Workgroup 2000
58 Oracle7 Server
Part XI
A Using PowerBuilder
B Using SQLWindows
C Using Visual Basic
D Using Delphi
E Oracle Power Objects Programming Reference
Installing the CD-ROM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous
Page

TOC

Next
Page


Home

● 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.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Part I Oracle Concepts
1 What Is an RDBMS?
2 Oracle and Client/Server
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3 Overview of the Oracle Architecture
Part II Oracle Tools and Utilities
4 SQL
5 PL/SQL SQL Procedural Language Extension
6 SQL*Plus
7 SQL*DBA
8 SQL*Loader
9 Import and Export
Part III Database Administration
10 Installation
11 Managing the Database
12 Managing Disk Space
13 Managing Users
14 Backup and Recovery
15 Performance Tuning and Optimizing
16 Database Security
Part IV Developing Applications
17 Designing a Database
18 Object-Oriented Programming with Packages
19 Transaction Processing
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
20 Enforcing Integrity

21 Application Security
Part V Designer/2000
22 Designer/2000
23 Installing Designer/2000
24 Repository Administration
25 Process Modeling
26 Systems Modeling
27 Systems Design
28 Client Application Generation
29 Server Generation
30 Using Designer/2000 with Developer/2000
Part VI Developer 2000
31 Introduction to Developer/2000
32 Oracle Forms 4.5
33 Oracle Reports 2.5
34 Oracle Graphics 2.5
35 Developer/2000 Integration
Part VII Oracle Power Objects
36 Introduction to Oracle Power Objects
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
37 Connecting to Database Sources Through Sessions
38 Managing the Database
39 Understanding the Application Model
40 Objects Within Oracle Power Objects
41 Building an Application with the Form Designer
42 Creating Reports
43 Using the Debugger
44 Oracle Basic
45 Libraries, User-Defined Properties and Methods, and Classes
Part VIII Oracle Objects for OLE

46 Introduction to Oracle Objects for OLE
47 Oracle OLE Automation Server
48 The Oracle Data Control
49 Oracle Objects for OLE C++ Class Library
Part IX Advanced Topics
50 Oracle Precompilers
51 ODBC Applications
52 Parallel Processing
53 Networking Oracle
54 Oracle PowerBrowser
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
55 The Oracle WebSystem and the World Wide Web Interface Kit
Part X Oracle Packages
56 Personal Oracle7
57 Workgroup 2000
58 Oracle7 Server
Part XI Appendixes
A Using PowerBuilder
B Using SQLWindows
C Using Visual Basic
D Using Delphi
Index
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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

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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
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
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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 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

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,
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Previous
Page

TOC

Next
Page

Home


Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
TOC

Next
Page

Home

■ The Authors
The Authors
***Richards***
Advanced Information Systems (AIS), Inc.
Michael Richards, CEO
***Becker***
Rachel Becker
***Bennett***
Matthew Bennett
***Cheang***
Winnie In-Kuan Cheang
***Connors***
Patrick Connors
***Conklin***
Mike Conklin
***Greene***
Joe Greene
***Hillegas***
Scott Hillegas
***Hotka***
Dan Hotka

***Hughes***
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Steve Hughes
***Kinchen***
Dave Kinchen
***Kostival***
Bruce Kostival
***Lashaw***
Ronnie Lashaw
***Majkut***
Keith Majkut
***Pearce***
Byron Pearce
***Singh***
Lave Singh
***Stambaugh***
Vic Stambaugh
***Twidt***
Brian Twidt
***West***
Chet West
***Zathian***
Joe Zafian, III
Not pictured: Kelly Leigh, Gigi Wadley, Kevin Whitley, and Kenneth Zimmerman
TOC

Next
Page

Home


Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous
Page

TOC

Next
Page

Home

● Part I
Part I
Oracle Concepts
Previous
Page

TOC

Next
Page

Home

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous
Page

TOC


Next
Page

Home

● 1
❍ What Is an RDBMS?
■ The Relational Database Model
■ Hierarchical and Network Models
■ Elements of the Relational Model
■ Relational Data Structures
■ Key Values and Referential Integrity
■ Relational Algebra
■ RDBMS Components
■ The RDBMS Kernel
■ The Data Dictionary
■ Nonprocedural Data Access (SQL)
■ Summary
1
What Is an RDBMS?
In recent years, database management systems (DBMS) have established themselves as the primary means of data
storage for information systems ranging from large commercial transaction processing applications to PC-based desktop
applications. At the heart of most of today's information systems is a relational database management system (RDBMS).
RDBMSs have been the workhorse for data management operations for over a decade and continue to evolve and
mature, providing sophisticated storage, retrieval, and distribution functions to enterprise-wide data processing and
information management systems. Compared to the file systems, relational database management systems provide
organizations with the capability to easily integrate and leverage the massive amounts of operational data into
meaningful information systems. The evolution of high-powered database engines such as Oracle7 has fostered the
development of advanced "enabling" technologies including client/server, data warehousing, and online analytical

processing, all of which comprise the core of today's state-of-the-art information management systems.
Examine the components of the term relational database management system. First, a database is an integrated
collection of related data. Given a specific data item, the structure of a database facilitates the access to data related to it,
such as a student and all of his registered courses or an employee and his dependents. Next, a relational database is a
type of database based in the relational model; non-relational databases commonly use a hierarchical, network, or object-
oriented model as their basis. Finally, a relational database management system is the software that manages a relational
database. These systems come in several varieties, ranging from single-user desktop systems to full-featured, global,
enterprise-wide systems, such as Oracle7.
This chapter discusses the basic elements of a relational database management system, the relational database, and the
software systems that manage it. Also included is a discussion of nonprocedural data access. If you are a new user to
relational database technology, you'll have to change your thinking somewhat when it comes to referencing data
nonprocedurally.
The Relational Database Model
Most of the database management systems used by commercial applications today are based on one of three basic
models: the hierarchical model, the network model, or the relational model. The following sections describe the various
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
differences and similarities of the models.
Hierarchical and Network Models
The first commercially available database management systems were of the CODASYL type, and many of them are still
in use with mainframe-based, COBOL applications. Both network and hierarchical databases are quite complex in that
they rely on the use of permanent internal pointers to relate records to each other. For example, in an accounts payable
application, a vendor record might contain a physical pointer in its record structure that points to purchase order records.
Each purchase order record in turn contains pointers to purchase order line item records.
The process of inserting, updating, and deleting records using these types of databases requires synchronization of the
pointers, a task that must be performed by the application. As you might imagine, this pointer maintenance requires a
significant amount of application code (usually written in COBOL) that at times can be quite cumbersome.
Elements of the Relational Model
Relational databases rely on the actual attribute values as opposed to internal pointers to link records. Instead of using an
internal pointer from the vendor record to purchase order records, you would link the purchase order record to the vendor
record using a common attribute from each record, such as the vendor identification number.

Although the concepts of academic theory underlying the relational model are somewhat complex, you should be
familiar with are some basic concepts and terminology. Essentially, there are three basic components of the relational
model: relational data structures, constraints that govern the organization of the data structures, and operations that are
performed on the data structures.
Relational Data Structures
The relational model supports a single, "logical" structure called a relation, a two-dimensional data structure commonly
called a table in the "physical" database. Attributes represent the atomic data elements that are related by the relation. For
example, the Customer relation might contain such attributes about a customer as the customer number, customer name,
region, credit status, and so on.
In relational database design literature, you might see a relation denoted as Relation(attribute1, attribute2, . . .)
with the name of the relation followed by the attribute list enclosed in parentheses.
Customer(Customer_ID, Customer_Name, Region, . . .)
The actual data values for the attributes of a relation are stored in tuples, or rows, of the table. It is not necessary for a
relation to have rows in order to be a relation; even if no data exists for the relation, the relation remains defined with its
set of attributes. Figure 1.1 illustrates the basic elements of the Customer relation.
Figure 1.1. The basic components of a relation.
Key Values and Referential Integrity
Attributes are grouped with other attributes based on their dependency on a primary key value. A primary key is an
attribute or group of attributes that uniquely identifies a row in a table. A table has only one primary key, and as a rule,
every table has one. Because primary key values are used as identifiers, they cannot be null. Using the conventional
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
notation for relations (shown in the note), an attribute is underlined to indicate that it is the primary key of the relation. If
a primary key consists of several attributes, each attribute is underlined.
You can have additional attributes in a relation with values that you define as unique to the relation. Unlike primary
keys, unique keys can contain null values. In practice, unique keys are used to prevent duplication in the table rather than
identify rows. Consider a relation that contains the attribute, United States Social Security Number (SSN). In some rows,
this attribute may be null in since not every person has a SSN; however for a row that contains a non-null value for the
SSN attribute, the value must be unique to the relation.
Linking one relation to another typically involves an attribute that is common to both relations. The common attributes
are usually a primary key from one table and a foreign key from the other. Referential integrity rules dictate that foreign

key values in one relation reference the primary key values in another relation. Foreign keys might also reference the
primary key of the same relation. Figure 1.2 illustrates two foreign key relationships.
Figure 1.2. Foreign keys that reference a primary key in another table as well as a primary key in the same table.
Many database design tools use underlines to denote primary keys in diagram and report views. Depending on
the tool that you use, you might also see (PK) and (FK) next to attributes to denote primary and foreign keys,
respectively.
You will notice frequent references to the DEPT and EMP sample tables throughout Oracle documentation. You
can find these tables along with other sample database objects in Oracle's standard demonstration account. You usually
access the account with the username/password combination scott/tiger.
You typically design a relational database using the rules of normalization that dictate which attributes belong in which
relations. There are five levels (or forms) of normalization to which a data model can comply. Of the five, most database
designs minimally conform to the third normal form. This form serves to alleviate redundancy in the data model,
requiring each atomic data element to appear once in the data model and be dependent on one and only one primary key.
Employing a normalized data model protects against insert, update, and delete anomalies that can arise as a result of
incorrectly defined relations.
Relational Algebra
The relational model defines the operations that are permitted on a relation or group of relations. There are unary and
binary relational operators, each of which result in another relation. You should find these operations somewhat intuitive
and very similar to those used with set operations. Table 1.1 describes the seven operators used to manipulate relational
structures. Binary operator types indicate that the operation uses two relations as operands; unary operators require a
single relation as an operand.
Table 1.1. Algebra operations of the relational model.
Operation Type Resulting Relation
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Union Binary Rows from the two relations are combined, eliminating duplicate rows.
Intersection Binary Rows common to two relations.
Difference Binary Rows that exist in the first relation but not in the second.
Projection Unary Rows that contain some of the columns from the source relation.
Selection Unary Rows from the source relation that meet query criteria.
Product Binary Concatenation of every row in one relation with every row in another.

Join Binary
Concatenation of rows from one relation and related rows from another.
The source relations used by UNION, INTERSECTION, and DIFFERENCE must have attribute lists that match
in number and data type.
RDBMS Components
Two important pieces of an RDBMS architecture are the kernel, which is the software, and the data dictionary, which
consists of the system-level data structures used by the kernel to manage the database.
The RDBMS Kernel
You might think of an RDBMS as an operating system (or set of subsystems), designed specifically for controlling data
access; its primary functions are storing, retrieving, and securing data. Like an operating system, Oracle7 manages and
controls access to a given set of resources for concurrent database users. The subsystems of an RDBMS closely resemble
those of a host operating system and tightly integrate with the host's services for machine-level access to resources such
as memory, CPU, devices, and file structures. An RDBMS such as Oracle7 maintains its own list of authorized users and
their associated privileges; manages memory caches and paging; controls locking for concurrent resource usage;
dispatches and schedules user requests; and manages space usage within its tablespace structures. Figure 1.3 illustrates
the primary subsystems of the Oracle7 kernel that manage the database.
Figure 1.3. An RDBMS and its multiple subsystems.
The Data Dictionary
A fundamental difference between an RDBMS and other database and file systems is in the way that they access data. A
RDBMS enables you to reference physical data in a more abstract, logical fashion, providing ease and flexibility in
developing application code. Programs using an RDBMS access data through a database engine, creating independence
from the actual data source and insulating applications from the details of the underlying physical data structures. Rather
than accessing a customer number as bytes 1 through 10 of the customer record, an application simply refers to the
attribute Customer Number. The RDBMS takes care of where the field is stored in the database. Consider the amount of
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
programming modifications that you must make if you change a record structure in a file system-based application. For
example, if you move the customer number from bytes 1 through 10 to bytes 11 through 20 to accommodate an
additional field, all the programs that use the customer number would require modification. However, using an RDBMS,
the application code would continue to reference the attribute by name rather than by record position, alleviating the
need for any modifications.

This data independence is possible because of the RDBMS's data dictionary. The data dictionary stores meta-data (data
about data) for all the objects that reside in the database. Oracle7's data dictionary is a set of tables and database objects
that is stored in a special area of the database and maintained exclusively by the Oracle7 kernel. As shown in Figure 1.4,
requests to read or update the database are processed by the Oracle7 kernel using the information in the data dictionary.
The information in the data dictionary validates the existence of the objects, provides access to them, and maps the
actual physical storage location.
Figure 1.4. Access to application data through the Oracle7 Kernel and Data Dictionary.
Not only does the RDBMS take care of locating data, it also determines an optimal access path to store or retrieve the
data. Oracle7 uses sophisticated algorithms that enable you to retrieve information either for the best response for the
first set of rows, or for total throughput of all rows to be retrieved.
Nonprocedural Data Access (SQL)
An RDBMS differentiates itself with its capability to process a set of data; other file systems and database models
process data in a record-by-record fashion. You communicate with an RDBMS using Structured Query Language (SQL,
pronounced sequel). SQL is a nonprocedural language that is designed specifically for data access operations on
normalized relational database structures. The primary difference between SQL and other conventional programming
languages is that SQL statements specify what data operations should be performed rather than how to perform them.
For example, consider a procedure to give a salary increase to a particular department for each employee who had not
received a raise within the past six months. The code segments in Figure 1.5 illustrate the solution to the problem using
both procedural and nonprocedural methods.
Figure 1.5. SQL programming versus traditional procedural programming methods.
Although the example in Figure 5.1 illustrates a simplistic scenario, consider a more complex application and the amount
of programming that is alleviated by using SQL for data access. By reducing the amount of programming required for
data access, the costs to develop and maintain the data access portions of an application are also reduced.
Summary
This chapter describes two aspects of a relational database management system: the relational database model and the
database management system. The relational model defines relations, which are the underlying database structures;
constraints, which are the rules that govern their relationships to one another; and the relational algebra operations that
you can perform on relations. Relational database management systems work on sets of data and employ many of the
concepts of basic set theory.
A full-featured management system for a relational database is a sophisticated, complex piece of software that functions

very much like an operating system. One of the reasons that Oracle has been so successful and widely used is that it has
been able to implement the same "logical" database operating system on a variety of host operating systems. User access
to objects in the database is controlled by the RDBMS kernel and the meta-data stored in the data dictionary.
Applications never access the data in the actual operating system data files directly; instead, all access is provided
through the RDBMS.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Access to RDBMS data is accomplished through nonprocedural requests using SQL. Compared to conventional file
system access, SQL provides "set-at-a-time" as opposed to "row-by-row" processing. The language elements and usage
of SQL are covered in Chapter 4.
Previous
Page

TOC

Next
Page

Home

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.