Book Image

PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax

By : Yuli Vasiliev
Book Image

PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax

By: Yuli Vasiliev

Overview of this book

Oracle Database gets high marks for performance, reliability, and scalability. Building and deploying your PHP applications on Oracle Database enables you to combine the power and robustness of Oracle and the ease of use, short development time, and high performance of PHP. When used in a complementary way, PHP and Oracle allow you to build high-performance, scalable, and reliable data-driven Web applications with a minimum of effort.When building a PHP/Oracle application, you have two general options. The first is to use an Oracle database just to store data, performing all the operations on that data on the client side; the other is to use the database not only to store data, but also to process it, thus moving data processing to the data. While building the key business logic of a database-driven PHP application inside the database is always a good idea, you should bear in mind that not all of the databases available today allow you to do. The Oracle database, which offers record-breaking performance, scalability, and reliability, does. The partnership of Oracle and the open-source scripting language PHP is an excellent solution for building high-performance, scalable, and reliable data-driven web applications.This 100% practical book is crammed full of easy-to-follow examples. It provides all the tools a PHP/Oracle developer needs to take advantage of the winning combination. It addresses the needs of a wide spectrum of PHP/Oracle developers, placing the emphasis on the most up-to-date topics, such as new PHP and Oracle Database features, stored procedure programming, handling transactions, security, caching, web services, and Ajax.
Table of Contents (16 chapters)
PHP Oracle Web Development
Credits
About the Author
About the Reviewer
Preface

What You Need to Start


Before you can proceed to PHP/Oracle development, you need to have PHP and Oracle database installed and working properly in your system. Moreover, to work with examples provided in this book, you will need an SQL command-line tool, such as SQL*Plus, allowing you to enter and execute SQL statements and PL/SQL code to manipulate database data, manage database objects, and perform database administration tasks.

This section briefly discusses all these pieces of software individually as well as how they fit into the big picture. For a discussion of how to install all the above software pieces and make them work together, see Appendix A Installing PHP and Oracle Software.

Pieces of Software Required

On jumping into a discussion of the software required for PHP/Oracle development, the first question you might ask is: "Which software components do I need to install in my system to be able to start developing PHP/Oracle applications?" Well, the list of required software components includes the following items:

  • Oracle Database Server software

  • An Oracle database

  • Oracle Client libraries

  • A web server with activated support for PHP

  • An SQL command-line tool to interact with the database (or a GUI tool)

While the above list just tells you what software components you need to install, the following list outlines general steps to take to install them in your system:

  • Install Oracle Database Server software. Make sure to create a database during installation.

  • Install a web server. Note that, in most production environments, the web server and Oracle database server reside on different machines located within the same network. But for simplicity, you might have both the web server and Oracle database server installed on the same machine.

  • Install PHP and configure the web server to use it.

  • Install Oracle Client libraries. Note that you don't need to do it if the web server and Oracle database server reside on the same machine.

  • Configure the PHP installation to work with Oracle.

Note

The above three steps are unnecessary when employing Zend Core for Oracle—a tool that allows you to install, deploy, and configure PHP to work with Oracle as quickly as possible. For a detailed discussion of Zend Core for Oracle, see the Installing Zend Core for Oracle section in Appendix A.

  • Install an SQL command-line tool to perform database administration tasks and manipulate database objects. Note that you don't need to worry about this if you are going to connect to the database from the same machine on which the Oracle database server has been installed. In this case, Oracle SQL*Plus—standard Oracle SQL command-line tool—is installed by default.

Note

As a graphical alternative to Oracle SQL*Plus, you might use Oracle SQL Developer, a new, free GUI tool that can be used not only to perform database administration tasks and manipulate database objects, but also to edit and debug PL/SQL code. You can download Oracle SQL Developer from Oracle Technology Network (OTN) at: http://www.oracle.com/technology/software/products/sql. For more information, see the Oracle SQL Developer home page on OTN at: http://www.oracle.com/technology/products/database/sql_developer/index.html.

