Read an Excerpt

Chapter 1

INTRODUCTION

Modern Databases

The database is an integral component of almost every large computer system. Whether you are building a consumer e-commerce website or developing more traditional client-server systems for in-house use, the database provides the critical central storage area for your data. By now, most developers will be familiar with the power of a relational database. Simply browsing the web almost always involves interacting with one or more highly tuned databases. However, a database is much more than simply a storage container for data.

Modern databases, such as Oracle 9i, provide many features that make the database a powerful development tool. Many of the tasks that used to be exclusively performed by middle-tier systems, such as data translation (e.g., database to XML, or vice versa), can now be executed directly in the database. Actions that used to be confined inside the databases (stored procedures) can now be extended to outside systems (e.g., Java extended stored procedures). Oracle 9i, for instance, can now call Java code external to the database from inside a stored procedure.

Many of these enhancements to the modern database take advantage of the optimized data storage systems and can provide tremendous performance advantages over traditional three-tier systems development. Additionally, many of these common tasks are now built-in features, accessible through simple Application Programming Interfaces (APIs).

Ultimately, this book explains how to exploit these modern database enhancements. It’s a hands-on, practical guide, written for all ranges of Oracle developers. Most important, this book will make you productive with Oracle 9i in as short a time as possible. As you read this book, our hope is that you will come to appreciate the tools associated with Oracle 9i, as well as learn to build powerful and flexible database applications.

Oracle Products

The Oracle 9i database is actually one component within a suite of products, although it is definitely the heart of the product line. At the time of this writing, additional top-level Oracle products include:

• Oracle 9i Application Server (Oracle 9i AS)—Oracle 9i AS delivers J2EE performance with the reliability and security needed for professional business applications and websites.

• Oracle E-Business Suite 11i—A complete set of business applications designed to run entirely on the Internet. Oracle E-Business Suite 11i allows companies to cut costs across customer relationship management, supply chain, financial, project, human resources, and business intelligence functions.

• Oracle 9i JDeveloper—Oracle 9i JDeveloper is a J2EE/XML development environment designed to debug and deploy e-business applications and web services, quickly and easily.

As you will see throughout this book, each of these top-level products actually contains a wide variety of subcomponents. Throughout this book, we have tried to limit the use of the word Oracle to refer to either the Oracle Corporation or the Oracle database. All other Oracle products are referred to by their proper names.

Oracle and Java

Whether you are building Java stored procedures, integrating Oracle with an LDAP system, or simply using objects in your database, you will find that Oracle and Java are fairly inseparable. Almost every Oracle 9i component relies heavily on the Java language. The Oracle Corporation has been a major player in the definition of many of the Java specifications, particularly with regard to database access, security models, and the various components of J2EE. Because of this association, all of the Oracle GUI tools are written in Java, and Java is the language of choice for working with Oracle.

Oracle History

Founded in 1977, Oracle Corporation has become one of the leading software companies in the world. The Oracle Corporation actually introduced the first commercial SQL database back in 1979. Since then, Oracle’s innovations and products have continued to have a significant impact on information technology.

In 1999, Oracle released Oracle 8i, which added a host of features including the ability to create and store complete objects (Oracle 8), as well as a combination of enhancements that made the database ready for Internet computing (Oracle 8i). These enhancements dramatically improved the performance and scalability of the database and set the foundation for Oracle 9i.

Oracle 9i is the latest incarnation of Oracle’s web-enabled database. It adds a host of Internet computing enhancements, Real Application Clusters, Advanced Analytical Services, along with many additional self-tuning, management, and data warehousing features. Ultimately, Oracle 9i creates a complete package of Internet-ready tools that address the highly varied needs of database developers.

Released in June 2001 for Unix/Linux and October 2001 for Windows, Oracle 9i has continued converting core components to interconnect with Java and the Internet. Oracle’s commitments to both Java and the Internet are fundamental to every aspect of the Oracle family of products. The various database management tools are all web-enabled, and the database (through JSP and Servlets) supports direct web access.

Additional Resources

Whether you are encountering Oracle for the first time or are an old hand, you may find that the following websites are essential resources:

• The Oracle Technology Network (OTN)—The OTN is Oracle’s central resource site for Oracle users and developers. If Oracle has ever published a white paper, book, or article on a subject, you can find it on the OTN. Some areas of the OTN require that you register for a free login. In particular, if you follow the instructions in Chapter 2 to download and install an Oracle database, you will need an OTN login. The homepage for the OTN is http://otn.oracle.com.

• Oracle Security Alerts—If you are working with a database that must be secure, you should keep an eye on Oracle’s security-alerts web page: (http://otn.oracle.com/deploy/security/alerts .htm). This page contains links to all of the latest patches, updates, and security notifications related to the Oracle database and any other Oracle products.

• Oracle Online Documentation Search—One of the more obscure but useful Oracle sites is the online documentation search facility (http://download-east.oracle.com/otndoc/oracle9i/901_doc/index.htm). Not only does this facility provide access to all of the online books, it also has an error message lookup function. You should definitely bookmark this site in your browser as it is a great starting point for quick answers to questions like “What does an ORA-00231 error really mean, and what can I do about it?”

In addition to these Oracle-sponsored sites, there are many third-party and user-generated websites that contain an amazing amount of information on Oracle and development in Oracle. See aOR010003 for links to some of these resources.

The Road map

Chapter 1: Introduction—As you have read in this chapter, the Oracle database is much more than a simple container for your data. In the following chapters you will learn how to work with Oracle from a developer’s point of view.

Chapter 2: Installation—Before experimenting with any of the exercises or examples in this book, you must have access to an Oracle 9i database. This chapter provides instructions on downloading, installing, and configuring the developer’s copy of Oracle 9i freely available from the Oracle website. This chapter also details the database we will use for the remaining chapters. Even if you already have access to an Oracle 9i instance, you should still read the Database Configuration part of this chapter to ensure that your database is properly configured for the examples presented throughout the book.

Chapter 3: Oracle Basics—Before diving into the more advanced features of Oracle 9i, you should have a proper grounding in Oracle basics. This chapter explains the Oracle database framework, provides some guidance on database design, discusses creating and using indexes, and illustrates some basic administration functions such as backup and recovery. This chapter is meant as a refresher for users experienced with Oracle and a fast introduction for developers and new Oracle users.

Chapter 4: SQL and PL/SQL—A database without programmatic access is simply a storage container for data. Using the Structured Query Language (SQL) you can insert, extract, and modify data inside your database. This chapter will also introduce you to Oracle’s Procedural Language/SQL (PL/SQL), which can be used to create complex code, stored procedures, and triggers. Although this chapter is not intended as a complete SQL or PL/SQL reference, it should provide you with a solid grounding on the specific way in which Oracle 9i supports the SQL-99 standard and on how PL/SQL works.

Chapter 5: Performance Tuning—One of the areas where programmers and database administrators disagree the most is in regard to performance tuning. Is the slowdown in the program, the network, or the database? This chapter examines strategies for isolating and removing performance problems.

Chapter 6: Oracle and Java—As mentioned earlier, it is almost impossible to separate Oracle 9i from Java. In this chapter, you will see how to use Java within the database, how to access external Java objects from inside the database, and how to use Oracle 9i as an object-oriented database.

Chapter 7: Oracle and XML—The rapid acceptance of XML as a data communication format has resulted in the need for improved XML support from the database. With Oracle 9i, the Oracle database is capable of not only generating XML from a query and inserting data into tables from an XML format; it can also store and index XML content directly. This chapter illustrates the full power of the Oracle 9i XML Developers Kit and Oracle Text.

Chapter 8: Advanced Features—The Oracle 9i family of products includes many advanced features and optional components. This final chapter provides a quick look at some of the components and systems that are beyond the scope of this particular installment of CodeNotes.