The inspiration for the material contained in this book comes from my experiences developing Oracle software and working with fellow Oracle developers, helping them build reliable and robust applications based on the Oracle database. The book is basically a reflection of what I do everyday and of the issues I see people encountering each and every day.

I covered what I felt was most relevant - namely the Oracle database and its architecture. I could have written a similarly-titled book explaining how to develop an application using a specific language and architecture - for example, one using Java Server Pages that speak to Enterprise Java Beans, that use JDBC to communicate with Oracle. However, at the end of the day, you really do need to understand the topics covered here in order to build such an application successfully. This book deals with what I believe needs to be universally known to develop successfully with Oracle, whether you are a Visual Basic programmer using ODBC, a Java programmer using EJBs and JDBC, or a Perl programmer using DBI Perl. This book does not promote any specific application architecture; it does not compare 3-tier to client-server. Rather, it covers what the database can do and what you must understand about the way it works. Since the database is at the heart of any application architecture, the book should have a broad audience.

What this Book is About

One of the problems with having plenty of development options is in trying to figure out which one might be the best choice for your particular needs. Everyone wants as much flexibility as possible (as many choices as they can possibly have) but they also want things to be very cut and dry; in other words, easy. Oracle presents the developer with almost unlimited choice. No one ever says 'you can't do that in Oracle'- they say 'how many different ways would you like to do that in Oracle?' I hope that this book will help you make the correct choice.

It is a book for those people who appreciate the choice but would also like some guidelines and practical implementation details on Oracle features and functions. For example, Oracle has a really neat feature called the virtual private database. Oracle documentation tells you how to use this feature and what it does. Oracle documentation does not, however, tell you when you should use this feature and, perhaps even more importantly, when you should not use this feature. It does not always tell you the implementation details of this feature, and if you're not aware of them, this can really come back to haunt you (I'm not referring to bugs, but really the way it is supposed to work and what the feature was really designed to do).

Who Should Use this Book?

The target audience for this book is anyone who develops applications with Oracle as the database backend. It is a book for professional Oracle developers who need to know how to get things done in the database. The practical nature of the book means that many sections should also be very interesting to the DBA. Most of the examples in the book use SQL*PLUS to demonstrate the key features, so you won't find out how to develop a really cool GUI - but you will find out how the Oracle database works, what its key features can do and when they should (and should not) be used.

It is a book for anyone who wants to get more out of Oracle with less work. It is for anyone who wants to see new ways to use existing features. It is for anyone who wants to see how these features can be applied in the real world (not just examples of how to use the feature but why the feature is relevant in the first place). Another category of people that would find this book of interest would be the technical manager in charge of the developers who work on Oracle projects. In some respects, it is just as important that they understand why knowing the database is crucial to success. This book can provide ammunition for the manager who would like to get their personnel trained in the correct technologies, or in ensuring that they already know what they need to know.

In order to get the most out of this book, the reader should have:

  • Knowledge of SQL.  You don't have to be the best SQL coder ever, but a good working knowledge would help.

  • An understanding of PL/SQL.  This is not a pre-requisite but will help you to 'absorb' the examples. This book will not, for example, teach you how to program a FOR loop or declare a record type - the Oracle documentation and numerous books cover this well. However, that's not to say that you won't learn a lot about PL/SQL by reading this book. You will. You'll become very intimate with many features of PL/SQL and you'll see new ways to do things, become aware of packages/features that perhaps you did not know existed.

  • Exposure to some 3GL language such as C or Java.  I believe that anyone who can read and write code in a 3GL language will be able to successfully read and understand the examples in this book.

  • Familiarity with the Oracle Server Concepts Manual.

A few words on that last point: due to its vast size, many people find the Oracle documentation set to be somewhat intimidating. If you are just starting out or haven't read any of it as yet, I can tell you that the Oracle8i Concepts manual is exactly the right place to start. It is about 800 pages long and touches on many of the major Oracle concepts that you need to know about. It may not give you each, and every technical detail (this is what the other 10,000 to 20,000 pages of documentation are for) but it will educate you on all of the important concepts. This manual touches the following topics (to name a few):

  • The structures in the database, how data is organized and stored.

  • Distributed processing.

  • Oracle's memory architecture.

  • Oracle's process architecture.

  • Schema objects you will be using (tables, indexes, clusters, and so on).

  • Built-in data types and user-defined data types.

  • SQL-stored procedures.

  • How transactions work.

  • The optimizer.

  • Data integrity.

  • Concurrency control.

I will come back to these topics myself time and time again. These are the fundamentals - without knowledge of them, your Oracle applications will be prone to failure. I encourage you to read through the manual and get an understanding of some of these topics.

How This Book is Structured

To help you use this book, it is organized into six discrete sections (described below). These are not rigid divisions, but they will help you navigate quickly to the area you need most. This book has 23 chapters and each is like a 'mini-book'- a virtually standalone component. Occasionally, I refer to examples or features in other chapters (the Security section, in particular, relies a little more on examples and concepts that are built up over several chapters) but you could pretty much pick a chapter out of the book and read it standalone. You will not have to read Chapter 10 to understand or make use of Chapter 14, for example.