Before proceeding to the installation of the above products, it is recommended that you read through the rest of this section to get an overview of the software components mentioned above. Then, you can proceed to Appendix A Installing PHP and Oracle Software, which provides a quick-and-dirty guide for each step presented in the above list.

Oracle Database Considerations

This section provides a brief overview of some of the issues related to the Oracle Database, which you need to be familiar with before installing Oracle Database software in your system.

Understanding the Oracle Database

Looking through the list of the software components that need to be installed in your system, you might notice that it distinguishes between Oracle Database Server software and an Oracle database. If you are new to Oracle, this may sound confusing to you. This needs a little explanation.

According to the Oracle terminology, an Oracle database is simply a collection of user and control data stored on a disk and is treated as a unit. It is obvious that a database itself is useless—you need software to operate it.

Note

An Oracle database server consists of an Oracle database and an Oracle instance. While an Oracle database represents a collection of files that hold the database data and metadata, an Oracle instance represents the combination of the background processes operating on a database and shared memory used by those processes.

During the installation of the Oracle Database software, you have the option of creating a database or installing the software. It means that you can either install the Oracle software components designed to operate on a database and create a database itself or install only the Oracle software components. You might want to choose the later if, for example, you already have a database created and you want to use it with the newly installed software.

Choosing Between Oracle Database Editions

At the time of writing this book, the latest production release of Oracle's database was Oracle Database 10g Release 2, which is available in several editions outlined below:

  • Oracle Database 10g Express Edition—a starter database for DBAs and developers. Being completely free of charge, this no-frills edition of Oracle Database supports up to 4 GB of user data and executes on one processor only. Built on the same core code as Oracle Database 10g Release 2, Express Edition provides the same set of integrated programming interfaces available in the other editions of Oracle Database 10g and can be easily upgraded to Standard or Enterprise Edition.

  • Oracle Database 10g Standard Edition One—a full-featured Oracle database that is ideal for small- to-medium-sized business environments. Standard Edition One provides the proven performance, ease of use, reliability and security of Oracle Database at a low cost. It can only be licensed on servers supporting up to two CPUs.

  • Oracle Database 10g Standard Edition—an ideal choice for medium-sized business environments. Unlike Standard Edition One, Standard Edition supports Real Application Clusters, an Oracle technology that enables the clustering of the Oracle Database, comprising several Oracle instances running on multiple clustered computers so that they operate as a single system. Standard Edition can be licensed on single or clustered servers with up to four processors.

  • Oracle Database 10g Enterprise Edition—ideal for enterprises that have to operate on large amounts of information. Enterprise Edition contains all of the components of the Oracle Database, offering enterprise-class performance as well as reliable and secure data management for mission-critical applications.

Note

For more information on the Oracle Database product family, see Oracle white papers and Oracle documentation available on the OTN web site at: http://www.oracle.com/technology.

You can choose the edition of Oracle Database that best suits your needs and budget. As for the examples provided in this book, they should work with any of the above editions, including Oracle Database Express Edition. Unless otherwise noted, all examples provided in this book will work with Oracle Database 10g Release 2, or higher, irrespective of its edition.

Obtaining Oracle Database Software

Since most of the Oracle products are available on a commercial basis, you may be asking yourself: "Is there any way to try Oracle software for free in order to determine if it suits my needs or not?" The Oracle Software Downloads page on the Oracle website gives us the following information: All software downloads are free, and each comes with a development license that allows you to use full versions of the products only while developing and prototyping your applications. You can buy Oracle products with full-use licenses at any time from the online Oracle Store or from your Oracle sales representative.

What this means in practice is that you can download for free any piece of Oracle software today, for example, Oracle Database Enterprise Edition, play with it while developing and prototyping your applications, and then pay for that piece of software only if you decide to use it in your final product. If you are not still satisfied with it and would like to keep using an Oracle database for free even in a final product, consider Oracle Database Express Edition—a lightweight Oracle database that is free to develop, deploy, and distribute.

