IntroductionO racle is the most wid

Introduction
O racle is the most widely used database in the world. It runs on virtually
every kind of computer. It functions virtually identically on all these
machines, so when you learn it on one, you can use it on any other. This
fact makes knowledgeable Oracle users and developers very much in
demand, and makes your Oracle knowledge and skills very portable.
Oracle documentation is thoroughgoing and voluminous, currently spanning
multiple CDs. Oracle8i: The Complete Reference is the first entity that has gathered all
of the major Oracle definitions, commands, functions, features, and products together
in a single, massive core reference—one volume that every Oracle user and developer
can keep handy on his or her desk.
The audience for this book will usually fall into one of three categories:
■ An Oracle end user Oracle can easily be used for simple operations such as
entering data and running standard reports. But such an approach would ignore
its great power; it would be like buying a high-performance racing car, and then
pulling it around with a horse. With the introduction provided in the first two sections
of this book, even an end user with little or no data processing background can
become a proficient Oracle user—generating ad hoc, English-language reports,
guiding developers in the creation of new features and functions, and improving
the speed and accuracy of the real work done in a business. The language of the
book is simple, clear English without data processing jargon, and with few assumptions
about previous knowledge of computers or databases. It will help beginners to
become experts with an easy-to-follow format and numerous real examples.
A developer who is new to Oracle With as many volumes of documentation
as Oracle provides, finding a key command or concept can be a time-consuming
effort. This book attempts to provide a more organized and efficient manner of
learning the essentials of the product. The format coaches a developer new to
Oracle quickly through the basic concepts, covers areas of common difficulty,
examines misunderstanding of the product and relational development, and
sets clear guidelines for effective application building.
■ An experienced Oracle developer As with any product of great breadth and
sophistication, there are important issues about which little, if anything, has been
published. Knowledge comes through long experience, but is often not transferred to
others. This book delves deeply into many such subject areas (such as precedence
in UNION, INTERSECTION, and MINUS operators; inheritance and CONNECT BY;
eliminating NOT IN with an outer join; using interMedia; implementing the
object-relational and Java options; and many others). The text also reveals many
common misconceptions and suggests rigorous guidelines for naming conventions,
application development techniques, and design and performance issues.
For every user and developer, the final section of this book is devoted to a comprehensive
Alphabetical Reference containing all major Oracle concepts, commands, functions, and
features including proper syntax, cross-references, and examples. This is the largest single
reference published on the subject and could be a book unto itself.
How This Book Is Organized
There are nine major parts to this book, and a CD-ROM.
Part I is an introduction to “Critical Database Concepts.” These chapters are essential
reading for any Oracle user, new or veteran, from key-entry clerk to database administrator.
They establish the common vocabulary that both end users and developers can use to
coherently and intelligently share concepts and assure the success of any development effort.
This introductory section is intended for both developers and end users of Oracle. It explores
the basic ideas and vocabulary of relational databases and points out the dangers, classical
errors, and profound opportunities in relational database applications.
Part II, “SQL and SQL*Plus,” teaches the theory and techniques of relational database
systems and applications, including SQL (Structured Query Language) and SQLPLUS. The
section begins with relatively few assumptions about data processing knowledge on the part
of the reader, and then advances step-by-step, through some very deep issues and complex
techniques. The method very consciously uses clear, conversational English, with unique and
interesting examples, and strictly avoids the use of undefined terms or jargon. This section
is aimed primarily at developers and end users who are new to Oracle, or need a quick review
of certain Oracle features. It moves step-by-step through the basic capabilities of SQL and
Oracle’s interactive query facility, SQLPLUS. When you’ve completed this section you should
have a thorough understanding of all SQL key words, functions, and operators. You should
be able to produce complex reports, create tables, and insert, update, and delete data from
an Oracle database.
The later chapters of Part II provide some very advanced methods in SQLPLUS, Oracle’s
simple, command-line interface, and in-depth descriptions of the new and very powerful features of Oracle. This is intended for developers who are already familiar with Oracle, and
especially those familiar with previous versions of Oracle, but who have discovered needs
they couldn’t readily fill. Some of these techniques are previously unpublished and, in some
cases, have been thought impossible. The tips and advanced techniques covered here
demonstrate how to use Oracle in powerful and creative ways. These include taking advantage
of distributed database capabilities, loading data files, and performing advanced text-based
searches.
Part III, “PL/SQL,” provides coverage of PL/SQL. The topics include a review of PL/SQL
structures, plus triggers, stored procedures, and packages.
Part IV, “Object-Relational Databases,” provides extensive coverage of object-oriented
features such as abstract datatypes, methods, object views, object tables, nested tables,
varying arrays, and large objects (LOBs).
Part V, “Java in Oracle,” provides coverage of the Java features introduced in Oracle8i.
This section includes an overview of Java syntax as well as chapters on JDBC and SQLJ and
Java stored procedures.
Part VI contains several guides: a developer’s guide to the data dictionary, a guide to
Oracle’s optimizer, an overview of WebDB, and a beginner’s guide to database administration.
Part VII, “Designing for Performance,” addresses vital issues in the design of useful and
well-received applications. Oracle tools provide a great opportunity to create applications
that are effective and well-loved by their users, but many developers are unaware of some of
the approaches and successes that are possible. This section of the book is aimed specifically
at developers—those individuals whose responsibility it is to understand a business (or other)
application, and design and program an Oracle application to satisfy it. This section should
not be completely incomprehensible to an end user, but the audience is assumed to have a
technical data processing background, and experience in developing computer application
programs. The purpose here is to discuss the techniques of developing with Oracle that have
proven effective and valuable to end users, as well as to propose some new approaches to
design in areas that have been largely, and sadly, ignored. This section includes “The Ten
Commandments of Humane Design,” a list of all of the vital rules of the development
process. A user-oriented guide to the Oracle optimizer concludes this section.
Part VIII, the “Alphabetical Reference,” is the complete reference for the Oracle server.
Reading the introductory pages to this reference will make its use much more effective and
understandable. This section contains references for most major Oracle commands, keywords,
products, features and functions, with extensive cross-referencing of topics. The reference is
intended for use by both developers and users of Oracle but assumes some familiarity with
the products. To make the most productive use of any of the entries, it would be worthwhile
to read the first six pages of the reference. These explain in greater detail what is and is not
included and how to read the entries.
Part IX, “Appendix,” contains the table creation statements and row insertions for all
of the tables used in this book. For anyone learning Oracle, having these tables available on your own Oracle ID, or on a practice ID, will make trying or expanding on the examples
very easy.
The CD that accompanies this book contains a special electronic edition of Oracle8i:
The Complete Reference. Now, with this electronic version, you can easily store all of the
valuable information contained in the book on your PC while the print version of the book
remains in your office or home.