The format and style of each chapter is virtually identical:

  • An introduction to the feature or capability.

  • Why you might want to use it (or not). I outline the times you would consider using this feature and when you would not want to use it.

  • How to use this feature. Not just a copy of the SQL reference here but rather step by step - here is what you need, here is what you have to do, these are the switches you need to go through to get started. Things covered in this section will be:

    How to implement it

    Examples, examples, and examples

    Debugging this feature

    Caveats of using this feature

    Handling errors (proactively)

  • A summary to bring it all together.

There will be lots of examples, and lots of code, all of which will be available for download at Following is a detailed breakdown of the content of each section:

Understanding the Database

  • Chapter 1, Developing Successful Oracle Applications.  This chapter sets out my essential approach to database programming. All databases are not created equal and in order to develop database-driven applications successfully and on time, you need to understand exactly what your particular database can do and how it does it. If you do not know what your database can do, you run the risk of continually 're-inventing the wheel' - developing functionality that the database already provides. If you do not know how your database works you are likely to develop applications that perform poorly and do not behave in a predictable manner.

    The chapter takes an empirical look at some applications where a lack of basic understanding of the database has lead to project failure. With this example-driven approach, the chapter discusses the basic features and functions of the database that you, the developer, need to understand. The bottom line is that you cannot afford to treat the database as a black box that will simply 'churn out the answers' and take care of scalability and performance by itself.

  • Chapter 2, Architecture.  The Oracle database is a highly complex tool. Every time you connect to a database or issue an UPDATE command, a whole host of processes occur in the background to make sure that you're application runs smoothly and that data integrity is maintained. For example, the database ensures that it has enough information to restore the data to its original state should it need to. It will cache program data and automatically re-use it where appropriate. And so on. Most of the time all of this is occurs transparently (to the developer, at least) but when problems occur, half the battle is knowing where to look to fix them.

    This chapter covers the three major components of the Oracle architecture - its memory structures (specifically, the System Global Area), its physical processes, and its set of files (parameter files, redo log files...). Understanding the Oracle architecture is fundamental to understanding the unique way in which Oracle implements certain features and how this will affect your application.

  • Chapter 3, Locking and Concurrency.  Different databases have different ways of doing things (what works well in SQL Server may not work as well in Oracle) and understanding how Oracle implements locking and concurrency control is absolutely vital to the success of your application.

    This chapter discussed Oracle's basic approach to these issues, the types of locks that can be applied (DML, DDL, latches...) and the problems that can arise if locking is not implemented carefully (deadlocking, blocking and escalation). The concurrency control section discusses the functions provided by Oracle that allow us to control how users can access and modify the database.

  • Chapter 4, Transactions.  Transactions are a fundamental feature of all databases - they are part of what distinguishes a database from a file system. And yet, they are often misunderstood and many developers do not even know that they are accidentally not using them. This chapter examines how transactions should be used in Oracle and also exposes some 'bad habits' that have been picked up when developing with other databases. In particular, we look at the implications of atomicity and how it affects statements in Oracle. We then move on to discuss transaction control statements (COMMIT, SAVEPOINT, ROLLBACK), integrity constraints and distributed transactions (the two-phase commit). Finally, we look at some real world issues surrounding use of transactions - how they are logged, and the role of redo and undo.

Database Structures and Utilities


Advanced SQL Features





We have used a number of different styles of text and layout in this book to help you differentiate between the different kinds of information. Here are examples of the styles we use and an explanation of what they mean:

Code has several fonts. If it's a word that we're talking about in the text, for example when discussing a PL/SQL SELECT query, it's in this font. If it's a block of code that you can type as a program and run, then it is in a gray box:

tkyte@DEV816> create or replace procedure StaticEmpProc(p_job in varchar2)   2  as   3    begin   4      for x in (select ename from emp where job = p_job)   5        loop   6          dbms_output.put_line( x.ename );   7        end loop;   8  end;   9  /      Procedure created. 

In this book we have also shown line numbers directly from the SQL*PLUS session, for ease of reference.


Advice, hints, and background information comes in this type of font.


Important pieces of information come in boxes like this.

Bullets appear indented, with each new bullet marked as follows:

Source Code and Updates

As you work through the examples in this book, you may decide that you prefer to type in all the code by hand. Many readers prefer this because it is a good way to get familiar with the coding techniques that are being used.

Whether you want to type the code in or not, we have made all the source code for this book available at our web site at the following address:

If you're one of those readers who likes to type in the code, you can use our files to check the results you should be getting they should be your first stop if you think you might have typed in an error. If you're one of those readers who does not like typing, then downloading the source code from our web site is a must!

Also, errata sheets are available for all our books at on each book's page. If you find an error that hasn't already been reported, please let us know.

Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON © 2008-2017.
If you may any questions please contact us: