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

In Chapter 28 we introduced interacting with Oracle Database XE primarily as a DBA. Since your job may involve wearing many hats, you will most likely interact with Oracle Database XE not only as a
DBA but also as a developer and an ad hoc query user. As a result, Oracle provides many different ways to interact with the database, depending on your role.
In that same chapter, we showed you how to connect to Oracle Database XE using SQL*Plus as an administrator. In this chapter, we’ll review SQL*Plus from a user’s point of view, as well as how to specify the database connection information if you are using SQL*Plus from a remote workstation using the components of an Oracle Database XE client installation.
The next tool we’ll cover is Oracle SQL Developer. You can use it to establish many connections to a single database (including Oracle Database XE), one connection to many different databases, or even many connections to many different databases. In a graphical environment, you can browse, edit, delete, or create database objects, run one SQL statement, and edit and debug PL/SQL proce- dures and functions. In addition, SQL Developer provides predefined reports against the objects in your database that are also easy to customize for your environment.
For the application developer, we introduce Application Express, more commonly known as APEX. APEX gives the application developer and the end user the power to create a Web-enabled application with no programming knowledge or experience and quickly build a Web application that leverages all of the power of Oracle Database XE, such as concurrency, transactional integrity, and robust security built-in to the database. The Web administration interface to Oracle Database XE is an APEX application.

XE Home Page

We introduced the Oracle Database XE home page in Chapters 26 and 27. This interface may be all that a DBA and an application developer need. In your DBA role, you can monitor the database and administer user accounts. As a developer or a user, you have basic query functions and object-browsing capabilities. Plus, if you are using a nonadministrative account, you can create APEX applications by clicking the Application Builder icon on the Oracle Database XE home page. The XE home page’s functionality was created using APEX. Later in this chapter in the section “Using Application Express,” we’ll show you how to get the latest version of APEX.

Installing the Oracle Database XE Client

If you have one or more users who need to access your Oracle XE database from workstations other than where Oracle Database XE is installed, you need to install the Oracle Database XE client soft- ware. The hardware and software requirements for the Oracle Database XE client are identical to the server requirements. This may seem puzzling at first. If the remote workstation is not installing the

501

database itself, why do you need the same amount of memory and disk space? The primary reason is that the remote workstation is most likely running many other applications that consume the same amount of memory that a full database installation would consume.
See Chapter 26 for the Oracle-recommended hardware and software requirements. These recommended requirements include a recent version of the Windows or Linux operating system along with a cookie-enabled recent version of Internet Explorer, Netscape Navigator, Firefox, or Mozilla; all modern browsers should work fine, however.

Installing the Windows Client

Navigate to the Oracle Web site download area for Oracle Database XE, http://www.oracle.com/ technology/software/products/database/xe/index.html, and follow the links to download the Windows version: OracleXEClient.exe. Registration for Oracle Technology Network (OTN) is required but free and gives you access to many other resources on http://otn.oracle.com.
Double-click the file OracleXEClient.exe, and the Oracle Install Wizard for Oracle Database XE starts. The only parameter you can adjust during the installation is the location for the executable files (see Figure 29-1).

Figure 29-1. Specifying the Oracle Database XE client installation location

The client installation files require approximately 75MB of disk space. Note that a remote Oracle Database XE installation is accessible from any Web browser on the network even without a client installation. You need a client installation if you need to use ODBC, JDBC, or Oracle Services for Microsoft Transaction Server (OraMTS) on your client. If all of your interaction with Oracle Database XE will be via a Web browser, use a URL such as http://example.com:8080/apex/ to access an Oracle Database XE installation on the server example.com.
If you have the client software installed and you want to access Oracle Database XE from a Windows application that supports ODBC, you can set up a connection using the Microsoft ODBC Data Source Administrator, accessible from the Control Panel by double-clicking the Administrative Tools icon. Figure 29-2 shows the Oracle in XE ODBC driver when you create a new data source.

