Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 26

Oracle, a major supplier of information management software, was started in 1977 when Larry
Ellison founded Software Development Laboratories (SDL). Though the name changed in 1979 to
Relational Software Inc. (RSI), the company continued to innovate database technologies at a rapid pace. That same year it created the first commercial SQL-based relational database. It wasn’t until
1983 that RSI became Oracle Corporation, and that name has stuck ever since.
In 1988, Oracle was the first company to introduce a built-in procedural language, PL/SQL, that ties very closely to the database engine itself. Programming languages such as C++ can only interact with Oracle via application programming interfaces (APIs). It also permitted application developers to use programming techniques borrowed from other languages such as C and Ada. In 1995, Oracle was first to implement a 64-bit database. Other Oracle firsts include a database with XML support in
1999 and an enterprise grid computing environment in 2003. In late 2005, Oracle released its first full-featured, 100 percent free database server, Oracle Database XE. We will use this version of Oracle
throughout the rest of this book.

■Note The starting point for any Oracle adventure begins, as you may expect, at http://www.oracle.com.

Oracle’s Database Family

Oracle has a product to fit every need. Its free product, Oracle Database 10g Express Edition (Oracle Database XE), will probably fit the needs of most small- to medium-size businesses. As your busi- ness and application needs grow, you can move up to Standard Edition One, Standard Edition, and Enterprise Edition incrementally, with no coding changes other than to take advantage of increasing features available in each version. If you are a developer who needs more memory or disk space for your database than Oracle Database XE allows, Personal Edition may be a viable option for you.

Express Edition (XE)

Oracle Database XE contains most if not all of the features you need to develop and deploy applica- tions with a Web-based front end for any small- to medium-size business. XE will run on 32-bit Windows and most 32-bit distributions of Linux, including Red Hat, Debian, Novell, Mandriva, and Ubuntu. Other flavors of Unix such as Solaris, HP-UX, and AIX are not supported at this time, but you will not typically see these operating systems deployed at small- to medium-size shops.

463

The only other restrictions on this edition are as follows:

• Oracle Database XE will use only one CPU or one dual-core CPU per system.

• The maximum amount of RAM utilized for Oracle Database XE is 1GB.

• The disk space available for user data is limited to 4GB (this does not include system data).

Unlike previous versions of Oracle, the installation process for Oracle Database XE is streamlined and simplified. The default management interface, provided by an Application Express (formerly known as HTML DB) application, is extremely easy to use. You can access every typical management func- tion using this interface. The home page shown in Figure 26-1 provides you with links to management functions and general database usage statistics.

Figure 26-1. The Oracle Database XE administration home page

As part of Oracle’s high availability initiatives, Oracle Database XE includes Flashback Query, an easy way to retrieve the state of the database in the past using a standard SQL query and the AS OF clause. The Flashback Query feature gives the database user an easy way to retrieve data (that may have been erroneously changed, for example) from the past without DBA intervention, saving time for both the user and the DBA.
For application developers, a wide variety of Oracle tools is available. A new graphical develop-
ment tool called SQL Developer provides the end user or developer with an easy way to browse all database objects, run and tune ad hoc queries, and debug PL/SQL code all within the same interface.
Automatic performance monitoring and management doesn’t take a back seat in XE either; automatic memory management frees the DBA (and that could be you!) from frequent tuning of memory components by dynamically changing the size of shared memory components reflecting

the current load on the system. This is especially useful in a development environment where memory is usually shared among many other processes and users.
Oracle Database XE has many of the distributed features of its older and pricier siblings—features such as Advanced Queuing (AQ) and Distributed Transactions. Leveraging these distributed features may be a way to maintain a loosely clustered environment without implementing a full Real Appli- cation Clusters (RAC) database, where two or more Oracle databases are tightly clustered and share a memory cache in a zero-downtime failover configuration.
Finally, Oracle Database XE provides many built-in content management features not found in other open source databases—features such as XML DB and Oracle Text. XML DB provides native storage capabilities for XML documents along with the SQL toolset to retrieve, create, update, and delete these XML documents. Oracle Text can index, search, and analyze all types of documents, including XML and Microsoft Word. The documents can be in the database, in a local or remote file system, or on the Web.
The major focus of the rest of this book is on Oracle Database XE. Until you’re ready to deploy your database for hundreds of thousands of users requiring more than 4GB of user data, Oracle Database XE will very easily fit the bill. And when you’re ready to upgrade, you won’t have to change one line of code or one SQL statement.

Standard Edition One

When your database applications must leverage more than one CPU, more than 1GB of RAM, or more than 4GB of user data in the database (almost always stored on a disk device), Oracle Database
10g Standard Edition One has a more affordable price point than the Standard Edition or the Enter- prise Edition. It is probably your best bet if you don’t yet need to implement an Oracle RAC database. Standard Edition One also contains many features not found in Oracle Database XE, such as 64-bit support, native Java compilation, and Automatic Storage Management (ASM). Oracle Database Standard Edition One, which includes Oracle interMedia, is also the most cost-effective solution if you want to develop, manage, and serve multimedia content, including images, audio, and video.

Standard Edition

Oracle Database 10g Standard Edition bumps up the CPU support to four CPUs (single or dual-core) in addition to the unlimited RAM and database size found in Standard Edition One. It is also the most cost-effective of Oracle’s server products with which to create an Oracle RAC database. If high availability and scalability in a clustered environment is an absolute requirement, and any down- time can be extremely hazardous to your bottom line, you will need to install an Oracle RAC database. Applications that you design to run on a single Oracle instance will run unmodified in a RAC envi- ronment, with the only difference being that such applications will rarely be unavailable to your users, if ever.

Enterprise Edition

At the upper end of the licensing cost spectrum, Oracle Database 10g Enterprise Edition has no CPU restrictions and therefore scales well when you upgrade your servers from four to eight or more CPUs. It also provides the DBA with a number of high availability tools not found in any other edition, such as Flashback Table, Flashback Database, and Data Guard. Flashback Database and Table make it easy for a DBA to restore a table or a database to a specific point of time in the past. Data Guard provides management and automation of one or more transactionally consistent standby databases to guard against database corruption or loss. The standby database can be in the next room, the next building, or half way around the world.

Personal Edition

Oracle Database 10g Personal Edition has all the features of the other editions of Oracle database, including Enterprise Edition, but is limited to a single user. As you might expect, it is ideal for a developer who needs a way to develop applications on a personal workstation that will be deployed later in an enterprise environment. Unlike most other editions, there is no limit to the number of CPUs for the workstation where Oracle Personal Edition is installed.

Other Products in the Oracle Family

In 1996 Oracle began to break away from its database-only roots and developed the first Web-enabled database, Oracle8i Database. In addition, many other Oracle products use the Oracle database engine as one of their key components, such as Oracle 10g Application Server (Oracle AS). Oracle AS uses Oracle Database 10g as its repository for metadata and the open source Apache, both versions 1.3 and 2.0, for a Web server. Oracle provides many customized modules to more seamlessly integrate middleware functionality with the back-end database. For example, Oracle AS makes a developer’s life easier by supporting PL/SQL procedures with the module mod_plsql.
Oracle Database XE is not Oracle’s first foray into free and open source database components. In late 2005, Oracle bought Innobase, a company that provides one of MySQL’s most popular storage engines. In early 2006, Oracle acquired another open source database company, Sleepycat Software, creator of Berkeley DB, a highly scalable, embedded transactional database engine. PHP can easily interact with either of these database engines using native function calls or PHP Data Objects (PDO), a PHP data-access abstraction layer that makes it easier to change your back-end database engine with minimal changes to the database access code.

Developer and Client-Side Tools

In addition to the SQL Developer tool mentioned earlier in this chapter, an Oracle user, DBA, or developer has many options at their disposal. In earlier releases of Oracle, the only SQL interface was a command-line interface. In addition to the plethora of third-party development tools available, you can use SQL Developer to browse database objects and debug PL/SQL code. You can also quickly develop Web-based applications that use your Oracle database with Application Express. Oracle developed the management interface for Oracle Database XE using Application Express.
From within a PHP development environment, you have several extensions at your disposal to
access an Oracle database:

The Oracle extension: This extension was designed for Oracle 7 and should be avoided since it uses a deprecated version of the Oracle API that will not be available in future releases.

ODBC within Windows: While ODBC provides some connection pooling and other built-in features, it lacks access to many of Oracle’s capabilities such as the ability to store large objects (LOBs).

PDO: This portable database API makes it easy to change your database without changing a lot of your code.

OCI8: This extension supports most of Oracle’s Oracle Call Interface (OCI).

Throughout the rest of this book, we will use the OCI8 extension for most of our examples. It maps the most closely to the OCI specification and supports all Oracle Database versions back to Oracle8i. Where appropriate, we will also show you how to use PDO, a more portable database API, and the database abstraction layer first available in PHP 5.1.

Summary

Oracle Database XE is right for almost everyone (at least to get your feet wet in the Oracle pond), and may suit your needs indefinitely. Unfortunately, the acronym LAMP (Linux, Apache, MySQL, PHP) does not sound as catchy when it is rewritten for Oracle on Windows—WOAP—but does seem to work better if you use Oracle on Linux instead: OPAL. But this does not diminish the power of Oracle as part of your database and Web development quartet. When your application outgrows the confines of Oracle Database XE, it’s going to be easier than you expect to move your application to another edition of Oracle.
In the following chapters, we’ll show you how PHP and Oracle are a powerful combination for your rapid Web development needs. We’ll start out by showing you how to install Oracle Database XE, followed by presenting the basics of database administration. In subsequent chapters, we’ll present the tools and methods for accessing the database, which includes showing you how Oracle SQL works. Every chapter will explore and explain how Oracle Database XE works in both Linux and Microsoft Windows environments as well as how you can leverage those features using your newly gained knowledge of PHP from reading the first 25 chapters of this book.