All Oracle Database 10g software is available for download from Oracle Technology Network (OTN). For Oracle Database 10g Enterprise/Standard Editions, you start by visiting the following OTN page:

http://www.oracle.com/technology/software/products/database/oracle10g/index.html

For Oracle Database 10g Express Edition Editions, visit the following OTN page:

http://www.oracle.com/technology/software/products/database/xe/index.html

Oracle Database software is easy to install regardless of which edition of the database you choose. The Installing Oracle Database Software section in Appendix A provides the basic steps to install Oracle Database 10g on both Windows and UNIX systems. For detailed information on how to install Oracle Database software, see Oracle documentation: the Oracle Database Installation Guide for your operating system platform. Oracle documentation is available from the documentation section of the OTN website at:

http://www.oracle.com/technology/documentation/index.html

Besides the detailed installation steps specific to your operating system, the Oracle Database Installation Guide provides information on the issues to consider before installing the software and discusses platform-specific post-installation tasks that must be performed before you start using the database. It is highly recommended that you familiarize yourself with this information before installing Oracle Database software.

PHP Considerations

If you have already got your feet wet with PHP, you probably know that PHP is a server‑side scripting language, which means that PHP code is executed on a web server. Therefore, before you install PHP, you must have a web server installed and working in your system.

Apache HTTP Server

Although PHP has support for most of the web servers worth mentioning, including Microsoft Internet Information Server, Personal Web Server, Netscape, and iPlanet servers, and many others, Apache/PHP remains the most popular combination among developers. Oracle itself incorporates open-source Apache technology in some of its products. For example, Oracle HTTP Server 10g, the web server component of Oracle database, is based on the proven technology of both Apache 1.3 and Apache 2.0.

The Apache HTTP server is distributed under the Apache License, a free software/open-source license authored by The Apache Software Foundation (ASF). The current version of the Apache License can be found on the licenses page of the Apache website at:

http://www.apache.org/licenses/

You can download the Apache HTTP server from the download page of the Apache website at:

http://httpd.apache.org/download.cgi

This page also contains a link to the Apache HTTP Server Documentation index, which in turn contains a link to the Compiling and Installing document.

Note

Oracle recommends that you install a web server on another machine but on the same network as the database server. However, for simplicity, you can install both an Oracle database server and a web server, which will communicate with that database server, on the same machine.

For instructions on how to install Apache in your system, see the Installing Apache HTTP Server section in Appendix A.

Why PHP 5

Although PHP 4 is still very popular, the examples provided throughout this book assume that you will use PHP 5. We decided on PHP 5 because it offers a lot of new features and improvements that are not available in its predecessors.

One of the significant improvements in PHP 5 is the new object model, which allows you to leverage the power and flexibility of object-oriented programming in a number of useful ways. In particular, you can take advantage of interfaces, abstract classes, private/public/protected access modifiers, static members and methods, exception handling, and other features that are usually found in other object-oriented languages, such as Java, but were not available in prior releases of PHP.

Note

Chapter 5 Object-Oriented Approach explains in detail how all these new object features of PHP 5 can be used in PHP/Oracle development.

Another significant improvement in PHP 5 is its renewed XML support; SAX, DOM, and XSLT extensions are now based on the single library, namely libxml2, thus allowing for better interoperability between the XML extensions.

Note

Chapter 8 XML-enabled Applications discusses how to build and deploy robust XML-enabled PHP applications on Oracle Database.

While PHP 4 lacked native SOAP support, PHP 5 introduces the SOAP extension. Written in C, this built-in extension can serve as a good alternative to the PEAR::SOAP package.

Note

Chapter 9 Web services discusses how to create a SOAP web service using PHP 5's SOAP extension, on top of the Oracle database.

