Oracle, Mac OS X, and open source technologies such as PHP and gqlplus make for an enjoyable, productive, and stable development environment. Here's how to set one up.

With Apple's move to a UNIX base with Mac OS X, many developers have been excited about the prospects of a development platform that combines legendary ease of use with a rock-solid foundation. Considering that Oracle has a developer release of the Oracle9i Database Release 2 for OS X 10.2 (Jaguar) and that OS X is now a fully supported platform for Oracle Database 10g, things are looking even brighter.

One of the most exciting aspects of OS X as a development platform is support for thousands of open source tools and frameworks that have been developed for UNIX-like systems. The Apache Web server and the PHP scripting language are two of the most popular and robust open source tools available to developers, and using them on OS X couldn't be easier. PHP is a great language to use with Oracle, as Sean Hull's "Get Started with Oracle and PHP" illustrates.

If you are a developer who works with Oracle applications and has been itching for an alternative to Windows or Linux—or if you are an experienced OS X user and are looking at basing your next project on Oracle—this article is for you. It assumes that you are comfortable working in the shell environment and have a working installation of OS X 10.3 (Panther) or 10.2 (Jaguar), including the developer tools and an administrator account.

Though the developer release was originally made available for 10.2.x Server (Jaguar), I have tested it with the following OS X Client versions: 10.2.6, 10.2.8 and 10.3.2 (Panther). The only difference between the installation on Jaguar and on Panther is that Apple has already activated PHP in the Apache configuration file in Panther. You will need to edit the configuration file to activate PHP if you are installing on Jaguar. Also, the default user shell on Jaguar is tcsh, while Panther uses bash. The instructions below are for the bash shell. To use bash, type

    shell> exec bash

from your tsch prompt.

At the end of the article, you should have a working Oracle instance and the ability to use PHP's OCI interface to communicate with Oracle via your PHP scripts. The article also demonstrates how to set up gqlplus, a wrapper to Oracle's SQL*Plus client that gives you the command-line editing, completion, and history facilities you may be used to in your shell environment.

Before beginning the install, you need to download the Oracle9i R2 Developer Release edition for Jaguar. It weighs in at a hefty 345MB, so be sure you have a fast connection or a lot of time before you begin. While you are downloading, head over to http://www.php.net/get/php-4.3.4.tar.bz2/from/a/mirror to retrieve version 4.3.4 of the PHP source code. Although Apple bundles a version of PHP with OS X, you need to compile your own copy with OCI support to work with Oracle.

Installing Oracle

When you have downloaded the database, you will need to create a user and a group to own the database files and the daemon processes. Open a terminal window, and type the following commands, entering your password when prompted:

    shell$ sudo mkdir /Users/oracle
    shell$ sudo nicl . -create /groups/dba passwd "*"
    shell$ sudo nicl . -append /groups/dba gid 1521
    shell$ sudo nicl . -append /groups/dba users oracle
    shell$ sudo nicl . -append /groups/admin users oracle
    shell$ sudo nicl . -create /users/oracle
    shell$ sudo nicl . -append /users/oracle gid 1521
    shell$ sudo nicl . -append /users/oracle uid 1521
    shell$ sudo nicl . -append /users/oracle shell /bin/bash
    shell$ sudo nicl . -append /users/oracle home /Users/oracle
    shell$ sudo nicl . -append /users/oracle realname "OracleDB"
    shell$ sudo chown oracle:dba /Users/oracle

You will notice that the user and group IDs are set to 1521, which is the default TCP port for the Oracle listener. I follow this convention for user accounts that run daemon processes—if you already have a user with 1521 as a user ID or want to follow a different convention, modify the commands accordingly. Now set the password for the Oracle user:

    shell$ sudo passwd oracle

Next move the files you downloaded to the Oracle user's home directory and change their ownership. The macosx_920_dev_rel.tar.gz file may have been decompressed into macosx_920_dev_rel.tar by your browser. If not, first decompress it:

  shell$ gunzip macosx_920_dev_rel.tar.gz

Then move it and the php source to the Oracle user's home directory:

  shell$ sudo mv macosx_920_dev_rel.tar php-4.3.4.tar.bz2 /Users/oracle