Figure 29-2. Creating a data source using the Oracle Database XE ODBC driver

From Microsoft Excel, for example, you can see the contents of a table or the results of a query using the new ODBC connection.

Installing the Linux Client

Navigate to the Oracle Web site download area for Oracle Database XE, http://www.oracle.com/ technology/software/products/database/xe/index.html, and follow the links to download the Linux version of Oracle Database XE. Registration for OTN is required but free and gives you access to many other resources on http://otn.oracle.com. The Linux version will run fine on the distribu- tions mentioned in Chapter 26. The installation may work on other Linux distributions, but proceed at your own risk.
There are two installation files available, oracle-xe-client-10.2.0.1-1.0.i386.rpm and oracle-xe-client_10.2.0.1-1.0_i386.deb. As you might expect, the first one works with all Linux distributions except for Debian, and the second one is packaged specifically for the Debian package manager.
To begin the installation process, locate the rpm file you just downloaded, and run the rpm
command as follows:

[root@tsm01 ]# rpm -ivh oracle-xe-client-10.2.0.1-1.0.i386.rpm
Preparing... ########################################### [100%]
1:oracle-xe-client ########################################### [100%] Executing Post-install steps...
[root@tsm01 ]#

The installation for a Debian environment is similar:

$ dpkg -i oracle-xe-client_10.2.0.1-1.0_i386.deb

A Linux client installation provides many of the same features as a Windows client installation, such as ODBC drivers. In addition, a client installation provides the shell scripts oracle_env.csh (for C shell or tcsh shell) and oracle_env.sh (for Bourne, Bash, or Korn shell) to set up the Oracle-related environment variables. You can run these scripts on an as-needed basis or incorporate them into your login or profile files.
The Linux client requires approximately 70MB of disk space.

Using SQL Command Line

In Chapter 27, we introduced SQL*Plus, also known as the SQL command line, to perform ad hoc queries as well as to create and run PL/SQL procedures. SQL*Plus is included in Oracle Database XE client installations as well. Here is an example where you establish the environment variables on a client workstation and connect to Oracle Database XE on the server phpxe:

[oracle@tsm01 bin]$ cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/bin
[oracle@tsm01 bin]$ . ./oracle_env.sh
[oracle@tsm01 bin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 13 22:36:04 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect system@phpxe
Enter password:
Connected.
SQL> select host_name, version, edition from v$instance;

HOST_NAME VERSION EDITION
------------------- ---------------- ---------------- phpxe 10.2.0.1.0 XE

SQL>

A couple of things worth noting. First, if you’re new to Linux, the “double dot” notation in the

second line of the previous example is not a typographical error. The first dot indicates that any envi- ronment variables set in the script will still be set when the script is done; the second dot (and slash) explicitly executes the shell script from the current directory just in case your path variable doesn’t include the current directory.
Also, note that you do not specify the service name in this example. For a default installation of the Oracle Database XE client, the default service name is xe, which not surprisingly is the default service name for an installation of the Oracle Database XE server components. The default listener port number is 1521. If you want to connect to another database residing on server xp64a with a service name of ora_ee listening on port 1523, you would connect to this database from SQL*Plus as follows:

SQL> connect system@xp64a:1523/ora_ee

Using SQL Developer

If your primary job role is a DBA, you will most likely spend most of your time using SQL Developer to access Oracle Database XE, or any version of Oracle version 9.2 or later. SQL Developer provides these key features:

Creating and editing database objects: You can connect to any database and create, edit, view, or delete objects to which you have permission. You can extract the DDL for any object, including tables, views, sequences, functions, procedures, and so on.

Running SQL commands: You can use SQL Worksheet to create and execute SQL, PL/SQL, and
SQL*Plus commands.

PL/SQL editing and debugging: You can create and edit PL/SQL anonymous procedures as well as functions, procedures, and packages. Code formatting makes your PL/SQL code more read- able, and breakpoints make it easy to step through your code line by line when you are debugging a PL/SQL procedure.

Reporting: SQL Developer includes many predefined database and object reports that can easily be customized for your environment.

To get SQL Developer, navigate to http://www.oracle.com/technology/software/products/ sql/index.html. You can run SQL Developer on Windows, Linux, or even Mac OS X. Each platform has two links; you can download the smaller file if you already have JDK 1.5 installed. Extract the files to a directory with at least 125MB of free disk space. You can put the extracted files anywhere, as long as you preserve the directory structure—no installer required. This applies to both Windows and Linux.
In a Windows environment, locate the file sqldeveloper.exe in the directory sqldeveloper and double-click it; that’s it! You might find it useful to create a desktop shortcut for sqldeveloper.exe to make it even easier to start the next time by right-clicking sqldeveloper.exe then selecting Send To
➤ Desktop (Create Shortcut).
For Linux, the process is very similar. Locate the file sqldeveloper in the directory sqldeveloper
and start it this way:

[oracle@tsm01 bin]$ sh sqldeveloper

Most if not all Linux GUIs support desktop shortcuts to this command just as in a Windows environment.
In the following example, you start SQL Developer (on either Windows or Linux) and create a connection to an Oracle Database XE instance on the same workstation where you started SQL Developer. On the left-hand side of the window, right-click Connections and select New Database Connection. Enter the following values in the dialog box that appears:

Connection name: orcl_hr (or any name that makes it easy to remember the local database connection for this user)

Username: HR (or any username that exists for this database)

Password: the password you assigned to the username

Hostname: localhost (i.e., the same server on which you are running SQL Developer)

SID: xe (the default Oracle Database XE instance name)

Select the Save Password checkbox if you want SQL Developer to remember the user’s password the next time you start SQL Developer. Click Test if you want to make sure that the connection works; otherwise, go ahead and click Connect to establish a permanent connection to the database and begin browsing database objects. You only need to set up this connection once; SQL Developer saves your connection definitions and makes them available on the left-hand navigation pane every time you start SQL Developer. In Figure 29-3, you connect as the user HR and browse the structure of the EMPLOYEES table.

Figure 29-3. Browsing database objects in the HR schema

Using Application Express

APEX, formerly known as HTML DB, is a rapid Web application development tool requiring little or no programming experience. The applications you develop with APEX connect to any Oracle data- base, including Oracle Database XE, and therefore automatically inherit the scalability and security features inherent in an Oracle database.
APEX is completely menu driven. You use application wizards to create a new application or
even convert an old application to a more robust platform. For example, you can easily import a single-user Microsoft Excel spreadsheet into APEX to not only make it a Web application available to any user with a Web browser, but also to import the data into Oracle Database XE. This makes your data more available and reliable; instead of residing on a user’s local hard disk, the data resides in Oracle Database XE where it is backed up and available to a wider user audience.
You can develop APEX applications at the Oracle Database XE home page; however, APEX is updated more often than Oracle Database XE, so to get the latest features available in APEX, navigate to http://www.oracle.com/technology/products/database/application_express/index.html and click the download link to get the latest version.

■Note As of the writing of this book, the current stand-alone version of APEX was 3.0, and the version of APEX
integrated with Oracle Database XE was 2.1.

Even though you cannot integrate a more recent version of APEX with your installation of Oracle Database XE, you can easily create an application in the current version of APEX installed in a different database and migrate the application to Oracle Database XE, and vice versa, with a high level of compatibility.
In the example starting with Figure 29-4, you create a simple APEX application to maintain the LOCATIONS table. Connect to Oracle Database XE as the HR user and click the Application Builder link on the Oracle Database XE home page. You’ll see the Create Application page shown in Figure 29-4. As you proceed through the development of your application, the current step name in the process is highlighted on the left-hand side of the page.

Figure 29-4. Create Application start page

Since you’re going to create an application using an existing table, accept the default selection, Create Application. Click the Next button. On the next page, specify an application name and number. In this example, the application name is Location Maintenance and the application number is 100. Since you’re creating a new application, select the From Scratch radio button. Click the Next button.

On the Pages phase of Create Application shown in Figure 29-5, select the Tabular Form radio button to use the default template for editing rows in the LOCATIONS table. In the Table Name text box, enter LOCATIONS as the table you will maintain, or select the LOCATIONS table using the list box selector to the right of the text box.

Figure 29-5. Create Application Page Type page

Click the Add Page button to add this page to the list of application pages. The new page appears at the top of the Create Application page in the page list. Click the Next button. On the Tabs step, select the No Tabs radio button and click the Next button. On the Shared Components page—you don’t have any shared components (yet!)—select the No radio button and click Next.
On the Attributes step shown in Figure 29-6, you can specify how you will authenticate with
this application as well as what language preferences you want. You can use the existing database authentication (your Oracle Database XE username and password) or another layer of authentication provided by Application Express. In this example, accept the default, Database Account. Click Next.

Figure 29-6. Create Application attributes page

In the User Interface step, choose a theme for your application—in other words, how you want the colors to look on the page as well as the general look and feel. In this example, select Theme 9 and click Next.
The next page, the Confirm step, summarizes the attributes you have selected so far and gives
you one more chance to revise them. You also have the option to save this application definition as a template for future applications by selecting the checkbox at the bottom of the page. Since every- thing looks fine, click the Create button. APEX builds your application and leaves you at the home page for the application: Application 100.
Click Run Application to start it. Enter a username and password on the next page; since you’re going to edit the LOCATIONS table, enter the HR username and password since you know that the HR user owns the LOCATIONS table. However, you can supply any username that can view and edit the LOCATIONS table.
Figure 29-7 shows the LOCATIONS table maintenance page. You can edit, update, or delete existing locations. To add new rows, click the Add Row button. When you are done making changes and want to save them to the database, click the Submit button.

Figure 29-7. Using the LOCATIONS maintenance application

Using PHP

Last, and certainly not least, you use PHP and your Apache Web server to access your Oracle database, using PHP scripts with calls to functions from Oracle’s OCI8 client library. We provide an introduc- tion to PHP’s Oracle functionality in Chapter 32 and provide numerous examples throughout the rest of the book.
If your primary role is as a PHP developer, you will most likely be using the PHP/Oracle client libraries to connect to Oracle Database XE. However, don’t forget about the other methods we presented earlier in this chapter. If something does not work right when your PHP code is trying to access Oracle, you will most likely try the same operation in SQL Developer or even SQL*Plus to help you narrow down the problem.
In Chapter 27, we enabled access to Oracle Database XE by removing a single semicolon in your
PHP configuration file php.ini:

extension=oci8.so

In Chapter 32, we cover the methods you will use as a PHP developer to connect to Oracle Data- base XE, either as a native Oracle database call or via an abstraction layer to make it easy to port your PHP applications to another database platform if required by your target environment.

Summary

As a PHP developer, you will most likely use PHP to interact with Oracle Database XE, but there will be many times when you need to wear your DBA hat and manage the database using SQL*Plus and SQL Developer. As a PHP Web application developer, you may also find that Oracle’s APEX may fit the bill for some of your Web users to complement your PHP-based Web applications that access the same Oracle database. As a result, it’s good to know what tools you have at your disposal to browse, manage, and develop applications against your Oracle database.
Now that you have a firm grasp on how to install, manage, and query Oracle Database XE, it’s time to start digging in to the nuts and bolts under the hood. The next chapter gives a solid overview of how you can partition many applications within a single database using schemas as well as distributing database data for performance and manageability using tablespaces. In addition, we’ll drill even deeper into Oracle by enumerating all of the data types you can store in an Oracle database and showing you how to use them.