Obtaining PHP

PHP is licensed under the PHP License, a BSD-style license. For more information, visit the License Information page of the php.net website at:

http://www.php.net/license/

The current recommended releases of PHP are available for download from the downloads page of the php.net site at:

http://www.php.net/downloads.php

From this page, download the latest stable release of PHP 5 and then follow the installation steps—provided in the Installing PHP section in Appendix A. For further assistance along the way, you may consult the Installation and Configuration manual available on the php.net website at:

http://www.php.net/manual/install.php

Alternatively, you can read the install.txt file that is shipped with PHP.

Making PHP and Oracle Work Together

As you will learn in Chapter 2 PHP and Oracle Connection, there are several ways in which PHP can interact with Oracle. However, it is important to realize that most of these ways are based on using APIs provided by the PHP OCI8 extension, which is not enabled by default. To enable it in your existing PHP installation, you have to perform the following general steps:

  • Install the Oracle client libraries needed by the PHP OCI8 extension.

  • On UNIX-like systems, recompile PHP to support the OCI8 extension. On Windows, uncomment the OCI8 extension line in php.ini.

  • Restart the web server.

As you can see from the above, you have to install the Oracle client libraries before you can enable the OCI8 extension in your existing PHP installation.

Oracle Instant Client

Consider Oracle Instant Client, a package containing the Oracle client libraries required to run OCI, OCCI, and JDBC-OCI applications. Note that Oracle Instant Client comes with a free license for both development and production environments.

Note

If you have both the database and web server installed on the same computer then you already have all the required Oracle components—no Instant Client is required. However, in this case you still have to explicitly enable the OCI8 extension in your PHP installation.

You can download a copy of Oracle Instant Client specific to your platform from the Instant Client web page on OTN at:

http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html

Looking through this page, you may notice that there are in fact several Instant Client Packages available for download. You should choose the Basic Instant Client Package. It includes all the files required to run OCI, OCCI, and JDBC-OCI applications.

For a detailed instruction on how to install Oracle Instant Client and then enable the OCI8 extension, see the Enabling the OCI8 Extension in an Existing PHP Installation section in Appendix A.

Zend Core for Oracle

As you learned in the previous sections, making PHP and Oracle software work together is a process involving many tedious tasks. You have to find, download, install, deploy, and configure a number of software pieces to get the job done.

You might significantly speed up and simplify the whole process by taking advantage of Zend Core for Oracle, a pre-built stack that delivers a rapid development and deployment foundation for Oracle-driven PHP applications. In particular, Zend Core for Oracle saves you the trouble of performing the following steps:

  • Installing PHP and configuring the web server to use it

  • Installing Oracle Client libraries

  • Configuring the PHP installation to work with Oracle

Moreover, Zend Core for Oracle provides the following features:

  • Updated OCI8 driver re-factored for reliability and performance

  • GUI-based tool that makes it easier to change the PHP configuration

  • Easy access to PHP, PEAR and Zend Core for Oracle documentation

The installation steps in the Installing Zend Core for Oracle section in Appendix A will help you install this tool in your system.

Using Oracle SQL*Plus

After an Oracle Database server and a web server with activated support for PHP, which has the OCI8 extension enabled, are installed and working correctly, you can proceed to developing PHP/Oracle applications. However, before moving on, you might want to install another piece of software in your system in order to make your development work easier.

Why Use SQL*Plus in PHP/Oracle Development?

While using the PHP OCI8 extension you can execute any valid SQL statement or PL/SQL block against an Oracle database from your PHP script, it is generally not a good idea to do so when you need to perform a database administration task or create a database object.

Note

The fact is that these kinds of tasks are usually performed with the help of batch scripts executed against the database. However, executing batch scripts from PHP is a tricky task and so you might want to use a more appropriate tool for this purpose.

The simplest way to perform the above tasks is to use an SQL command-line tool that will allow you to execute SQL statements and PL/SQL blocks in a batch or individually, and display the results of each statement once it has been executed.

Consider Oracle SQL*Plus—an interactive and batch query command-line tool that is installed by default with every Oracle Database installation. Most of the examples in this book assume that you will use SQL*Plus when it comes to performing database administration or creating database objects. Since SQL*Plus is installed by default when installing the Oracle Database software, you don't need to install it again if you are going to use a local database. Otherwise, you might take advantage of SQL*Plus Instant Client—a stand-alone SQL*Plus command-line tool that allows you to communicate with a remote database.

This section discusses how to use SQL*Plus to interact with an Oracle database. The Installing SQL*Plus Instant Client section in Appendix A describes the installation steps to install SQL*Plus Instant Client in your system, assuming you will work with a remote database. For detailed information on how to use Oracle SQL*Plus, see Oracle documentation: SQL*Plus User's Guide and Reference.

Note

From now on, we won't distinguish between SQL*Plus and SQL*Plus Instant Client—references will be made to SQL*Plus.

Connecting to a Database with SQL*Plus

To connect to a database with SQL*Plus, you specify the database using an Oracle Net connection identifier. For example, you might use the following Easy Connect syntax to connect as hr/hr to the orcl database running on yourmachine:1521 within yourdomain:

sqlplus hr/hr@//yourmachine.yourdomain:1521/orcl

However, if you are connecting to a local database you might use a simpler syntax:

sqlplus hr/hr

Note

HR/HR is a demonstration database schema that is installed by default when you choose the Basic Installation and Create Starter Database options during Oracle Database installation. Some examples in this book use the default tables from this database schema.

If your HR/HR database schema is locked, you will see the following error message:

ERROR:
ORA-28000: the account is locked

To unlock the HR database account, first connect as SYSTEM database user:

sqlplus system/system_pswd@//yourmachine.yourdomain:1521/orcl

And then enter the following SQL statement at the SQL> prompt:

ALTER USER HR IDENTIFIED BY hr ACCOUNT UNLOCK;

Now, to connect as hr/hr, you don't have to open another SQL*Plus session. From an existing SQL*Plus session, enter a CONNECT command as follows:

CONNECT hr/hr@//yourmachine.yourdomain:1521/orcl;

To connect to the database, all the above examples employ the Easy Connect Naming feature that first appeared in Oracle Database 10g Release 1. You may find this method very convenient because it enables you to connect to a database server without first configuring net service names.

Note

Warning: the Easy Connect Naming feature can be used only in a TCP/IP environment.

Alternatively, you might use the Local Naming method to connect to a database. The local naming method uses a localized configuration tnsnames.ora file to store net service names and their connect descriptors. So, you first create a connect descriptor in the tnsnames.ora file and then you can refer to that descriptor by name.

Normally, the tnsnames.ora file can be found in the [ORACLE_HOME]\network\admin directory on the client machine. However, if you are connecting to a remote database by means of SQL*Plus Instant Client, you should take into account that neither SQL*Plus Instant Client nor Basic Instant Client comes with a tnsnames.ora file and so you will have to create it yourself. For example, if you have installed the Instant Client to the c:\instantclient_10_2 directory, create the c:\instantclient_10_2\network\admin\ directory and then create a plain-text tnsnames.ora file in that directory.

Next, set the ORACLE_HOME environment variable to c:\instantclient_10_2\. Finally, create a connect descriptor in the tnsnames.ora file. For example, you might create the following connect descriptor for the orcl database running on yourmachine:1521 within yourdomain:

ORCL10g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourmachine.yourdomain) (PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

Once you have saved this entry in the tnsnames.ora file, you can then connect to the specified database with SQL*Plus as follows:

sqlplus hr/hr@orcl10g

For further discussion of how to use the Easy Connect Naming and Local Naming method, see the Connecting to a Database section, which discusses these Oracle Net Services features from a PHP developer standpoint.

Running Scripts in SQL*Plus

As you saw in the previous subsection, executing an SQL statement in SQL*Plus is very simple. To do this, you simply enter the SQL statement at the SQL> prompt. For example, you might enter the following statement at the SQL> prompt to obtain the current date from the database:

SELECT SYSDATE FROM DUAL;

Once you press the Enter key, the above should produce a result that might look like the following:

SYSDATE
---------
20-MAR-06

However, note that entering statements at the SQL> prompt manually may be an acceptable solution when dealing with a single statement that you are not going to use frequently in the future. Otherwise, you might want to save the statement or statements in a script and then run this script when necessary.

To create a script in SQL*Plus, you can use your operating system's default text editor. To do this, you simply enter the EDIT command followed by the name of the script file you want to create or edit. For example, if you want to create a dbdate.sql script, you have to enter the following command at the SQL> prompt:

EDIT dbdate

As a result, the dbdate.sql file is opened in the editor so that you can insert the desired statement or statements into the script. For simplicity, you might insert into the dbdate.sql script the same SQL statement as in the above example:

SELECT SYSDATE FROM DUAL;

Once you have saved the dbdate.sql script with the editor, you can then run it by entering the START command followed by the name of the script as follows:

START dbdate

This should produce the same output as in the previous example.

Now, if you want to edit the dbdate.sql script without leaving SQL*Plus, you can do it with the help of the EDIT command, as shown below:

EDIT dbdate

This should open the dbdate.sql file with the editor for editing. You can edit the script as needed and then save the changes and quit the editor. For example, you might replace the existing SQL statement in the dbdate.sql script with the following:

SELECT TO_CHAR(SYSDATE, ‘MM-DD-YYYY HH:MI:SS') FROM DUAL;

So, the next time you run the dbdate.sql script with the START command, it will produce output that looks like:

TO_CHAR(SYSDATE,'MM
-------------------
03-20-2006 04:50:07

Here, the script outputs the current date and time.

No doubt you have realized, the dbdate.sql script is a toy example. In a real-world situation, you might need to create a script containing more than one statement. As noted previously, using batch scripts can be very handy when you need to perform database administration tasks or create database objects.

Putting It All Together

Now that you have a rough idea of what each piece of software required for PHP/Oracle development is used for, let's look at how all these components interact with each other to get the job done. Let's summarize what each piece does individually to better understand its role in the entire architecture.

The following table gives a summary description for each piece of software you need to have installed to get started.

Software component

Functional description

Oracle Database Server software

Provides concurrent access to the database data while still delivering high performance. Also prevents unauthorized access to the data and provides efficient mechanism for backup and recovery.

Oracle Database

Provides physical and logical structures in which user and control data is stored. A database is mounted and opened by an instance (may be more than one instance in the case of using Real Application Clusters).

Apache HTTP Server

Provides secure, efficient, and feature-rich web server services. Provides the ability to incorporate new functionality in the form of third-party modules.

PHP

Provides a way to add dynamic content into HTML. Supports a wide range of relational databases.

Oracle Instant Client

Provides the Oracle client libraries that allow client software to interact with an Oracle Database server.

Zend Core for Oracle

Contains all the client-side components required for PHP/Oracle development. Specifically, it contains an Apache HTTP server, PHP, and Oracle client libraries. All the components are configured so that you can quickly start developing PHP/Oracle applications.

While the above table gives a short functional description of each software component needed to start developing PHP/Oracle applications, the following figure illustrates how all these components fit together, giving you a high-level view of PHP/Oracle interactions.

As you can see in the above figure, the web server sends connection requests to the Oracle Net listener, a process that listens for client connection requests, rather than to the Oracle database server directly. The listener in turn forwards those requests to the database server. But what is not shown in the figure is that the web server and Oracle database server start communicating directly with one another once a connection is established.