And make sure they're owned by the Oracle user:

   shell$ sudo chown oracle:dba /Users/oracle/*

The majority of the remainder of the installation will be completed as the Oracle user, so open another terminal window and type

    shell$ sudo su - oracle

to switch to the Oracle user.

The next step is to unpack the Oracle developer release and put it in place. Starting from the Oracle user's home directory, enter the following commands:

    shell$ tar xf macosx_920_dev_rel.tar
    (this will take a while, and at the end you will be left with an
    'orahome' directory)
    shell$ mkdir Source
    shell$ mv orahome Source/9iR2
    shell$ cd Source/9iR2
    shell$ ln -s lib dylib

Your ORACLE_HOME is /Users/oracle/Source/9iR2. If you want to move it elsewhere, be sure to set up symlinks so that the Oracle binaries can find the libraries they need.

To create a database and run Oracle properly, you must first customize the oracle user's environment, by setting a few environment variables and adjusting default process and memory limits. Create a file in the Oracle user's home directory called .bashrc.oracle, with the following contents:

    # .bashrc.oracle - set up ENV for oracle users

    # Change ORACLE_SID, HOST_NAME, and DOMAIN_NAME below to suit your
    # environment
    export ORACLE_SID=OSXDEV
    export HOST_NAME=host
    export DOMAIN_NAME=example.com

    ## You shouldn't have to change anything below here ##

    # Root of All Oracle installation
    # If you change this, set up symlinks so Oracle binaries can find libs
    export ORACLE_HOME=/Users/oracle/Source/9iR2
    # Same as ORACLE_HOME
    export SRCHOME=$ORACLE_HOME
    # Add Oracle binaries to path
    export PATH=$ORACLE_HOME/bin:$PATH
    # Required to find dynamic libraries
    export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$DYLD_LIBRARY_PATH

    # Set shell user limits
    ulimit -c unlimited
    ulimit -d unlimited
    ulimit -s 65536

    # Fix delete in SQL*Plus
    stty erase ^H

    # Alias for ease of movement
    alias cdo="cd $ORACLE_HOME"

Note that the beginning of the file sets several environment variables: ORACLE_SID, HOST_NAME, and DOMAIN_NAME. Change their values to suit your environment. I try to keep my development environment as similar to my production environment as possible, so I would change my ORACLE_SID to be the same as the ORACLE_SID in the production system, perhaps adding DEV to the end to indicate that it is a development instance. The HOST_NAME and DOMAIN_NAME variables are used by the script that creates the database and the following networking configuration files—if you do not have a DNS name for your computer, you should be able to use the default value or change it to a preferred name without adverse effects.

When your configuration is complete, create or edit a file in the Oracle home directory called .bashrc and add the following line:

    . ~/.bashrc.oracle

This will allow subsequent logins as the Oracle user to automatically acquire the environment from the .bashrc.oracle. The commands are stored in a separate file, so that other users on the machine who need to use Oracle can add the same line in their .bashrc file.

If you have created the .bashrc file, you will probably also want to adjust your path, by including this line:

    export PATH=$HOME/bin:/usr/local/bin:/usr/local/sbin:$PATH

To activate the environment for your current shell, type the same line at your prompt:

    shell$ . ~/.bashrc.oracle

Next, make sure your system can resolve the name you set for HOST_NAME. Use sudo (entering the Oracle user's password) to edit the /etc/hosts file, changing the line that reads

    127.0.0.1       localhost

to

    127.0.0.1       localhost HOST_NAME

where HOST_NAME is the value you set in .bashrc.oracle.

Creating a Database

Now that your ORACLE_HOME is in place and your environment is correctly configured, you can create a database. Copy the lines in Listing 1 into a file called createDB.

This script creates several configuration files (init{ORACLE_SID}.ora, listener.ora, and tnsnames.ora) and then creates the database. There are several areas of this script you may want to modify, particularly if you are setting up a development environment that mirrors a current production environment. First, you can change the LOG_DIR and DATA_DIR variables to put your database log files and data files into another directory. The default directory is $ORACLE_HOME/dbs.

Second, after the "# Set up your INIT.ORA file" line, you can adjust some of the settings in init{ORACLE_SID}.ora to reflect your production environment, keeping in mind that the resource settings may not need to be as high for your development instance as for the production server. You can request a copy of your production init.ora file from your DBA, and there is a detailed reference to the settings in the Oracle9i Database Administrator's Guide, which you can find at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm (OTN membership required).

Third, you can customize the settings for the Oracle listener daemon and clients, by editing the sections after "# Set up your LISTENER.ORA file" and "# Set up your TNSNAMES.ORA file." The default is IPC (interprocess communication), but if you need to have remote clients connect to the database, you should un-comment the TCP lines. You can also add other instances to the TNSNAMES.ORA file, if you want to use SQL*Plus on your Mac to work with remote databases.

Finally, you can customize the commands that create the database. For example, you will see that the script creates two separate tablespaces—one called "tmeda" and one called "imeda." In our application tables are created in the "tmeda" tablespace, indexes are created in the "imeda" tablespace, and the tablespace files are on separate disk partitions on the production machine. You can also modify the size of the datafiles and logfiles to reflect the amount of space you'll need for your application.

When you have customized the script for your environment or decided that the defaults are fine, make it executable and run it. Be aware that the script takes a while to run and uses a significant amount of resources—you may want to run out and grab a burrito while it executes. For instance, the default script took 30 minutes on my 12-inch PowerBook G4 867 with 640 MB of RAM.

Enter the following commands:

    shell$ chmod 755 createDb
    shell$ ./createDb

Test the Instance

After the script has finished executing, the database and the listener should be running. To confirm that everything is in order, log in via SQL*Plus and execute the following command:

    shell$ sqlplus scott/tiger
    SQL> select object_name from user_objects;

You should see

    OBJECT_NAME
    ------------------
    BONUS
    DEPT
    EMP
    PK_DEPT
    PK_EMP
    SALGRADE

    6 rows selected.

Congratulations! Oracle is up and running on OS X. At this point, you can change default passwords, create users, and load your application. If you've modified the $ORACLE_HOME/network/admin/tnsnames.ora file, you can also connect to remote Oracle instances.

You can use the following script to start and stop the Oracle database and the listener.

    #!/bin/sh

    # Use to start and stop the Oracle database
    # Must be run by oracle user or member of DBA group

    start() {
    $ORACLE_HOME/bin/sqlplus /nolog <<__EOF__
    connect / as sysdba
    startup
    __EOF__
    sleep 2
    $ORACLE_HOME/bin/lsnrctl start
    }

    stop() {
    $ORACLE_HOME/bin/lsnrctl stop
    $ORACLE_HOME/bin/sqlplus /nolog <<__EOF__
    connect / as sysdba
    shutdown abort
    __EOF__
    }

    if [ -z $ORACLE_HOME ]; then
    echo "You must define ORACLE_HOME" 1>&2
    exit 2
    fi

    if [ ! -d $ORACLE_HOME ]; then
    echo "Unable to find ORACLE_HOME" 1>&2
    exit 2
    fi

    case "$1" in
    'start')
    start
    ;;
    'stop')
    stop
    ;;
    *)
    echo "Usage: $0 { start | stop }"
    exit 2
    ;;
    esac

    exit 0

Place it in a directory on the Oracle user's path (~/bin will work if you have followed the instructions above; just create it first), make sure it is executable, and use it as follows:

    shell$ oraclectl start
    shell$ oraclectl stop
 

Installing PHP

Now that Oracle is installed and running, it's a snap to compile PHP. The Apache Web server and a PHP module are included in the Mac OS X install, but you need to compile OCI support into your own custom module. First back up the module Apple supplies (enter this entire line at the prompt):

    shell$ sudo mv /usr/libexec/httpd/libphp4.so/usr/libexec/httpd/
       libphp4.so.apple

Now unpack the archive you downloaded from php.net:

    shell$ tar xjf php-4.3.4.tar.bz2
    shell$ cd php-4.3.4

PHP uses the standard "./configure; make; make install" autoconf syntax for compilation and installation, but there are a few things to keep in mind when compiling for OS X. One is that Apple has included many, but not all, of the supporting libraries you need for PHP extensions (such as XSLT and graphics support). If the libraries for the extensions your applications require are not included, you will want to download, compile, and install them before compiling PHP. There are a few projects that ease the process of managing third-party packages, among them Fink (http://fink.sourceforge.net/) and DarwinPorts (http://darwinports.opendarwin.org/). See "A Better SQL*Plus" below for an example of using DarwinPorts to install the readline library.

For now, though, you should concentrate on installing a basic PHP module that will let you talk to Oracle. You can modify the configure line below to activate the extensions you need for your application.

    shell$ ./configure --with-apxs --with-oci8
    shell$ make
    shell$ sudo make install
 

PHP support is enabled for you in the Apache httpd.conf file that ships with Mac OS X Panther. If you're using Jaguar, however, you'll need to activate it yourself—see http://www.php.net/manual/en/install.macosx.php and http://developer.apple.com/internet/macosx/php.html for details.

Test the Installation

To make sure PHP can connect with Oracle, create a file with the following contents in /Library/Webserver/Documents/test_oci.php:

   <?php

        // Set this to the value you set in ~oracle/.bashrc.oracle
        $sid = 'OSXDEV';

        //
        // You shouldn't have to change anything below here
        //
        $home='/Users/oracle/Source/9iR2';
        putenv("ORACLE_HOME=$home");
        putenv("ORACLE_SID=$sid");
        putenv("TNS_ADMIN=$home/network/admin");

        echo "<html><head><title>Test OCI</title></head><body>\n";

        // Persistent connections are recommended for OCI
        $conn = OCIPlogon('scott', 'tiger', $sid);
        if ($conn) {
            $stmt = OCIParse($conn, "SELECT TO_CHAR(SYSDATE,
                                     'YYYY-MM-DD HH24:MI:SS') AS datetime
                                     FROM DUAL");
            if ($stmt) {
                if (OCIExecute($stmt)) {
                    while (OCIFetchInto($stmt, $row, OCI_ASSOC)) {
                        // keys from OCI_ASSOC are always UPPERCASE
                        echo "SYSDATE: ", $row['DATETIME'];
                    }
                }
            }
        }

        echo "</body></html>\n";
    ?>

Change the $sid variable to the value you set in .bashrc.oracle, and start the Web server:

    shell$ sudo apachectl start

You can now point your browser at http://127.0.0.1/test_oci.php, and you should see the current date and time. Refresh your browser a few times to see the time change, and note that the page is displayed much quicker than the first time. Persistent connections are a must with Oracle and PHP.

PHP's OCI extension supports many advanced Oracle features, such as stored procedures, bind variables, and cursors. More information about using OCI with PHP can be found here :

• "Get Started with Oracle and PHP," by Sean Hull
• Notes from a presentation by a maintainer of the OCI extension; see slides at http://conf.php.net/pres/index.php?p=slides%2Foci&id=oci3
• Oracle's PHP troubleshooting FAQ
• PHP Manual, OCI section

A Better SQL*Plus

Finally, I'd like to introduce you to a tool that has made my life as an Oracle developer much more pleasant. Ljubomir J. Buturovic created gqlplus, a wrapper for SQL*Plus that uses the GNU readline library to add command-line history, editing, and table/column name completion. It runs on most UNIX-like systems, and the download contains binaries for the Linux/Intel, Solaris/SPARC, OSF1/Alpha, IRIX, and OSX/PPC platforms.

There is one catch, though: As I mentioned earlier, Apple doesn't include some libraries that are useful for development with OS X, so you need to install the readline library before using gqlplus. I choose to use the DarwinPorts collection for this purpose, but you can certainly use Fink or just grab the source from the GNU project and compile it yourself.

First, download the latest snapshot of the DarwinPorts collection and build and install the ports tools:

    shell$ curl -O http://darwinports.opendarwin.org/
        darwinports-nightly-cvs-snapshot.tar.gz
    shell$ tar zxf darwinports-nightly-cvs-snapshot.tar.gz
    shell$ cd darwinports/base
    shell$ ./configure --prefix=/usr/local
    shell$ make
    shell$ sudo make install

I have elected to make the base for the installed ports /usr/local, because many packages look for libraries there by default. When the ports tools are installed, you can install readline:

    shell$ cd ../dports
    shell$ sudo port install readline

DarwinPorts automatically fetches the source and configures, compiles, and installs the library. Now all you need to do is get gqlplus from http://prdownloads.sourceforge.net/gqlplus/gqlplus-1.8.tar.gz?download and unpack and install it. Because SourceForge makes it hard to download without going through your browser, you will probably need to switch back to your user account for the next steps.

    shell$ cd Desktop   # (or wherever you downloaded the file)
    shell$ tar zxf gqlplus-1.8.tar.gz
    shell$ sudo cp gqlplus-1.8/OSX/gqlplus /usr/local/bin

Now switch back to the Oracle user and try it out, using the same syntax as for SQL*Plus:

    shell$ gqlplus scott/tiger

Play around with the various line editing commands—an online reference is available at http://cnswww.cns.cwru.edu/php/chet/readline/rluserman.html.

Conclusion

Oracle, Mac OS X, and open source technologies such as PHP and gqlplus make for an enjoyable, productive, and stable development environment. Oracle's commitment to fully supporting the Oracle Database 10g release on OS X has removed the last roadblock for those want to explore the Mac.