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

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.

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.

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:

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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:

Moreover, Zend Core for Oracle provides the following features:

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

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.

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.

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

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.

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.

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.

By specifying localhost as the host name in the connect descriptor in the above example, you tell oci_connect that the database you are connecting to is local. In a real‑world situation, however, you might need to establish a connection to a remote database. To do this, you can use either the IP address or network name of the host machine on which the Oracle Net listener is running. For example, if your Oracle database server resides on the computer whose network name is MyServer and IP address is 192.168.100.1, you might use either HOST = MyServer or HOST = 192.168.100.1 in the connect descriptor.

To make using connect descriptors easier, you might define them in the tnsnames.ora file, which is normally located in the ORACLE_HOME/network/admin directory on the client machine. Once you have a connect descriptor defined in the tnsnames.ora file, you can then refer to that descriptor by name. Doing so saves you the trouble of defining descriptor connections in your application code. For example, you might define the following connect descriptor in tnsnames.ora:

ORCL10gR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Once you have the above entry saved in the tnsnames.ora file, you can then pass the name that maps to the connect descriptor, namely, ORCL10gR2, as the third parameter to oci_connect, rather than passing the string representing the connect descriptor itself:

$dbDescName = "ORCLR10gR2";
$dbConn = oci_connect($usr,$pswd,$dbDescName);

It is important to note that you may not pass the third parameter to oci_connect at all if the database you are connecting to is local.

For TCP/IP environments, you might make use of Oracle Database 10g's Easy Connect Naming Method feature, which can eliminate the need for service name lookup in the tnsnames.ora file. We touched on this feature in the Using Oracle SQL*Plus section earlier in this chapter, when discussing some of the ways you can connect to a remote database with SQL*Plus. If you recall, the Easy Connect Naming method enables you to connect to an Oracle database server by simply providing the database user/password combination and the server computer's host name along with two optional parameters, namely, the service name of the database and the port on which the listener will accept connections. Note, however, that you cannot omit the service name when it comes to defining the third parameter of oci_connect:

$dbEasyConn = "//MyDbServer/orcl";
$dbConn = oci_connect($usr,$pswd,$dbEasyConn);

To specify an optional port, you use the following syntax:

$dbEasyConn = "//MyDbServer:1521/orcl";

It is important to realize that orcl in the above examples is not the name of a connect descriptor defined in the tnsnames.ora file, but the service name of the database.

As you can see, the Easy Connect Naming method provides an easy-to-use syntax enabling you to connect to a database server without any configuration.