PHP and Oracle: Christopher Jones

PHP-FPM FastCGI Process Manager with Apache 2

cj — Tue, 13 Mar 2012 23:00:00 -0500

I've published some vanilla PHP 5.4.0 RPMs to make new feature testing easier.

Along with all the PHP 5.4 goodies, the php-fpm "FastCGI Process Manager" is available for the first time on oss.oracle.com. Php-fpm is an alternative FastCGI interface to PHP with various extra features such as load dependent spawning of processes. (For other features, see php-fpm.org). Php-fpm has been getting more and more traction in the PHP community and the EXPERIMENTAL flag was removed in PHP 5.4. You might want to test it out.

To use php-fpm with the default Apache web server, first install Oracle Linux 5.8 (64bit) using Oracle's free, public yum repository public-yum.oracle.com.

Install Apache, if not already installed:

  yum install httpd

Download and install the PHP 5.4 RPMs from oss.oracle.com/projects/php:

  rpm -i php54-common-5.4.0-1.el5.x86_64.rpm php54-fpm-5.4.0-1.el5.x86_64.rpm

Other extensions can also be installed, depending on the functionality you want to test.

Download and build FastCGI for Apache:

  wget http://www.fastcgi.com/dist/mod_fastcgi-current.tar.gz
  tar -zxf mod_fastcgi-current.tar.gz
  cd mod_fastcgi-2.4.6
  cp Makefile.AP2 Makefile
  make top_dir=/usr/lib64/httpd

Install FastCGI as root:

  make top_dir=/usr/lib64/httpd install

Edit /etc/httpd/conf/httpd.conf and comment out any existing references to PHP you might previously have added for testing:

# LoadModule php5_module        modules/libphp5.so
# AddType application/x-httpd-php .php

Add the php-fpm configuration to httpd.conf:

  LoadModule fastcgi_module modules/mod_fastcgi.so

  <IfModule mod_fastcgi.c>  
    FastCGIExternalServer /usr/sbin/php-fpm -host 127.0.0.1:9000
    AddHandler php-fastcgi .php  

    #<LocationMatch "/status">
    #  SetHandler php-fastcgi-virt
    #  Action php-fastcgi-virt /usr/sbin/php-fpm.fcgi virtual
    #</LocationMatch>

    Action php-fastcgi /usr/sbin/php-fpm.fcgi  
    ScriptAlias /usr/sbin/php-fpm.fcgi /usr/sbin/php-fpm  

    <Directory /usr/sbin>  
      Options ExecCGI FollowSymLinks  
      SetHandler fastcgi-script  
      Order allow,deny  
      Allow from all  
    </Directory>  
  </IfModule>

Start php-fpm and Apache:

  service php-fpm start
  service httpd start

Test it out with your favorite script or create a file pi.php:

  <?php
    phpinfo();
  ?>

Save it in /var/www/html/pi.php or in $HOME/public_html/pi.php, if you have configured UserDir in httpd.conf.

In a browser load http://localhost/pi.php or http://localhost/~yourname/pi.php.

This will show the PHP configuration values.

To test php-fpm's built-in statistics, edit httpd.conf and uncomment the four lines of the LocationMatch section:

  <LocationMatch "/status">
    SetHandler php-fastcgi-virt
    Action php-fastcgi-virt /usr/sbin/php-fpm.fcgi virtual
  </LocationMatch>

Edit /etc/php-fpm.conf and uncomment the line:

  pm.status_path = /status

Restart php-fpm and Apache:

  service php-fpm restart
  service httpd restart

Run some load on the system:

  ab -c 10 -t 60 http://localhost/pi.php

Now http://localhost/status gives you the status of the server:

  pool:                 www
  process manager:      dynamic
  start time:           13/Mar/2012:14:25:53 -0700
  start since:          26
  accepted conn:        50001
  listen queue:         0
  max listen queue:     6
  listen queue len:     128
  idle processes:       2
  active processes:     1
  total processes:      3
  max active processes: 5
  max children reached: 1

The php-fpm.conf file documents other output formats for the statistics. It also shows the extensive functionality available with php-fpm.

Documentation on php-fpm is not ideal, but you can see some at http://php.net/manual/en/install.fpm.php. The php-fpm.org site has more, including a forum and wiki.

Php-fpm is commonly used in conjunction with the nginx webserver. For this, you will need to compile nginx yourself. Because php-fpm is installed and managed separately from the webserver, the php-fpm RPM will still be usable. Now that Apache 2.4 has been released with claims of improved performance, it will be interesting to see if web server popularity swings back towards Apache.

Finally, remember that the PHP RPMs on oss.oracle.com are for testing purposes only. They are not supported.

PHP 5.4.0 RPMs for 64bit Oracle Linux 5.x are available

cj — Tue, 13 Mar 2012 22:06:43 -0500

I've published some vanilla PHP 5.4.0 RPMs to make new feature testing easier for Oracle Linux 5.x 64 bit users. The standard set of RPMs is at oss.oracle.com/projects/php. The OCI8 extension is also available (this requires the free Oracle Instant Client 11.2 from ULN or OTN.)

Some of the features of PHP 5.4 are:

Improved memory usage and performance. Some impressive preliminary reports of the benefits include: http://news.php.net/php.internals/57760 and http://news.php.net/php.internals/57747.
File Upload progress support is natively implemented.

Support for Traits now allows code reuse:

    trait t1 {
	function m1() { echo "hi"; }
	function m2() { echo "bye"; }
    }

    class my_c1 {
	use t1;
	/*...*/
    }

    class my_c2 extends c2 {
	use t1;
	/*...*/
    }

A built-in HTTP server for development is included:
```
  php -S 127.0.0.1:8888
```
Improvements were made to the interactive PHP shell (when PHP is compiled with readline).
A shortened array syntax was introduced: $a = [1,2,3];
The default character set for several internal functions was changed to UTF-8.
Support for multibyte languages is now configurable at run-time instead of compile-time.
The value echo tag "<?=" is now always on.
Binary number support was added.
DTrace support was added.
A new typehint indicates a function argument must be callable.
Session entropy uses /dev/urandom or /dev/arandom by default for extra security if either is present at compile time.
Function call results can now be immediately dereferenced as arrays: foo()[0]
Class members can be accessed on instantiation: (new foo)->method()

For more changes see the migration documentation.

PHP 5.4 is out; your work begins now

cj — Fri, 2 Mar 2012 01:57:20 -0600

Exciting news: PHP 5.4 is out. Thanks to the release managers Stas & David for making it happen. The PHP code is syncing to mirrors. PHP documentation will take a day or two to build & sync. Take a look when it reaches you.

The announcement lists some of the features that have changed.

There is a current tidy up of documentation for new features and migration happening. There are some low priority bug fixes that are waiting merge. These came in during the extended release process and were left out to avoid destabilizing the code.

Some PECL extensions still need to make PHP 5.4 compatible releases. Rasmus helped out by making changes to some extension, but the package owners need to bundle up releases. Other extensions may need some work.

XDebug is due for a compatible release soon.

PHPUnit is said to be fine already.

APC has at least one pending fix too, for the "?:" operator.

In summary, PHP 5.4 is available and I expect this branch will stabilize quickly.

PHP VirtualBox VM has been refreshed

cj — Thu, 19 Jan 2012 13:19:05 -0600

While I was recently on summer vacation (yes, it is summer in half the world), the VirtualBox VM for PHP was refreshed to a more recent Zend Server/Oracle Linux/Oracle Database XE stack.

I just pulled the VM down, imported it and had it booting and serving PHP pages in a very few minutes. VirtualBox really is marvelous. There is a link to download the VM near the foot of the Oracle Technology Network Developer VM page.

PHP 5.3.9 RPMs Available for Testing

cj — Thu, 12 Jan 2012 20:06:44 -0600

I've updated my relatively "vanilla" PHP 5 RPMs to 5.3.9 on oss.oracle.com/projects/php. They are built for Oracle Linux 5.7 (and RHEL 5.7). I've included the OCI8 extension for Oracle DB, of course. The various MySQL extension are there and use the mysqlnd driver, so installation doesn't require any client-side MySQL libraries. Note the PHP 5.3.9 RPMs are for testing only. For production users I recommend Zend Server.

PHP OCI8 1.4.7 is available on PECL

cj — Wed, 11 Jan 2012 13:37:37 -0600

Following on the heels of the PHP 5.3.9 release, I've bundled OCI8 1.4.7 for PECL. The PECL OCI8 1.4.7 code is the same as included in PHP 5.3.9 and can be used to update older PHP installations. The release notes are here. There are several bug fixes; upgrading is recommended.

PHP oci_password_change() ORA-1017 gotcha with Oracle Database 11.2.0.3

cj — Mon, 24 Oct 2011 18:27:45 -0500

The Oracle Instant Client 11.2.0.3 for Linux is now available on OTN. I know many users will upgrade sooner rather than later to get the "latest and greatest" release.

However, if your PHP application uses oci_password_change (or its old alias ocipasswordchange), you should upgrade the client libraries (Instant or normal "full" client) and the Oracle Database version to 11.2.0.3 at the same time. Otherwise oci_password_change will fail with the error "ORA-1017: invalid username/password". If you're not using oci_password_change then this won't impact you.

Since the protocol change is in the Oracle libraries, the change is not just limited PHP. Any C program using Oracle's Call Interface "OCIPasswordChange" API to change passwords will also have the same restrictions.

Scripting Language Related Sessions at Oracle OpenWorld and JavaOne, October 2011

cj — Mon, 26 Sep 2011 23:50:29 -0500

Oracle OpenWorld and JavaOne conferences are happening in San Francisco next week. It will be a busy and exciting time.

First, here's a shout out: For me the conference kicks off on Sunday morning. Marcelle Kratochvil from Piction (heavy users of PHP and Oracle DB) is hosting the inaugural Unstructured Data with Multimedia SIG for Oracle Database and MySQL database (32440) Sunday 9:00 am in Moscone West room 2011.

Below are some of the scripting and related sessions happening during the week.

Exhibition Hall

During the Exhibition Hall hours, come and talk to us at the Database Access Services and APIs booth. This year we're in Moscone South, Left SL-067. The Tuxedo application server booth is Moscone South, Right - SR-202. At JavaOne look out for the NetBeans booth, Hilton San Francisco - HHJ-023.

Scripting Sessions, Birds-of-a-Feather Meetings, and Hands-on-Labs at OOW

The Oracle Tuxedo team has scripting language support in their powerful application server environment:
High-Performance Web Applications with C/C++/PHP/Python (15705)
Monday, 05:00 PM, Moscone South - 300
This year we are running introductory Hands-on Lab sessions for three languages concurrently. Come along and choose which language you'd like to dip your toes into:
Develop and Deploy High-Performance Web 2.0 PHP, Ruby, or Python Applications (30082)
Monday, 05:00 PM, Marriott Marquis - Salon 10/11
Come and ask questions at the round table Birds-of-a-Feather session:
Meet the Oracle Database Clients Developers: C, C++, PHP, Python, Ruby, and Perl (26240)
Monday, 07:30 PM, Marriott Marquis - Salon 8
My overview and state-of-the-nation session is:
PHP, Ruby, Python, and Perl: Develop and Deploy Mission-Critical Apps with Oracle Database 11g (14704)
Wednesday, 11:45 AM, Marriott Marquis - Salon 8
The Tuxedo team Hands-on-Lab lets you code in C/C++/PHP/Python/Ruby:
Develop High-Performance, Service-Oriented C/C++ Applications for Oracle Exalogic (31120)
Thursday, 12:00 PM, Marriott Marquis - Salon 3/4
Raimonds Simanovskis, maintainer of the Rails adapter for Oracle is giving a session:
Extending Oracle E-Business Suite with Ruby on Rails (8604)
Thursday, 03:00 PM, Moscone West - 2002/2004

Several other sessions discuss topics that scripting language devotees will find invaluable:

Build, Deploy, and Troubleshoot Highly Performant, Highly Available Apps with Oracle Database (14703)
Wednesday, 05:00 PM, Moscone South - 303
Net Services: Best Practices for Performance, Scalability, and High Availability (14345)
Wednesday, 01:15 PM, Moscone South - 303

Also check out the full Oracle Tuxedo application server schedule here.

Scripting at JavaOne

Over in the concurrent JavaOne conference there are several scripting sessions driven by San Francisco's EngineYard. This year they have JRuby sessions but with their recent aquisition of PHP technnology, I hope they'll have more on PHP in one of the OOW streams next year:

Accelerate Your Business and Aim for the Cloud with Java and JRuby (25284)
Wednesday, 03:00 PM, Parc 55 - Embarcadero
From Java to Rails: Techniques for Adding Ruby Agility to Your Java Web Stack (24582)
Monday, 05:30 PM, Parc 55 - Market Street
Real-World JRuby (23600)
Wednesday, 04:30 PM, Parc 55 - Market Street
Script Bowl 2011: A Scripting Languages Shootout (22060)
Wednesday, 08:30 AM, Hilton San Francisco - Grand Ballroom B

Also keep an eye out for the various NetBeans IDE sessions and demo booth.

Linux

Check out the four pages of Focus on Linux sessions and events.

MySQL

There is a veritable plethora of MySQL content - four pages of sessions and activites are listed in the Oracle Focus on MySQL. Don't forget the MySQL Community Reception Tuesday 7:00pm - 9:00pm in the Marriott Marquis - Foothill G.

Having started this post with a shout out, let me end with one to Bill Karwin, who was instrumental in getting PHP's Zend Framework off the ground. He is talking about MaatKit and SQL Injection.

Update: Ligaya Turmelle, well known in the PHP community, confirmed overnight that despite a recent job move she is still on track to present her MySQL Performance Tuning talk (16040).

You can search the OOW session catalog here and the JavaOne session catalog here.

Oracle 11.2 XE with newly updated PHP Developer's Guide is available

cj — Sat, 3 Sep 2011 21:01:10 -0500

Oracle Database 11g Release 2 Express Edition aka "Oracle XE" is now available on Linux 64 bit and Windows 32 bit. This is a free version of the Oracle Database. Windows 64 bit is "in the works" but Linux 32 bit is not planned.

Check out the newly updated Oracle Database Express Edition 2 Day + PHP Developer's Guide. As well as HTML and PDF variants, the manual is available in Mobi and EPUB formats.

The 2 Day + PHP manual has steps for installing PHP and walks through creating an introductory application. It shows different ways to interact with Oracle XE and introduces PHP 5.3 features.

The example shows the mechanics of DB interaction. It builds the application from the ground up so you can understand how to construct your own high performance applications. If you want to continue the learning path and use a PHP framework, Oracle 11g XE works with the Developing a PHP Web Application with Oracle Database 11g training.

The Oracle 11.2 XE Linux install is RPM based. It takes just a few minutes to install, prompting only for a few necessary details such as desired passwords and ports. You need root access to install it.

Oracle 11.2 XE supports DRCP connection pooling so you'll really be able to maximize the small footprint database for PHP applications.

Readers who want to dive deeper into detail about PHP and advanced features available in other editions of Oracle Database might be interested in the later sections of the Underground PHP and Oracle Manual.

PHP 5.3.8 RPMs are on oss.oracle.com

cj — Tue, 30 Aug 2011 15:41:09 -0500

I've built PHP 5.3.8 RPM packages with various common extensions (and the latest OCI8 1.4.6) for Linux x64. They are downloadable at oss.oracle.com/projects/php/. These binaries might be useful for quick testing. They are unsupported.

Oracle customers with support using Oracle Linux 5.6+ may prefer the stable PHP 5.3.3 php53-* packages available on ULN. The OCI8 extension is also available there.

PHP OCI8 1.4.6 is Available

cj — Mon, 22 Aug 2011 19:15:45 -0500

I just released PHP OCI8 1.4.6 on PECL. This is the same OCI8 extension that is included in PHP 5.3.7 and PHP 5.3.8.

The release is mostly test suite additions. A new oci_client_version() function is available. This is useful for OCI8's test suite to overcome some of the limitations of PHP's simple test infrastructure. You might also find it useful in special cases where you need to know the exact version of the Oracle client libraries that PHP is using.

There are also some small internal code changes brought on by general PHP code cleanups. These OCI8 changes allow cross version portability for the extension.

Oracle Enhanced Adapter 1.4.0 for Ruby on Rails is Available

cj — Wed, 10 Aug 2011 08:45:59 -0500

Check out Raimond Simanovskis's updated Rails ActiveRecord adapter for Ruby and JRuby with Oracle DB: Oracle enhanced adapter 1.4.0 and Readme Driven Development. I love the "Readme Driven Development" approach. It's close to my favorite model which is "install Driven Development".

Oracle Resources for PHP

cj — Wed, 15 Jun 2011 11:58:56 -0500

Here are some key resources for working with PHP and Oracle technologies.

Overall Links

Oracle : Oracle Technology Network (OTN)

Homepage : The PHP Developer Center -- downloads, how-tos, sample code and discussion forums brought to you by OTN.

Read : PHP Articles -- a collection of informative articles on OTN.

Technology : Download the newest versions of other software in Oracle's Technology Stack. Read about other Oracle application development tools.
PHP Oracle Database Extension : OCI8 is the most available and scalable PHP adapter for the Oracle database. It is included with PHP, and is also separately downloadable for upgrading older PHP releases. OCI8 works with PHP 4 and PHP 5, and will compile with Oracle 9iR2, 10g and 11g client libraries. Oracle's standard cross-version compatibility and connectivity is applicable, so OCI8 can connect to older or newer databases, locally or remotely. The latest OCI8 release includes support for Oracle Database 11g Database Resident Connection Pooling (DRCP), and for Fast Application Notification (FAN) [whitepaper here], and also has support for Oracle's authentication and end-to-end tracing meta data attributes [article here]. These features improve scalability and availabilty of the OCI8 extension.

The OCI8 extension can also be used with the Oracle In-Memory Database Cache.

Book : The Underground PHP & Oracle Manual -- the definitive, free guide to using PHP with Oracle Database.

Tutorial : Oracle & PHP tutorials -- step by step Oracle Learning Library tutorials for using PHP with Oracle. (Free OTN login required)

Read : PHP & High Scalability -- covers enabling DRCP and FAN for PHP applications.

Read : PHP Web Auditing, Authorization and Monitoring with Oracle Database -- learn how to audit individual web users, automatically apply rules to individual web users to restrict data access, and monitor and trace database usage per application user.

Forum : The Oracle & PHP forum -- technical discussion forum for using PHP with Oracle.

Blog : PHP and Oracle: Christopher Jones -- The latest news on PHP and Oracle.
Database : Oracle Express Edition Database -- an entry-level, small-footprint database based on the standard Oracle Database code base that's free to develop, deploy, and distribute; fast to download; and simple to administer.

PHP OCI8 works with Oracle Database XE the same way it works with the other editions of Oracle Database.

Oracle Database XE is a great starter database for:
- Developers working on PHP, Python, Ruby, Java, .Net and other open source applications.
- DBAs who need a free, starter database for training and deployment
- Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
- Educational institutions and students who need a free database for their curriculum
Install Now!
Database : MySQL -- The world's most popular open source database. MySQL Community Edition is the freely downloadable version. Commercial customers have the flexibility of choosing from multiple editions to meet specific business and technical requirements. Also available for free is the MySQL Workbench for SQL Authoring, System Administration, and schema modeling.

Recent additions to MySQL include semi synchronous replication, direct memcached-to-InnoDB access, multi-threaded replication, and enhanced partitioning. See What's new in MySQL 5.6

PHP's mysqli or PDO_mysql extensions can be used to access MySQL.

Check out the new Query Cache plugin documented here and the Replication and Load-Balancing plugin for master-slave-splitting, documented [here].

Homepage : Using MySQL With PHP -- Articles and downloads

Forum : PHP and MySQL forum -- The place to ask questions

Blog : Planet MySQL -- The latest MySQL news from the community

Blog : Johannes Schlüter -- A member of the MySQL connector team and the current PHP Release Master.

Blog : Me Talking Out Loud -- Ligaya Turmelle, a member of MySQL Support. (and contributer to The PHP Anthology: 101 Essential Tips, Tricks & Hacks)

Blog : Internet Super Hero -- Ulf Wendel, a member of the MySQL connector team.

Books : Many! Including PHP and MySQL Web Development and Web Database Applications with PHP & MySQL and Learning PHP & MySQL: Step-by-Step Guide to Creating Database-Driven Web Sites

Install Now!
Database : Oracle Berkeley DB 11g -- provides the complex data management features found in enterprise class databases. These facilities include high throughput, low-latency reads, non-blocking writes, high concurrency, data scalability, in-memory caching, ACID transactions, automatic and catastrophic recovery when the application, system or hardware fails, high availability and replication in an application configurable package. Simply configure the library and use the particular features available to satisfy your particular application needs. Berkeley DB can be configured to address any application need from the hand-held device to the datacenter, from a local storage solution to a world-wide distributed one, from kilobytes to petabytes.

Berkeley DB's SQL API allows PHP's sqlite3 and PDO_sqlite extensions to be used.

Berkeley DB also includes source code for a php_db4 extension.

Install Now!
IDE : NetBeans -- lets you develop desktop, mobile and web applications using Java, PHP, C/C++ and more. Runs on Windows, Linux, Mac OS X and Solaris. NetBeans IDE is open-source and free.

Understand: NetBeans PHP features -- All the great features that NetBeans has for PHP developers

Learn: NetBeans PHP Learning Trail -- the best way to learn how to use NetBeans

Install Now!
Virtualize : Oracle VM VirtualBox -- powerful Cross-platform Virtualization Software for x86-based systems. "Cross-platform" means that it installs on Windows, Linux, Mac OS X and Solaris x86 computers. And "Virtualization Software" means that you can create and run multiple Virtual Machines, running different operating systems, on the same computer at the same time. For example, you can run Windows and Linux on your Mac, run Linux and Solaris on your Windows PC, or run Windows on your Linux systems.

Oracle VM VirtualBox is available as Open Source or pre-built Binaries for Windows, Linux, Mac OS X and Solaris.

OTN hosts some pre-built Developer VMs , including an Oracle Tuxedo Web Application Server VM showing PHP support. There is also a Zend Server VM for PHP created by Zend. Zend Server is a complete, Oracle-enabled, enterprise-ready Web Application Server for running and managing PHP applications that require a high level of reliability, performance and security. Zend Server is available in community and supported editions.

Install Now!
Operating system : Oracle Linux -- free to download and distribute. As one of the most widely deployed operating systems today, Linux is increasingly being adopted for cloud-based solutions. Oracle Linux is the most complete and integrated solution available and delivers higher performance and better reliability at up to 7 times lower cost than Red Hat. Why Choose Oracle Linux over Red Hat Linux?

Oracle Linux 5.6 and 6 introduced PHP 5.3 packages. Subscribers to the Unbreakable Linux Network have access to an additional pre-built PHP OCI8 RPM, and also direct access to an RPM for installing Zend Server .

Users of older Linux 5 releases can get PHP 5.3 RPMs from oss.oracle.com .

Install Now!
Application Server : Oracle Tuxedo -- provides a robust, grid enabled platform for developing enterprise applications. It provides mainframe-class scale and performance on open, distributed systems for software written in C, C++, COBOL, PHP, Python and Ruby. Oracle Tuxedo provides cost-effective reliability, extreme scalability and throughput of hundreds of thousands of transactions per second. Functionality like Web services, SCA programming model, metadata driven application development make it simple to develop and integrate applications written in many programming languages.

PHP applications can be hosted on the Tuxedo platform without requiring any code changes and can leverage high availability, scalability and integration capabilities.

The whitepaper Oracle Tuxedo - An Enterprise Platform for Dynamic Languages explains the architecture and features. A Tuxedo Demo VM for VirtualBox is available for immediate testing.

Install Now!
Client Libraries : Oracle Instant Client -- a small footprint set of libraries that allows applications and tools to connect to an existing Oracle Database. Oracle OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification.

The PHP OCI8 extension can be built with Oracle Instant Client to connect to a remote database.

Instant Client is provided under a separate OTN Development and Distribution License for Instant Client that allows most licensees to download, redistribute, and deploy in production environments, without charge.

Install Now!

Mid conference wrap up

cj — Tue, 24 May 2011 12:51:10 -0500

In a nicer section of the RAI conference center than in previous years I've attended, the Dutch PHP conference last week was as professionally organized as ever by ibuildings. The weather was kind too, having cleared up from a previous blustery days I'd spent south of Amsterdam with my parents.

My talk had a big audience, which stayed the course. Unfortunately my abstract was condensed down in the small printed name-tag/program-guide, which contributed to some polarized reviews. Some really liked the Oracle-PHP overview section, while others were not expecting it.

In a few minutes I'm heading off to Chicago for php|tek. My talk is based on the presentation I gave at DPC but I'll cut down the "state of the Oracle-PHP nation" section down by a couple of slides (taking it to about 8 minutes worth). I'll also rejig which advanced Oracle techniques are covered in the main part of the talk. My code snippets and screen captures didn't satisfy DPC audience's thirst for knowledge so I'm adding back (some) live demos - hey, why not live dangerously!?

Since I'm at the conference for the full three session days, there'll be plenty of time for you to pin me down with your questions. And if you have MySQL questions, Ligaya Turmelle is the person to talk to. Her tutorial is in session as I write. She's also talking about schema normalization on Wednesday afternoon.

At php|tek make sure you come along to the Uncon after hours on Wednesday and find all the latest and hot topics. Oracle is sponsoring it this year.

Oracle at the Dutch PHP Conference & php|tek

cj — Sat, 14 May 2011 11:15:08 -0500

I'm off to Amsterdam to talk at the Dutch PHP Conference. After that it's on to Chicago for php|tek.

See you there!

Oracle Tuxedo: A New way to Run PHP Applications

cj — Thu, 12 May 2011 16:08:57 -0500

The Oracle Tuxedo team have posted an Oracle Tuxedo 11.1.1.2 Web Application Server Demo for PHP. It is a VirtualBox VM so you can immediately test it out. The free VM is available from OTN's Pre-Built Developer VMs page.

Oracle Tuxedo provides a scalable and highly available mid tier platform capable of managing and integrating heterogeneous applications. This overview datasheet explains its capabilities: "Oracle Tuxedo provides a service-oriented infrastructure for efficiently routing, dispatching, and managing requests, events, and application queues across system processes and application services". A raft of other material is on the Tuxedo landing page. There is also a new white paper on PHP being reviewed at this very moment.

The VM uses Oracle Linux 5.6. It has PHP 5.3 and Oracle Tuxedo 11.1.1.2 installed and configured. The SALT and TSAM components of Tuxedo are also available.

Tuxedo's PHP support comes in the form of a new mod_tuxedo module for Apache 2.2 (or Oracle HTTP Server). This handles all PHP scripts, executing them using managed PHP processes. Existing applications can run normally:

Tuxedo also lets PHP be exposed as a web service:

The tutorial manual provided inside the VM steps through using PHP in both ways. The NetBeans IDE, Oracle Database XE and MySQL are included on the VM to make it easier to carry out your own further experiments with Tuxedo and PHP.

Tuxedo has been around for a long time and has an established customer base. If you look at Oracle Database's big TPC-C benchmark's you can see Tuxedo was involved. It's even certified on Exalogic. The new dynamic language support in Tuxedo is going to open up "Enterprise" infrastructure, making it easy to integrate modern solutions in a heterogeneous environment.

When I get back from the Dutch PHP and PHP|Tek conferences, which run back to back from next week, I plan to write up more details about using Tuxedo and PHP.

Update: a new whitepaper is available: Oracle Tuxedo: An Enterprise Platform for Dynamic Languages.

PHP 5.4 is Gathering Momentum

cj — Wed, 11 May 2011 16:36:24 -0500

Discussion on the PHP mail lists and IRC channels in the past few days has been looking positive about an alpha release of PHP 5.4 soon. This will be taken from the "trunk" branch of PHP. The exact feature list is under discussion but the mood seems to be "ship what we currently have" though a couple of features are slated to be deferred until later.

A paragraph from a post by Rasmus Lerdorf on PHP's "internals" mail list is worth re-broadcasting. Rasmus is more than willing to grant PHP karma to encourage contributions:

And if you can't figure out how to fix a test, post the details here. I'd love to point some of the obvious talents and energy of this list towards the code. If you don't have an svn account for committing your fixed test, go to http://www.php.net/svn-php.php and fill in the little form at the bottom there and put in the test that you fixed and a 1-liner about how you fixed it and I will set you up with an account right away. Info on how to check out the code from svn is here: https://wiki.php.net/vcs/svnfaq

So, you can easily become a contributor to PHP, which would look great on your resume.

If SVN seems daunting you can run PHP tests from the normal snapshots and release bundles too.

To discuss test issues you can subscribe to the PHP internals and QA mail lists at http://php.net/mailing-lists.php

Configuring PHP GD on Ubuntu 11.04 "Natty"

cj — Wed, 11 May 2011 14:52:27 -0500

This is a post for the "so I can find it again" category. (It is also a test for the new blogging infrastructure that Oracle migrated to last week).

After an upgrade to Ubuntu 11.04 on one 32 bit machine, my simple custom script to build PHP 5.3 from source failed. The script isn't complex, pulling in a couple of extensions that I use for sanity checking the OCI8 extension. I use the GD extension to generate some simple graphs.

My configuration command was like:

./configure ... --with-gd --with-jpeg-dir --with-png-dir ...

which gave the error:

configure: error: libjpeg.(a|so) not found

This release of Ubuntu has moved around some standard libraries. After some fiddling around, checking the 'configure' options and the GD extension config.m4 file intention, the solution for me was simple. Add the --with-libdir=lib/i386-linux-gnu option to configure. I haven't tried this with other extensions so YMMV.

Update: Rasmus just merged a patch to gd that should make the --with-libdir option unnecessary

Malicious website use will never go away: how do you manage it?

cj — Wed, 27 Apr 2011 14:48:43 -0500

OTN has published two articles by Eli White that stem from his long experience with protecting high volume websites from unwanted and malicious use. Eli has worked on many large scale PHP projects including Digg, TripAdvisor, and for the Hubble space telescope program. I've seen Eli's conference presentation on preventing web site spam and on the deeper topic of website manipulation. I am really glad to see the material put in article format. The techniques Eli covers will always be applicable in one form or another. They should be a fundamental part of any website architect's bag of tricks.

The articles are:

Combating Spam for Healthier Websites

and

Preventing Nefarious Website Manipulation (Gaming a System)

These articles follow on from Eli's recent two part article on Scaling a PHP MySQL Web Application.

SQL*Plus 10.1 Substitution Variables

cj — Mon, 11 Apr 2011 10:11:10 -0500

This article from 2004 was on OTN for many years. It wasn't flagged for saving when they did an infrastructure migration in 2010 but I think it still has some value because SQL*Plus is fundamentally unchanged. Ignore the reference manual links, which don't point to the current manual. Also ignore the section on iSQL*Plus, a product obsoleted by the introduction of Oracle Apex and SQL Developer.

1 Introduction

2 Using Substitution Variables

2.1 Creating, Showing and Deleting Substitution Variables

2.2 Referencing Substitution Variables

2.3 Prompting for Undefined Variables

2.4 Difference Between "&" and "&&" Prefixes

2.5 Storing a Query Column Value in a Substitution Variable

2.6 Predefined Substitution Variables

2.7 Script Parameters

2.8 More on Substitution Variables

3 Using Bind Variables

3.1 Assigning Substitution Variables to Bind Variables

3.2 Assigning Bind Variables to Substitution Variables

4 Using System Variables

4.1 System Variables Influencing Substitution Variables

4.1.1 SET CONCAT

4.1.2 SET DEFINE

4.1.3 SET ESCAPE

4.1.4 SET NULL

4.1.5 SET NUMFORMAT

4.1.6 SET NUMWIDTH

4.1.7 SET SQLPROMPT

4.1.8 SET VERIFY

4.2 System Variables in Titles and EXIT

5 SQL*Plus Substitution Variable Commands

5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands

5.7.1 Using "&" Prefixes With Title Variables

5.7.2 Variables and Text Spacing in Titles

5.8 UNDEFINE Command

5.9 WHENEVER Command

6 Substitution Variables Namespace, Types, Formats and Limits

6.1 Substitution Variable Namespace

6.2 Substitution Variable Types

6.3 Substitution Variable Formats

6.4 Substitution Variable Limits

7 iSQL*Plus and Substitution Variables

7.1 iSQL*Plus 9 and SQL*Plus Substitution Variable Compatibility

7.2 iSQL*Plus Parameters

8 Substitution Variable Summary

9 Substitution Variable Examples

9.1 Setting a Substitution Variable's Value

9.2 Using a Substitution Variable

9.3 Finding All Defined Substitution Variables

9.4 Inserting Data Containing "&" Without Being Prompted

9.5 Putting the Current Date in a Spool File Name

9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable

9.7 Putting a Period After a Substitution Variable

9.8 Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

9.9 Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

9.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL

9.11 Passing Parameters to SQL*Plus Substitution Variables

9.12 Passing Operating System Variables to SQL*Plus

9.13 Passing a Value to a PL/SQL Procedure From the Command Line

9.14 Allowing Script Parameters to be Optional and Have a Default Value

9.15 Passing a Value to an iSQL*Plus Dynamic Report for the Web

9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web

9.17 Using a Variable for the SQL*Plus Return Status

9.18 Putting the Username and Database in the Prompt

1 Introduction

This document explains how SQL*Plus substitution variables work and where they can be used. It shows the relationship between the three types of variable (substitution, bind, and system) used in SQL*Plus.

Substitution variables can replace SQL*Plus command options or other hard-coded text. They can be used to customize SQL*Plus script output. Substitution variable references in a statement are pre-processed and replaced before SQL*Plus executes the statement. Variable values can be pre-defined, prompted for, or set to script parameters. Variables can also hold values returned from queries. Sometimes substitution variables are known as user variables or define variables.

Bind variables store data values for SQL and PL/SQL statements executed in the RDBMS. They can hold single values or complete result sets.

System variables contain values directly controlling SQL*Plus, such as the line size and page size of reports. Some system variables affect how substitution variables are processed. System variables are sometimes called SET variables.

This document is a complete reference but you can understand and use substitution variables in a few minutes by reading the overview in Section 2 and looking at the examples in the last section.

Section 3 and Section 4 discuss bind and system variables respectively, and how they interact with substitution variables.

Section 5 discusses in detail all SQL*Plus commands that interact with substitution variables.

Section 6 gives substitution variable limits and some finer points.

Section 7 covers substitution variables in the iSQL*Plus web based interface.

Section 8 is a brief summary of substitution variables.

Section 9 gives a number examples of using substitution variables.

This document was updated in February 2004 for SQL*Plus Version 10.1. However all examples in this document apply to previous versions of SQL*Plus, except where noted.

Small sections were borrowed from the SQL*Plus User's Guide and Reference and the SQL*Plus FAQ. Some examples were derived from Support Notes and from questions posted on the iSQL*Plus Forum.

Thanks to all reviewers, especially Glenn Stokol, Sharon Castledine and the SQL*Plus Team.

2 Using Substitution Variables

2.1 Creating, Showing and Deleting Substitution Variables

Substitution variables can be explicitly created with the DEFINE command. Defining a variable means storing a value for future use:

    SQL> define myv = 'King'

This creates a variable called "myv" containing the text "King".

Another way to create substitution variables is with the ACCEPT command. This can be used to prompt for a value:

    SQL> accept myv2 char prompt 'Enter a last name: '

This command causes SQL*Plus to stop and prompt you to enter a character string:

    Enter a last name: _

What you enter is stored in the variable "myv2".

The DEFINE command can also be used to display known variables. It shows the variable name, value and type. Any variable that DEFINE lists is said to be defined:

    SQL> define myv
    DEFINE MYV             = "King" (CHAR)

All variables that are currently defined can be shown by executing the DEFINE command with no arguments:

    SQL> define
    DEFINE MYV             = "King" (CHAR)
    DEFINE MYV2            = "Taylor" (CHAR)
    ...

Any variable not listed is undefined:

    SQL> define abc

    SP2-0135: symbol abc is UNDEFINED

Substitution variables can be removed with the UNDEFINE command:

    SQL> undefine myv

2.2 Referencing Substitution Variables

Variables can be referenced by prefixing their name with an ampersand (&):

    SQL> define myv = 'King'
    SQL> select employee_id from employees where last_name = '&myv';

SQL*Plus lists the statement line number and line containing the substitution variable "myv" before and after substitution:

    old   1: select employee_id from employees where last_name = '&myv'
    new   1: select employee_id from employees where last_name = 'King'

Lines verifying substitution are displayed for SQL or PL/SQL statements. The lines can be hidden with SET VERIFY OFF. Verification never occurs for variables in SQL*Plus commands (e.g. SPOOL and SET).

A more practical use of substitution variables is to prompt for a value before referencing the variable:

    SQL> accept myv char prompt 'Enter a last name: '
    SQL> select employee_id from employees where last_name = '&myv';

If these two commands are stored in a SQL*Plus script, a different last name can be entered each time the script is run.

2.3 Prompting for Undefined Variables

If a variable is referenced using an "&" prefix, but the variable value is not yet defined, SQL*Plus prompts you for a value:

    SQL> define myname
    SP2-0135: symbol myname is UNDEFINED

    SQL> select employee_id from employees where last_name = '&myname';
    Enter value for myname:

After you enter a value, SQL*Plus substitutes the variable and executes the query.

The Oracle Globalization Language setting (e.g. the language component of the NLS_LANG environment variable) determines the exact language used for the "Enter value for" prompt. The prompt text cannot otherwise be changed.

2.4 Difference Between "&" and "&&" Prefixes

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the variable is discarded and remains undefined. If the variable is referenced twice, even in the same statement, then you are prompted twice. Different values can be entered at each prompt:

    SQL> prompt Querying table &mytable
    Enter value for mytable: employees
    Querying table employees
    SQL> select employee_id from &mytable where last_name = 'Jones';
    Enter value for mytable: employees

    EMPLOYEE_ID
    -----------
            195

If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value. Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again:

    SQL> prompt Querying table &&mytable
    Enter value for mytable: employees
    Querying table employees
    SQL> select employee_id from &mytable where last_name = 'Jones';

    EMPLOYEE_ID
    -----------
            195

2.5 Storing a Query Column Value in a Substitution Variable

Data stored in the database can be put into substitution variables:

    SQL> column last_name new_value mynv
    SQL> select last_name from employees where employee_id = 100;

The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called "mynv". The variable is not physically created until a query references the column LAST_NAME. When the query finishes, the variable "mynv" holds the last retrieved value from column "last_name":

    SQL> define mynv
    DEFINE mynv      = "King" (CHAR)

2.6 Predefined Substitution Variables

The predefined substitution variables created when you start SQL*Plus can be seen by entering DEFINE with no arguments. Each predefined variable is prefixed with an underscore. The predefined variables can be undefined or redefined just like user defined substitution variables.

In SQL*Plus Release 10.1 the predefined variables are:

    _CONNECT_IDENTIFIER
    _DATE
    _EDITOR
    _O_RELEASE
    _O_VERSION
    _PRIVILEGE
    _SQLPLUS_RELEASE
    _USER

The variables _DATE, _PRIVILEGE, and _USER were introduced in SQL*Plus 10.1. The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2.

The variable _CONNECT_IDENTIFIER contains the connection identifier used to start SQL*Plus. For example, if the SQL*Plus connection string is "hr/my_password@MYSID" then the variable contains MYSID. If you use a complete Oracle Net connection string like "hr/my_password@(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then _CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not explicitly specified then _CONNECT_IDENTIFIER contains the default connect identifier Oracle uses for connection. For example, on UNIX it will contain the value in the environment variable TWO_TASK or ORACLE_SID. If SQL*Plus is not connected then the variable is defined as an empty string.

The variable _DATE can be either dynamic, showing the current date or it can be set to a fixed string. The date is formatted using the value of NLS_DATE_FORMAT and may show time information. By default a DEFINE or dereference using &_DATE will give the date at the time of use. _DATE can be UNDEFINED, or set to a fixed string with an explicit DEFINE command. Dynamic date behavior is re-enabled by defining _DATE to an empty string.

The variable _EDITOR contains the external text editor executable name. See 5.4 EDIT Command.

The variable _O_RELEASE contains contains a string representation of the Oracle database version number. If your Oracle database version is 9.2.0.3.0 then the variable contains "902000300". The Oracle version may be different from the SQL*Plus version if you use Oracle Net to connect to a remote database.

The variable _O_VERSION contains a text string showing the database version and available options.

When SQL*Plus is connected as a privileged user the variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS SYSOPER". If SQL*Plus is connected as a normal user the variable is defined as an empty string.

The variable _SQLPLUS_RELEASE contains the SQL*Plus version number in a similar format to _O_RELEASE.

The variable _USER contains the current username given by SHOW USER. If SQL*Plus is not connected, the variable is defined as an empty string.

2.7 Script Parameters

Parameters can be passed to SQL*Plus scripts. For example, from the command line:

    sqlplus hr/my_password @myscript.sql King

You can also pass parameters when calling a SQL*Plus script from within a SQL*Plus session, for example:

    SQL> @myscript.sql King

Script parameters become defined substitution variables. The variable name for the first parameter is "1", the second is "2", etc. The effect is as if you start SQL*Plus and type:

    SQL> define 1 = King
    SQL> @myscript.sql

Commands in myscript.sql can reference "&1" to get the value "King". A DEFINE command shows the parameter variable:

    SQL> define 1
    DEFINE 1      = "King" (CHAR)

Script parameter variables have type CHAR, similar to variables explicitly created with DEFINE.

Quoting parameters with single or double quotes is allowed. This lets whitespace be used within parameters. Operating systems and scripting languages that call SQL*Plus handle quotes in different ways. They may or may not pass quotes to the SQL*Plus executable. For example, in a standard Bourne shell on UNIX, quotes around parameters are stripped before the parameters are passed to SQL*Plus, and SQL*Plus never sees the quotes.

It is recommended to check how quoted parameters are handled on your operating system with your patch level of SQL*Plus. For portability between UNIX and Windows environments use double quotes around parameters containing whitespace.

SQL*Plus Releases 8.1.7, 9.2.0.3 (and other 9.x versions patched for bug 2471872) and 10.1 onwards remove an outer set of single or double quotes from parameters passed on the SQL*Plus command line. This makes SQL*Plus behave the same way on operating systems that do not themselves strip quotes as it does when the operating system strips the quotes before calling SQL*Plus.

As an example of passing parameters, when SQL*Plus 10.1 is called in the UNIX shell script:

    #! /bin/sh
    sqlplus hr/my_password @myscript.sql "Jack and Jill"

only one program parameter is defined. References in myscript.sql to "&1" are replaced with "Jack and Jill" (without quotes - because the shell script does not pass quotes to SQL*Plus).

From SQL*Plus Release 9.0 onwards, an empty string can be passed as a parameter.

2.8 More on Substitution Variables

Substitution variable references are pre-processed and substituted before the command is otherwise parsed and executed. For each statement SQL*Plus will:

    1. Loop for each "&" and "&&" variable reference
           If the variable is defined
               Replace the variable reference with the value
           else
               Prompt for a value
               Replace the variable reference with the value
               If the variable is prefixed with "&&" then
                   define (i.e. store) the variable for future use
    2. Execute the statement

Step 1 happens inside the SQL*Plus client tool. SQL*Plus then sends the final statement to the database engine where step 2 occurs.

It is not possible to repeatedly prompt in a PL/SQL loop. This example prompts once and the entered value is substituted in the script text. The resulting script is then sent to the database engine for execution. The same entered value is stored five times in the table:

    begin
      for i in 1 .. 5 loop
        insert into mytable values (&myv);
      end loop;
    end;
    /

Substitution variables are not recursively expanded. If the value of a referenced variable contains an ampersand, then the ampersand is used literally and is not treated as a second variable prefix:

    SQL> set escape \
    SQL> define myv = \&mytext
    SQL> prompt &myv
    &mytext

You cannot use a substitution variable as the first token of a command. Each command name must be hard coded text otherwise an error is displayed. For example:

    SQL> &myv * from dual;
    SP2-0734: unknown command beginning "&myv * fro..." - rest of line ignored.

Substitution variables cannot be used in buffer editing commands like APPEND, CHANGE, DEL, and INPUT. Ampersands (&) in these commands are treated literally.

If you wish to use alphanumeric characters immediately after a substitution variable name, put the value of SET CONCAT - by default a period (.) - to separate the variable name from the following characters. For example, if "mycity" is defined as "Melbourne" then:

    SQL> spool &mycity.Australia.log

is the same as:

    SQL> spool MelbourneAustralia.log

If you want to append a period immediately after a substitution variable name then use two periods together. For example, if "myfile" is defined as "reports" then the command:

    SQL> spool &myfile..log

is the same as:

    SQL> spool reports.log

Text in ANSI "/* */" or "--" comments that looks like a substitution variable may be treated as one, for example:

    SQL> select department_id, location_id /* get dept & loc */ from departments;
    Enter value for loc: _

Here the text "& loc" in the comment is interpreted as a variable reference. SQL*Plus prompts you for a value for the variable "loc".

3 Using Bind Variables

Bind variables are used in SQL and PL/SQL statements for holding data or result sets. They are commonly used in SQL statements to optimize statement performance. A statement with a bind variable may be re-executed multiple times without needing to be re-parsed. Their values can be set and referenced in PL/SQL blocks. They can be referenced in SQL statements e.g. SELECT. Except in the VARIABLE and PRINT commands, bind variable references should be prefixed with a colon.

Bind variables are created with the VARIABLE command. The following PL/SQL block sets a bind variable:

    SQL> variable bv number
    SQL> begin
      2    :bv := 8;
      3  end;
      4  /


    PL/SQL procedure successfully completed.

Once a value is set, you can show it with the PRINT command.

    SQL> print bv

            BV
    ----------
             8

Numeric bind variables can be used in the EXIT command to return a value to the operating system:

    SQL> EXIT :bv

Other SQL*Plus commands do not recognize bind variables.

There is no way to undefine or delete a bind variable in a SQL*Plus session. However, bind variables are not remembered when you exit SQL*Plus.

For information about automatically displaying values and using REFCURSOR bind variables for whole result sets, see Using Bind Variables and VARIABLE, in the SQL*Plus User's Guide and Reference.

3.1 Assigning Substitution Variables to Bind Variables

You can assign a substitution variable to a bind variable:

    SQL> define mysubv = 123
    SQL> variable mybndv number
    SQL> execute :mybndv := &mysubv;

SQL*Plus executes the PL/SQL assignment statement after it substitutes the value of "mysubv". If "mysubv" was not already defined, you would be prompted for a value.

The bind variable can be used in subsequent SQL or PL/SQL commands.

3.2 Assigning Bind Variables to Substitution Variables

Sometimes it is useful to make the value of a bind variable available to SQL*Plus commands like TTITLE or SPOOL. For example, you might want to call a PL/SQL function that returns a string and use the value for a SQL*Plus spool file name. The SPOOL command does not understand bind variable syntax so the bind variable value needs to be assigned to a substitution variable first.

This is done using COLUMN NEW_VALUE and SELECT commands. For example, declare a bind variable in SQL*Plus and instantiate it in a PL/SQL block. Its value can be returned from a PL/SQL function, or like here, set by a direct assignment:

    SQL> variable mybv varchar2(14)
    SQL> begin
      2    /* ... */
      3    :mybv := 'report.log';
      4  end;
      5  /

Pass the bind variable's value to a new substitution variable "nv" by using a query:

    SQL> column mybvcol new_value nv noprint
    SQL> select :mybv mybvcol from dual;

Now you can use the substitution variable in a SPOOL command:

    SQL> spool &nv

The SPOOL command executes as if you had typed

    SQL> spool report.log

4 Using System Variables

Most system variables are the SET command options used to control the behavior of the SQL*Plus system. For example, to set the output line size from SQL*Plus:

    SQL> set linesize 60

The current status of each system variable can be displayed with the SHOW command.

    SQL> show linesize
    linesize 80

System variables are sometimes known as SET variables.

Some system variables contain values that cannot be set. For example, RELEASE (a string representation of the SQL*Plus version) can only be shown.

See SET and SHOW in the SQL*Plus User's Guide and Reference.

4.1 System Variables Influencing Substitution Variables

Several system variables influence substitution variables.

4.1.1 SET CONCAT

Use SET CONCAT to define the character that separates the name of a substitution variable from alphanumeric characters that immediately follow the variable name. By default it is a single period (.).

For example, if "mycity" is defined as "Melbourne" then the command:

    SQL> spool &mycity.Australia.log

is the same as:

    SQL> spool MelbourneAustralia.log

See SET CONCAT in the SQL*Plus User's Guide and Reference.

4.1.2 SET DEFINE

Use SET DEFINE OFF to stop SQL*Plus performing any variable substitution. This makes SQL*Plus treat all ampersands (&) as literal characters and prevents SQL*Plus prompting you for values:

    SQL> set define off
    SQL> select 'B&W' MyHeading from dual;


     MYH
     ---
     B&W

The default substitution variable prefix is an ampersand (&). The SET DEFINE command can be used to change the variable-name prefix character. SET DEFINE ON turns variable substitution back on and resets the prefix character to "&"

Sometimes in SQL*Plus literature you may see references to the SET SCAN command. This is an obsolete alternative for SET DEFINE. To ensure maximum portability of scripts use SET DEFINE.

See SET DEFINE in the SQL*Plus User's Guide and Reference.

4.1.3 SET ESCAPE

Use SET ESCAPE to prevent isolated occurrences of "&" from being treated as the substitution variable prefix:

    SQL> set escape \
    SQL> select 'B\&W' MyHeading from dual;

     MYH

     ---
     B&W

Any "&" without the escape character is treated as a variable prefix.

See SET ESCAPE in the SQL*Plus User's Guide and Reference.

4.1.4 SET NULL

SET NULL sets the text that SQL*Plus displays when a NULL data value is printed.

A substitution variable may take the value of the SET NULL text if a COLUMN NEW_VALUE (or COLUMN OLD_VALUE) command associated the variable with a selected column and the current row contains a NULL value. The type of the substitution variable temporarily changes to CHAR while it contains NULL.

See SET NULL in the SQL*Plus User's Guide and Reference.

4.1.5 SET NUMFORMAT

SET NUMFORMAT and SET NUMWIDTH interact. Use SET NUMFORMAT to change the default display format of a numeric variable. Use SET NUMFORMAT "" to remove the format. When there is no format, the default number formatting uses the SET NUMWIDTH option:

    SQL> show numformat
    numformat ""
    SQL> define myn
    DEFINE MYN             =     123.45 (NUMBER)

    SQL> set numformat 0999.9
    SQL> define myn
    DEFINE MYN             =  0123.5 (NUMBER)
    SQL> set numformat 9.9EEEE
    SQL> define myn
    DEFINE MYN             =   1.2E+02 (NUMBER)
    SQL> prompt The number is &myn
    The number is   1.2E+02

See SET NUMFORMAT in the SQL*Plus User's Guide and Reference.

4.1.6 SET NUMWIDTH

SQL*Plus uses the value of SET NUMWIDTH only if there is no value for SET NUMFORMAT. Use SET NUMWIDTH to change the display width of a numeric variable:

    SQL> show numformat
    numformat ""
    SQL> show numwidth
    numwidth 15
    SQL> define myn
    DEFINE MYN             =     123.45 (NUMBER)
    SQL> set numwidth 6
    SQL> define myn
    DEFINE MYN             = 123.45 (NUMBER)
    SQL> set numwidth 15
    SQL> define myn
    DEFINE MYN             =          123.45 (NUMBER)

Note the value is right justified within the field width and the number of leading spaces changes in each example.

SQL*Plus displays pound signs (#) if the format or field width for a numeric substitution variable is too small for the value.

See SET NUMWIDTH in the SQL*Plus User's Guide and Reference.

4.1.7 SET SQLPROMPT

In SQL*Plus 10g, substitution variables in the prompt are dynamically substituted each time the prompt is printed. Like variables used in TTITLE, they should not be prefixed with '&' else they are substituted only once when the SET SQLPROMPT command is executed.

This example shows the HR user setting the prompt and re-connecting to the OE schema. The predefined substitution variables _USER and _CONNECT_IDENTIFIER are used in the prompt to give the current username and database:

    SQL> set sqlprompt "_user'@'_connect_identifier:SQL> "
    HR@MYDB:SQL> connect system/manager
    SYSTEM@MYDB:SQL> disconnect
    @:SQL> connect oe/my_password@otherdb
    OE@otherdb:SQL>

Each time the prompt is printed, SQL*Plus checks each word to see if it is a defined substitution variable. If it is, it will have its value printed. Otherwise it is displayed verbatim. Text in nested quotes will never be substituted. For performance reasons, the word SQL in the default prompt "SQL> " is never treated as a substitution variable.

Variables in the prompt are dynamically substituted only when SET SQLPLUSCOMPATIBILITY is 10.1 or greater. Otherwise, and for SQL*Plus versions 9.2 and earlier, "&"-prefixed variables can be substituted once when the SET SQLPROMPT command is executed.

4.1.8 SET VERIFY

Use SET VERIFY to control whether SQL*Plus echoes the old and new statement text when it substitutes a variable's value. SET VERIFY only has an effect on substitution variables used in SQL and PL/SQL statements:

    SQL> set verify on

    SQL> define myv = 100

    SQL> select last_name from employees where employee_id = &myv;
    old   1: select last_name from employees where employee_id = &myv

    new   1: select last_name from employees where employee_id = 100

    LAST_NAME
    -------------------------
    King


    SQL> set verify off

    SQL> select last_name from employees where employee_id = &myv;

    LAST_NAME
    -------------------------
    King

Variables used in SQL*Plus commands (like SET and TTITLE) are not verified.

See SET VERIFY in the SQL*Plus User's Guide and Reference.

4.2 System Variables in Titles and EXIT

There is a special syntax to reference system variables in TTITLE, BTITLE, REPHEADER, REPFOOTER, and EXIT commands. The name of each special variable is the same as the SHOW option prefixed with "SQL.".

The special variables that can be referenced include:

SQL.PNO - page number
SQL.LNO - line number
SQL.USER - current username
SQL.RELEASE - SQL*Plus version
SQL.SQLCODE - last Oracle "ORA" error number

For example:

    SQL> ttitle left 'Salary Report. Page: ' sql.pno
    SQL> select salary from employees;
    SQL> exit sql.sqlcode

System variables of numeric type e.g. SQL.SQLCODE are formatted using the same rules as numeric substitution variables.

The variables cannot be prefixed with an "&" (see 5.7.1 Using "&" Prefixes With Title Variables).

These variables are not substitution variables. The DEFINE command does not show them. They cannot be referenced in general commands. The system variables are not affected if you create substitution variables with the same name. For example, SQL.USER is not affected if you create a substitution variable called USER. The system variable SQL.RELEASE is not affected if the predefined substitution variable _O_RELEASE is changed.

5 SQL*Plus Substitution Variable Commands

Substitution variables can be used to replace options and values in almost all SQL*Plus commands. Several of the commands have special significance for substitution variables. These are discussed below.

5.1 ACCEPT Command

The ACCEPT command always prompts for a variable's value, creating a new variable or replacing an existing one. ACCEPT has advantages over a double ampersand (&&) variable reference that causes a prompt. ACCEPT allows the prompting text to be customized and allows a default value to be specified. ACCEPT does type and format checking.

    SQL> accept myv number default 10 prompt 'Enter a number: '
    Enter a number: _

In this example, if you enter alphabetic characters then an error is shown and you are re-prompted. If you press Enter without typing anything then the variable takes the value "10".

The ACCEPT command understands numbers, strings and dates. If a FORMAT clause is used, SQL*Plus validates the input against the given format. If the input is not valid, you are re-prompted for a value. For a list of format models, see Format Models in the Oracle Database SQL Reference 10g Release 1.

If a FORMAT specifier such as "A10" is used for a CHAR variable, the entered value may be any length up to and including 10 characters.

If a FORMAT specifier is used for a NUMBER type, the allowed inputs depend on the specifier used. For example, a specifier of "9EEEE" (for exponential notation) allows "3e2" but not "300" or "12e2". A format specifier of "999.99" allows both "123.45" and "67" to be entered.

DATE variables are validated against an explicitly supplied FORMAT or against the default session date format (like "DD-MON-YYYY").

After successful validation against the format model, variables are stored in the appropriate variable type. See 6.2 Substitution Variable Types. In particular, substitution variables created with an ACCEPT ... DATE command are stored with type CHAR.

The ACCEPT ... HIDE option can be used to prevent the value you enter from being displayed on the screen. This can be useful for scripts that need to prompt for passwords. Note some operating systems cannot redirect batch program script output into an ACCEPT ... HIDE command.

See ACCEPT in theSQL*Plus User's Guide and Reference for the full ACCEPT command syntax.

5.2 COLUMN Command

The COLUMN NEW_VALUE and COLUMN OLD_VALUE commands can be used to associate a substitution variable with a SELECT column's data.

    SQL> column department_id new_value dnv

When column "department_id" selected, a substitution variable "dnv" is created to hold each row of the column in turn. The variable remains defined after the query completes:

    SQL> select department_id
      2  from departments
      3  where department_id between 60 and 100;

    DEPARTMENT_ID
    -------------
               60
               70

               80
               90
              100

    SQL> define dnv
    DEFINE DNV             =        100 (NUMBER)

COLUMN OLD_VALUE and NEW_VALUE substitution variables can be used for basic numeric and text column types. They are used for any query executed that has a column of the same name as the COLUMN command. Their current value can be displayed in report headings and titles during query execution.

COLUMN NEW_VALUE variables should be used in TTITLE and REPHEADER titles at start of a page. They hold data from the new row about to be printed on the page. After the query finishes the variable has value of the last row.

COLUMN OLD_VALUE variables are used in BTITLE and REPFOOTER titles at the end of a page. They contain data from the old row most recently printed on the page. See 5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands for discussion and examples.

Variables change type as required. If another query with the same column name is run, the variable may take on a new type. Also if a number column contains null values, a substitution variable on the column changes from type NUMBER to CHAR for that row. This lets it hold the current string for the SET NULL option. The variable changes back to NUMBER when the next numeric value is fetched.

If no rows are selected by a query, and the substitution variable does not already exist, then a zero length CHAR variable is created. If the substitution variable exists then its value and type are not changed.

Similar to the DATE option for the ACCEPT command, a variable on a DATE column is stored as type CHAR.

COLUMN NEW_VALUE and OLD_VALUE can be used to transfer a value from a bind variable to a substitution variable. See 3.2 Assigning Bind Variables to Substitution Variables.

The COLUMN option NOPRINT can be used to suppress query results and stop them appearing in a final report.

See COLUMN in the SQL*Plus User's Guide and Reference for the command syntax.

5.3 DEFINE Command

Use the DEFINE command to explicitly create substitution variables:

    SQL> define myv = 'King'

The DEFINE command can also be used to display the value of a known variable. It shows the variable name, value and type:

    SQL> define myv
    DEFINE MYV             = "King" (CHAR)

Using DEFINE with no arguments lists all defined substitution variables. Any variable that DEFINE lists is said to be defined.

A variable may be redefined by repeating the DEFINE command with a different value.

The DEFINE command only ever creates variables with type CHAR.

See DEFINE in the SQL*Plus User's Guide and Reference for the command syntax.

5.4 EDIT Command

The EDIT command starts an external editor such as Notepad or Vi. On most operating systems SQL*Plus has a predefined substitution variable called _EDITOR set to the default editor's executable:

    SQL> define _editor
    DEFINE _EDITOR             = "Notepad" (CHAR)

EDIT can edit a named file. It can also edit the current SQL buffer (which holds the most recently executed SQL statement). When the external editor is closed the changed statement is loaded back into the SQL buffer. EDIT writes the SQL buffer to a temporary file called afiedt.buf. The temporary file name can be changed with the SET EDITFILE command.

You can redefine the value of the _EDITOR substitution variable to any editor.

On Windows _EDITOR can be set to "write.exe" to invoke WordPad. However, if the SQL buffer is being edited, the buffer is not automatically updated with the modified script. This is because SQL*Plus cannot tell when the WordPad editor has been closed.

A recommended way to create SQL*Plus scripts is to explicitly specify a file name for EDIT and then use the START or "@" commands to run this file.

See EDIT and SET EDITFILE, both in the SQL*Plus User's Guide and Reference.

5.5 EXIT Command

On many operating systems the EXIT command can pass the value of a numeric bind variable or substitution variable to the operating system environment. On UNIX, the return status from SQL*Plus can be displayed with the command "echo $?" in the Bourne, Korn and Bash shells, or with "echo $status" in the C shell.

To return a substitution variable, it is recommended not to use an ampersand prefix before its name. If you use "&" or "&&", the command preprocessor does the substitution using default number formatting rules (see 6.3 Substitution Variable Formats) before the EXIT command is finally parsed and executed. This is normal pre-processing of a numeric substitution variable in a command. Since the Oracle format specifier may include decimal and group separators which are not digits, or the number may overflow the format and be substituted as pound signs (#), there may be problems doing the final conversion from the resulting formatted string to the operating system return status. For example, if the formatting rules return exponential format and the number is formatted as "4E+05", then only the value "4" is returned to the operating system by the EXIT command.

When "&" does not prefix the substitution variable name, e.g. "EXIT myv", SQL*Plus internally uses the more practical format specifier "9999999990" to convert from the internal number format to the string used as the EXIT command parameter.

Note some operating systems limit the number range that can be returned from a program. On such systems the returned value may overflow and contain an unexpected number. This commonly limits the use of the system variable SQL.SQLCODE which contains the last Oracle error number. Typically this number is larger than an operating system supports as an exit return status.

If a non numeric variable is referenced in an EXIT statement the EXIT command exits but reports an error. The operating system return value is the same as for EXIT FAILURE.

Substitution variables are not saved when SQL*Plus exits. Only the predefined substitution variables and any variables set in the site and user profiles (e.g. glogin.sql and login.sql) are defined when you next start SQL*Plus.

See EXIT in the SQL*Plus User's Guide and Reference.

5.6 HOST Command

The HOST command runs a specified operating system command or opens a command window. On some operating systems a character like "!" or "$" is a synonym for HOST.

After a HOST command finishes then the substitution variable _RC is defined. Its value is port specific and may contain a text message. On UNIX it is defined as "0" if the command is successful, or "1" if not. It may also be the operating exit status returned from the host program. On other platforms the value of _RC is not well defined and its value should not be relied on.

See HOST in the SQL*Plus User's Guide and Reference.

5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands

Variables are used in report titles to make each page relate to the data on that page, for example to give the product item that the report page describes. Any substitution variable can be used in a title command. However, the COLUMN NEW_VALUE or OLD_VALUE commands are often used to associate variables with column values in a report query.

Use NEW_VALUE variables in TTITLE and REPHEADER commands. Use OLD_VALUE variables in BTITLE and REPFOOTER commands. For example, the script:

   column last_name new_value ttnv old_value btov
   ttitle left 'First employee is: ' ttnv
   btitle left 'Last employee is: ' btov
   select last_name from employees where department_id = 60 order by last_name;

gives the output:

    First employee is: Austin
    LAST_NAME
    -------------------------
    Austin
    Ernst
    Hunold
    Lorentz
    Pataballa


    Last employee is: Pataballa

See TTITLE, BTITLE, REPHEADER, and REPFOOTER in the SQL*Plus User's Guide and Reference.

5.7.1 Using "&" Prefixes With Title Variables

The title commands (TTITLE, BTITLE, REPHEADER and REPFOOTER) substitute variables differently to most other commands. (The exceptions are the EXIT and SET SQLPROMPT commands, which are similar to the title commands). In general you do not need, and will not want, to put an "&" prefix before a variable name in a title command. For example, if your TTITLE command is:

    ttitle left 'Urgent: ' &2 ' Days High: ' &days

you should possibly change it to:

    ttitle left 'Urgent: ' 2 ' Days High: ' days

The guidelines for variables in titles are:

If you want the same value for a variable to be printed on every page then use an "&" prefix and put the variable inside a quoted string:

    accept mycustomer char prompt 'Enter your company name: '
    ttitle left 'Report generated for company &mycustomer'
    select last_name, job_id from employees order by job_id;

If you want each title to have data from the query that is unique to each report page then do not use an "&" prefix for the variable and do not put the variable inside quotes.
```
    column job_id new_value ji_nv noprint
    break on job_id skip page
    ttitle left 'Employees in job: ' ji_nv
    select last_name, job_id from employees order by job_id;
```

SQL*Plus substitution variables are expanded before each command is executed. After this happens in a title command, the resulting string is stored as the title text. What makes variables in titles special is that they need to be re-substituted for each page of query results. This is so the current COLUMN NEW_VALUE and OLD_VALUE substitution variable values are displayed on each page, customizing each title for the results displayed on its page. If "&" is used inadvertently or incorrectly to prefix title variables, it is possible to get double substitution. This is dependent on the variable's value and is easily overlooked when you write scripts.

Any non-quoted, non-keyword in a title is checked when the page is printed to see if it is a variable. If it is, its value is printed. If not, then the word is printed verbatim. This means that if you use "&myvar" in a title command, and the text substituted for it can itself be interpreted as another variable name then you get double variable substitution. For example, the script:

   define myvar = scottsvar
   ttitle left &myvar

   define scottsvar = Hello

   select * from dual;

causes the text "left scottsvar" to be stored as the title. When the title is printed on each page of the query this string is re-evaluated. The word "scottsvar" in the title is itself treated as a variable reference and substituted. The query output is:

   Hello
   D
   -
   X

Using "&" in titles most commonly causes a problem with the numeric variable names of the SQL*Plus script parameters. If the value of an arbitrary "&"-prefixed title variable is the same as a script parameter variable name, then double substitution will occur.

To display an "&" in a title, prefix it with the SET ESCAPE character. The ampersand (&) is stored as the title text and is not substituted when page titles are printed.

5.7.2 Variables and Text Spacing in Titles

Unquoted whitespace in titles is removed. Use whitespace instead of the SET CONCAT character to separate variables from text that should appear immediately adjacent. Use whitespace inside quotes to display a space. For example, the script:

    define myvar = 'ABC'
    ttitle left myvar myvar Text ' Other words'
    select ...;

gives a title of:

    ABCABCText Other words

5.8 UNDEFINE Command

Use UNDEFINE to remove a defined substitution variable:

    SQL> undefine myv
    SQL> define myv
    SP2-0135: symbol myv is UNDEFINED

Any variable not listed by DEFINE is said to be undefined.

Undefining unused substitution variables may help improve SQL*Plus performance because SQL*Plus can look up variables faster. This is especially true when variables are used in the SQLPROMPT.

See UNDEFINE in the SQL*Plus User's Guide and Reference for the command syntax.

5.9 WHENEVER Command

Substitution variables used for return statuses in WHENEVER OSERROR EXIT or WHENEVER SQLERROR EXIT commands follow the same general guidelines as variables in EXIT commands. Specifically, no ampersand (&) prefix is required, for example:

    SQL> whenever sqlerror exit myv

Be careful of using an ampersand (&) prefix for substitution variables in WHENEVER ... EXIT commands. Using an ampersand causes the current value of the variable at the time the WHENEVER command is run to be used, not the value that is in effect when the program later exits. For example, in the script:

    define myv = 5
    whenever sqlerror exit &myv
    define myv = 10
    -- This query should fail
    select * from non_existent_table;

the operating system return status is 5. This is because the WHENEVER statement is pre-processed and executed as if you typed:

    whenever sqlerror exit 5

However, if you remove the ampersand:

    define myv = 5
    whenever sqlerror exit myv

    define myv = 10
    -- This query should fail
    select * from non_existent_table;

the return status is "10" which is the value of "myv" at the time of exit.

See WHENEVER OSERROR and WHENEVER SQLERROR in the SQL*Plus User's Guide and Reference.

6 Substitution Variable Namespace, Types, Formats and Limits

6.1 Substitution Variable Namespace

In a SQL*Plus session there is just one global name space for substitution variables. If you reconnect using CONNECT, or run subscripts using "@", all variables ever defined are available for use and may be overridden or undefined.

When a child script finishes, all substitution variables it defined or changed are visible to the calling script. This is particularly noticeable when a subscript executed with "@" or START is given script parameters. The parameters "&1" etc. get redefined and the parent script sees the new values.

To minimize problems, and for general readability, use symbolic variable names for command parameters. All other references should use the new variable name instead of "&1". For example:

    define myuser = '&1'
    @myscript.sql King
    select first_name from employees where last_name = '&myuser';

The call to myscript.sql changes the value of "&1" to "King". By saving the original value of "&1" in "myuser" and using "&myuser" instead of "&1" in the SELECT, the query executes correctly.

6.2 Substitution Variable Types

The substitution variable types stored by SQL*Plus are:

CHAR
NUMBER
BINARY_FLOAT
BINARY_DOUBLE

The CHAR type is a generic text format similar to the database table VARCHAR2 column type. All variables created by:

DEFINE
from prompts for "&" variables
from script parameters

are of type CHAR. This ensures that values entered are substituted verbatim with no conversion loss.

Variables created by COLUMN NEW_VALUE or OLD_VALUE for the columns in Oracle number format will have the type NUMBER. These substitution variables are stored in Oracle's internal number representation as they are in the database. This allows display formats to be altered without any internal value loss. Substitution variables of BINARY_FLOAT and BINARY_DOUBLE types are similarly created for Oracle BINARY_FLOAT and BINARY_DOUBLE columns. These variables are stored in native machine representation. The CHAR type is used for NEW_VALUE and OLD_VALUE variables with all other column types.

There is no explicit DATE type. The DATE keyword in the ACCEPT command is used solely to allow correct format validation against a date format. Substitution variables created by ACCEPT ... DATE, or by COLUMN NEW_VALUE on a date column, are stored as type CHAR. For example:

    SQL> accept mydvar date format 'DD-MON-YYYY' prompt 'Enter a date: '
    Enter a date: 03-APR-2003
    SQL> define mydvar

    DEFINE MYDVAR              = "03-APR-2003" (CHAR)

If a variable already exists and is redefined, its old type is discarded and the new type used.

The type of a substitution variable is generally transparent. Substitution variables are weakly typed. For example, a COLUMN NEW_VALUE variable takes on the particular type of the named column in each new query. It may also change type during a query. For example, the type of a substitution variable used on a NUMBER column changes from NUMBER to CHAR when a NULL value is fetched. It changes back to NUMBER when the next numeric value is fetched.

No type comparison semantics are defined for any type since there is no direct comparison of variables. All variables are textually substituted before any SQL or PL/SQL statement that could do a comparison is executed.

6.3 Substitution Variable Formats

When a variable is substituted, or its value is shown by a DEFINE command, it is formatted as text before the command referencing the variable is finally executed.

CHAR variables are substituted verbatim.

NUMBER variables are formatted according to SET NUMWIDTH (by default) or SET NUMFORMAT (if you have explicitly set one):

The display format of a number can be changed even after the variable is created. To show this, first create a NUMBER variable. You cannot use DEFINE to do this because it makes the type of all new variables CHAR. Instead use a COLUMN NEW_VALUE command which inherits the NUMBER type from a NUMBER column:

    SQL> column c2 new_val m
    SQL> select 1.1 c2 from dual

            C2
    ----------
           1.1

    SQL> define m
    DEFINE M               =        1.1 (NUMBER)

Changing the format affects the display of the number but not the stored value:

    SQL> set numformat 99.990

    SQL> define m
    DEFINE M               =   1.100 (NUMBER)

For a list of format models, see Format Models in the Oracle Database SQL Reference 10g Release 1.

6.4 Substitution Variable Limits

The maximum number of substitution variables allowed is 2048. SQL*Plus gives an error an attempt is made to create more. The limit includes the predefined variables, however these can be undefined if necessary. Leaving a large number of unnecessarily defined variables can reduce the performance of SQL*Plus because variable lookups are slower.

A character substitution variable can be up to 240 bytes long.

A numeric substitution variable holds the full range of Oracle numbers. See NUMBER Datatype in the Oracle Database SQL Reference 10g Release 1.

When a command line undergoes variable substitution, the resulting line length can be no more than:

3000 bytes if it is a line of SQL (like SELECT or INSERT) or PL/SQL text (like BEGIN or CREATE PROCEDURE)
2499 bytes if it a line of a SQL*Plus command (like TTITLE or COLUMN)

Otherwise an error is displayed.

These limits may be lower in old versions of SQL*Plus.

7 iSQL*Plus and Substitution Variables

iSQL*Plus Release 10.1 interactively prompts for substitution values as the statement referencing them is executed. Each undefined variable is prompted for on its own HTML page. This is similar to command-line SQL*Plus.

Sometimes it is convenient to prompt for more than one value at a time. A separate HTML form can be used to prompt for all input. The values can be passed to iSQL*Plus as script parameters and referenced as substitution variables. See 7.2 iSQL*Plus Parameters.

7.1 iSQLPlus 9i and SQLPlus Substitution Variable Compatibility

The prompting model is different in iSQL*Plus 9i. When a script is run in iSQL*Plus Release 9.0 or 9.2, a single HTML page for undefined substitution variables is displayed. After you enter a value for each variable, the script executes and generates its results.

Some differences may be noticed between command-line SQL*Plus and iSQL*Plus Releases 9.0 or 9.2 when your script does one of the following:

contains DEFINE
contains UNDEFINE
contains SET DEFINE
uses "&" and "&&" prefixes for the same variable

These differences include being unnecessarily prompted in iSQL*Plus for values, and an empty string being used instead of the expected value. These problems do not occur in iSQL*Plus 10.1.

iSQL*Plus Releases 9.0 and 9.2 cannot prompt for input in the middle of an executing script. This is due to the way the iSQL*Plus server interacts with the SQL*Plus engine. (The engine is the same statement-executing code used by command-line SQL*Plus. The iSQL*Plus server generates the iSQL*Plus HTML interface and handles HTTP requests). If a script explicitly changes variable definitions, for example by undefining a variable, then iSQL*Plus cannot subsequently prompt for a new value. In this example an empty string is used instead.

In iSQL*Plus, parsing for "&" is performed twice, once by the iSQL*Plus server and once by the SQL*Plus engine. The iSQL*Plus server scans each script for "&" and "&&" references and creates a page with entry fields for undefined variables. When you have given values for the variables, they are transparently sent to the engine as DEFINE commands at the start of your script.

For example, if you start iSQL*Plus Release 9.x and enter:

    define mytable = employees

    break on &sortcol

    select &sortcol, salary
    from &mytable

    where salary > 12000
    order by &sortcol

the iSQL*Plus server:

Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. By default these are "&", OFF and "." respectively. (The values of SET ESCAPE and SET CONCAT are not relevant for this example.)
Parses the script as if it were a single stream of arbitrary words. Since SET DEFINE is not OFF, all "&" variables that were undefined prior to the script being started are recorded. In this example these are "sortcol" and "mytable". No SQL*Plus statements are recognized or processed so the "define mytable" is ignored and the iSQL*Plus server records that an unknown variable "mytable" was referenced in the line "from &mytable".
Creates a page prompting for values of "sortcol" and "mytable". Each variable name occurs only once on the page.
After you enter a value for each variable on the page and click "OK", iSQL*Plus prepends explicit DEFINE commands for the variables and their values to your script. Because only single "&" prefixes were used in this example, iSQL*Plus also appends matching UNDEFINE commands at the end of the script. All extra commands are removed when the script finishes and do not display in the iSQL*Plus Work screen.

The modified script is then sent to the SQL*Plus engine for processing. The engine:

Finds the session's current values of SET DEFINE, SET ESCAPE and SET CONCAT. These are still "&", OFF and "." respectively. Again, only the value of SET DEFINE is relevant to this example.
When the script in this example is run then the two variables get defined by the new, transparently added, DEFINE commands. The script's original define of "mytable" runs next and its value "employees" is the one finally used by the rest of the script.

Since SET DEFINE is not OFF, when "&sortcol" and "&mytable" are seen by the engine's preprocessor, the defined values are used.

At the conclusion of the script, the two variables are undefined in the session by the explicit UNDEFINE commands of the transparently modified script.

The undesired behavior in this example is being prompted for a value for "mytable" despite the script explicitly defining it. However, the prompted value is not used and the correct results are displayed.

The iSQL*Plus server creates the page to enter variable values unless SET DEFINE is OFF before the script is submitted to the SQL*Plus engine for execution. To stop iSQL*Plus prompting for "&" values, make sure DEFINE is OFF. In iSQL*Plus Release 9.2 go to the System Variable page and change the DEFINE radio button to OFF. Then execute your script. In iSQL*Plus Release 9.0 you need to execute an explicit second script containing "SET DEFINE OFF" prior to entering and executing the main script.

If you want to use "&" prefixed variables in a script but know all values are generated in the script (using DEFINE, COLUMN NEW_VALUE or OLD_VALUE) then make sure SET DEFINE is OFF prior to executing the script (to stop the variable entry page being displayed). Also make the first line of the script "SET DEFINE ON" (to allow the SQL*Plus engine to do variable substitution using the script-created variables) and make the last line "SET DEFINE OFF" (to prevent subsequent re-executions of the script from unnecessarily creating the variable entry page).

If you want the main script to prompt for some values, but not others, explicitly define these latter variables before running your main script. The variables you explicitly define do not generate prompts when the main script is later executed. These variables can be defined by executing an initial script containing DEFINE commands. This initial script does not have to give valid values to the variables if the main script is later going to provide them. When the main script is run, iSQL*Plus sees that the variables have already been defined in the current session and does not include them on the variable entry page.

If variables you do want to be prompted for in a script are prefixed with "&&" then make sure the script undefines them at its end. Otherwise the variables become defined in the session. When the script is re-run iSQL*Plus sees the variables have a value and does not include them on the variable entry page. Explicitly undefining the variables allows the script to re-prompt for values each time it is run.

7.2 iSQL*Plus Parameters

Variables can be passed to iSQL*Plus dynamic reports using the URL syntax. These become defined as if they are named parameters. For example, the iSQL*Plus 10.1 URL:

    http://machine/isqlplus/dynamic?script=http://machine/mys.sql&myv=emp

would define "myv" as "emp" and then run mys.sql. The script can reference "&myv". In SQL*Plus 9.2, the equivalent URL is:

    http://machine/isqlplus?script=http://machine/mys.sql&myv=emp

For compatibility with command-line SQL*Plus scripts, you can use numeric names for parameters, e.g. "1=employees"

The "&" in the URL is the character for separating URL variables. It is only coincidentally the same as the default SQL*Plus substitution variable prefix and cannot be changed using SET DEFINE.

8 Substitution Variable Summary

SQL*Plus substitution variables can be used to customize reports and can be used instead of hard-coded text. Substitution variables can interact with bind and system variables. Substitution variables that have values stored are said to be defined.

Substitution variables can be defined explicitly:
- with ACCEPT
- with DEFINE
- by passing parameters to SQL*Plus or iSQL*Plus scripts
Substitution variables can be defined implicitly:
- with a COLUMN NEW_VALUE or COLUMN OLD_VALUE command
- by using a double ampersand (&&) prefix on an undefined variable
Substitution variables references have an "&" or "&&" prefix.
If a variable is referenced but is not defined, SQL*Plus stops and prompts for a value.
Substitution variable references are pre-processed and substituted before the command is otherwise parsed and executed.
Substitution variables have a current type, such as CHAR. Substitution variables are weakly typed and change type as necessary.
Values are substituted as text. So even if the type of a variable is NUMBER, its value is formatted as a text string, substituted, and then the command executed.
In titles, EXIT and SET SQLPROMPT, substitution variables do not have to be prefixed with "&" or "&&".
The predefined substitution variables are named with a leading underscore (_). They can be undefined or redefined.

9 Substitution Variable Examples

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

The DEFINE command sets an explicit value:
```
    define myv = 'King'
```
This creates a character variable "myv" set to the value "King".
The ACCEPT command:
```
    accept myv char prompt 'Enter a last name: '
```
prompts you for a value and creates a character variable "myv" set to the text you enter.
Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:
```
    select first_name from employees where last_name = '&&myuser';
```
If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.
Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:
```
    column last_name new_value mynv
    select last_name from employees where employee_id = 100;
```
This creates a substitution variable "mynv" set to the value in the "last_name" column.

9.2 Using a Substitution Variable

Once a substitution variable has a value, it can be referenced by prefixing the variable name with an ampersand (&).

If the variable "myv" is already defined it can be used like:

    select employee_id from employees where last_name = '&myv';

9.3 Finding All Defined Substitution Variables

The DEFINE command with no parameters shows all defined substitution variables, their values, and their types. For example:

    define

might give:

    DEFINE MYV             = "King" (CHAR)
    ...

9.4 Inserting Data Containing "&" Without Being Prompted

There are two ways to make an "&" be treated as text and not cause a prompt. The first turns all variable substitution off:

    set define off
    create table mytable (c1 varchar2(20));
    insert into mytable (c1) values ('thick & thin');

The INSERT statement stores the text "thick & thin" in the table.

The second method is useful for ignoring individual occurrences of "&" while allowing others to prefix substitution variables:

    set escape \
    create table mytable (c1 varchar2(20));
    insert into mytable (c1) values ('thick \& thin');
    insert into mytable (c1) values ('&mysubvar');

The first INSERT statement in this method stores the text "thick & thin" in the table. The second INSERT causes SQL*Plus to prompt you for a value, which is then stored.

9.5 Putting the Current Date in a Spool File Name

Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

    column dcol new_value mydate noprint
    select to_char(sysdate,'YYYYMMDD') dcol from dual;

    spool &mydate.report.txt
    -- my report goes here
    select last_name from employees;
    spool off

In this example the first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "20030120" from the first query then the spool file name would be "20030120report.txt".

You can use this technique to build up any string for the file name.

The period is the default value of SET CONCAT. If you have assigned another character then use it instead of a period to end the substitution variable name.

9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable

If you wish to append alphanumeric characters immediately after a substitution variable, use the value of SET CONCAT to separate the variable name from the following text. The default value of SET CONCAT is a single period (.). For example:

    define mycity = Melbourne
    spool &mycity.Australia.txt

creates a file with the name "MelbourneAustralia.txt"

9.7 Putting a Period After a Substitution Variable

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

    define mycity = Melbourne
    spool &mycity..log

is the same as:

    spool Melbourne.log

9.8 Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

This example makes every page of a report have exactly the same heading. It can be used for TTITLE, BTITLE, REPHEADER or REPFOOTER commands.

In a TTITLE command prefix the variable name "dept" with "&" and place it inside a quoted string:

    define dept = '60'
    ttitle left 'Salaries for department &dept'
    select last_name, salary from employees where department_id = &dept;

9.9 Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER

This example uses a different title on every page of a report. Each title contains a value derived from query results shown on that particular page.

In a TTITLE command do not put an "&" before the variable name "dv". Put the variable name outside a quoted string:

    column department_id new_value dv noprint
    ttitle left 'Members of department ' dv
    break on department_id skip page
    select department_id, last_name from employees order by department_id, last_name;

In a BTITLE or REPFOOTER command use a COLUMN OLD_VALUE variable instead of a COLUMN NEW_VALUE variable.

9.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL

If you want to use the value of a bind variable in a SQL*Plus command it must first be copied to a substitution variable.

SQL*Plus commands like SPOOL, SET and TTITLE are executed in the SQL*Plus program and are not passed to the database for execution. Because of this they do not understand bind variables.

To use a bind variable's value as the name of a spool file:

    -- Set a bind variable to a text string
    variable mybindvar varchar2(20)
    begin
      :mybindvar := 'myspoolfilename';
    end;

    -- Transfer the value from the bind variable to the substitution variable
    column mc new_value mysubvar noprint
    select :mybindvar mc from dual;

    -- Use the substitution variable
    spool &mysubvar..txt
    select * from employees;
    spool off

9.11 Passing Parameters to SQL*Plus Substitution Variables

You can pass parameters on the command line to a SQL*Plus script:

    sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

    set verify off
    select employee_id from &1 where last_name = '&2';

Here the "SET VERIFY OFF" command stops SQL*Plus from echoing the SQL statement before and after the variables are substituted. The query returns the employee identifier for the employee "De Haan" from the "employees" table.

Parameters can also be passed to scripts called within SQL*Plus:

    SQL> @myscript.sql employees "De Haan"

9.12 Passing Operating System Variables to SQL*Plus

You can pass an operating system variable to a SQL*Plus script as a command line parameter. For example, on UNIX:

    sqlplus hr/my_password @myscript.sql $USER

or in a Windows command window:

    sqlplus hr/my_password @myscript.sql %USERNAME%

The script myscript.sql could reference the substitution variable "&1" to see the passed name.

9.13 Passing a Value to a PL/SQL Procedure From the Command Line

If you create a procedure "myproc":

    create or replace procedure myproc (p1 in number) as
    begin
      dbms_output.put_line('The number is '||p1);
    end;

and myscript.sql contains:

    begin
      myproc(&1);
    end;
    /

then calling:

    sqlplus hr/my_password @myscript.sql 88

executes the script as if it is:

    begin
      myproc(88);
    end;
    /

This method does not work if the parameter "p1" to "myproc" is "IN OUT". The variable reference is pre-processed and is effectively a hard coded value which cannot contain an OUT value. To get around this you can assign the substitution variable to a bind variable. The script myscript.sql becomes:

    variable mybindvar number
    begin
      :mybindvar := &1;
      myproc(:mybindvar);
    end;
    /

9.14 Allowing Script Parameters to be Optional and Have a Default Value

The goal is to create a script which accepts an optional parameter. If a parameter is passed from the command line then its value should be used. However, if there is no parameter, then SQL*Plus should ask for a value with a customized prompt.

Perhaps the closest solution is with a PROMPT/DEFINE sequence like this. If myscript.sql is:

    -- Name: myscript.sql
    prompt Enter a value for PAGESIZE
    set termout off
    define mypar = &1
    set termout on

    prompt Setting PAGESIZE to &mypar
    set pagesize &mypar

    select last_name from employees where rownum < 20;
    exit

you can call the script with or without a parameter. If you enter "12" at the prompt your screen looks like:

    % sqlplus hr/my_password @myscript.sql

    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:19:40 2003
    ...

    Enter a value for PAGESIZE
    12
    Setting PAGESIZE to 12

    LAST_NAME
    -------------------------
    King
    Kochhar
    De Haan
    ...

or if you call it with a parameter "8":

    % sqlplus hr/my_password @myscript.sql 8

    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 5 15:20:38 2003
    ...


    Enter a value for PAGESIZE
    Setting PAGESIZE to 8

    LAST_NAME
    -------------------------
    King
    Kochhar
    De Haan
    ...

Note when you pass a parameter the PROMPT text is still displayed, but you do not enter a value. The PROMPT command is the SQL*Plus "echo" or "print" statement. (It does not read input).

The only occurrence of "&1" should be where "mypar" is defined. All other references to the parameter should use "&mypar" or "&&mypar".

9.15 Passing a Value to an iSQL*Plus Dynamic Report for the Web

Variables can be passed as URL parameters to an iSQL*Plus report. For example with iSQL*Plus 10.1:

    http://machine/isqlplus/dynamic?script=http://machine/mys.sql&myv=emp&v2=dept

or in iSQL*Plus 9i:

    http://machine/isqlplus?script=http://machine/mys.sql&myv=emp&v2=dept

These define the substitution variable "&myv" as "emp" and the substitution variable "v2" as "dept" before running the script mys.sql. The script can use "&myv" and "&v2" anywhere substitution variables are allowed.

Note that iSQL*Plus prompts for a username and password before defining the variables and running the script.

9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web

A customized HTML form can be used to enter and validate variables to be passed to an iSQL*Plus Release 9.2 dynamic report.

Create and save a SQL*Plus script employee_name.sql on your Oracle HTTP Server. Check it can be loaded into a web browser to verify iSQL*Plus is able to access the file over HTTP. The script is:

    -- Name: employee_name.sql
    set verify off
    set pagesize 200
    set feedback off
    prompt Employee Details for Employee(s) with Last Name like &last_name%
    select *
    from employees
    where upper(last_name) like upper('&last_name%')
    /

Create an HTML file myreport.html on your Oracle HTTP Server. The file is:

    <html>
    <head>

      <title>iSQL*Plus Dynamic Report - Query by Last Name</title>
    </head>
    <body>
      <h1>iSQL*Plus Dynamic Report - Query by Last Name</h1>
      <form method=get action="http://machine/isqlplus">
      <input type="hidden" name="script"
            value="http://machine/employee_name.sql">
        Enter last name of employee:
        <input type="text" name="last_name" size="20">
        <input type="submit" value="Run Report">
        </form>
    </body>
    </html>

Replace "http://machine/" with the appropriate host name, domain name and port number of your Oracle HTTP Server, for example, "http://machine.oracle.com:7777/".

The name of the INPUT TYPE should be the same as the substitution variable name in the SQL*Plus script. For example, the input field:

    <input type="text" name="last_name" size="20">

maps to the substitution variable "&last_name" in employee_name.sql.

Load myreport.html in your web browser. Enter a name or partial name in the text field, for example, "Fay". Click the Run Report button. iSQL*Plus will prompt for database connection details and then execute the script. The Employee Details report is displayed in your web browser.

You could add Javascript to the HTML form to do any desired browser-side validation of the values entered.

9.17 Using a Variable for the SQL*Plus Return Status

To use the value of a substitution variable called "myv" as the SQL*Plus return status, use:

    EXIT myv

No ampersand (&) prefix is required before the substitution variable name.

A numeric bind variable requires a colon (:) prefix:

    EXIT :mybv

9.18 Putting the Username and Database in the Prompt

In SQL*Plus 10g add this to your glogin.sql or login.sql:

    set sqlprompt "_user'@'_connect_identifier:SQL> "

For customized prompts that query the database make sure to explicitly DEFINE any referenced substitution variables. Glogin.sql and login.sql can get run when there is no database connection. Defining variables prevents the user being prompted for values when the query fails and the variables do not get defined by it:

    set termout off
    define myv = 'Not connected'
    column myc new_value myv
    select user||'@'||global_name myc from global_name;
    set sqlprompt '&myv:SQL> '
    set termout on

SQL*Plus 9.2 and earlier don't re-execute glogin.sql and login.sql after CONNECT commands. Also variables in the SQLPROMPT are not dynamically substituted. It is possible to use the query script given above, but beware that the prompt will only be valid for the original connection.

Oracle 11gR2 XE Beta is now available

cj — Fri, 1 Apr 2011 14:17:27 -0500

Oracle 11gR2 Express Edition ("XE") Beta is now downloadable for Linux x86_64 and Windows (32-Bit). Oracle XE is a free, slimmed down version of the full Oracle DB. Documentation for XE 11gR2 is here.

One of the Oracle features retained by XE 11gR2 is DRCP support. This is the first XE release with DRCP so now you can make the most of limited resources using PHP again a small footprint DB.

The big XE change for Linux users is that it is now 64-bit instead of 32-bit. With Linux production systems really rooted in a 64-bit world it makes sense for XE to head in that direction too.

Report any XE 11gR2 Beta issues at the dedicated OTN forum.

To compile PHP with XE 11gR2 Beta code, edit $ORACLE_HOME/lib/sysliblist and remove the references to the two libraries -lipgo -lsvml (Oracle bug 12318656). Before the Beta release I had picked up on another compilation gotcha but didn't spot this sysliblist issue. Dang. Luckily, that's what Beta programs are for!

cx_Oracle 5.1 for Python is Available

cj — Tue, 29 Mar 2011 19:21:52 -0500

Anthony Tuininga has released the cx_Oracle 5.1 extension for Python. His announcement email has the details: http://mail.python.org/pipermail/python-announce-list/2011-March/008876.html.

Building PHP 5.3 with Oracle HTTP Server 11g

cj — Wed, 23 Mar 2011 11:13:24 -0500

This post has instructions for building PHP with Oracle HTTP Server 11g (OHS). It is based on the older note Using PHP with Oracle HTTP Server 11g.

I used an uncommon OS, but the instructions should be very similar for other systems. This post is in the "blogging it so I can find it again" category.

I recently had the so-called "pleasure" of building PHP on HP/UX Itanium. This is a platform that I'm very glad to see Oracle's announcement: Oracle Stops All Software Development For Intel Itanium Microprocessor

I get the impression that not a lot of people in the PHP community are using Itanium. There are enough warnings during PHP compilation that would make me extremely uncomfortable using it for production applications. A PHP on Itanium installation bug that I logged long ago has not been patched, indicating to me that not many PHP people are investing time in the platform. I can't recommend using Itanium platform for PHP.

Anyway, I had a need and here are the steps for posterity.

Steps to build PHP 5.3.5 with OHS 11g (with reference to HP/UX Itanium 64 bit)

1. Check OS & compiler

    $ uname -a
    HP-UX B.11.31 U ia64 2921109885 unlimited-user license

    $ cc --version
    cc: HP C/aC++ B3910B A.06.23 [May 18, 2009]

I saw various issues with the whole HP/UX build environment, including total compilation failure when using an older (different??) compiler than this.

2a. Set the Oracle environment. The Oracle home contains an instance of OHS.

     export ORACLE_HOME=/path/to/your/oracle-home
     export ORACLE_INSTANCE=$ORACLE_HOME/instances/instance1

     export CONFIG_FILE_PATH=$ORACLE_INSTANCE/config/OHS/ohs1
     export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ohs/lib:$LD_LIBRARY_PATH

2b. Set the PHP compiler environment.

     export CFLAGS='+O2 +DD64'
     export CC=/bin/cc

You'll need to check and experiement with the best compiler options. On Itanium +DD64 is needed to create a 64bit binary usable with OHS.

3. Download and extract the Oracle InstantClient 11.1.0.7.0 SDK ZIP file from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html e.g.: http://download.oracle.com/otn/hp/instantclient/111070/sdk-11.1.0.7.0-hpux-ia64.zip

     unzip sdk-11.1.0.7.0-hpux-ia64.zip

4. Copy the new header files

     cp instantclient_11_1/sdk/include/*.h $ORACLE_HOME/rdbms/demo

5. Extract the php-5.3.5 bundle to a working directory:

     gunzip php-5.3.5.tar.gz
     tar -xf php-5.3.5.tar

6. Change to the extracted PHP directory:

     cd php-5.3.5

7. Edit PHP's 'configure' and change 'sl' to 'so' in the 'hpux' case at about line 3720: (This is the Itanium specific part of the patch in PHP PECL Bug 15016)

     case $host_alias in
     *hpux*)
       SHLIB_SUFFIX_NAME=so
       SHLIB_DL_SUFFIX_NAME=so
       ;;

8. If using PHP 5.3.6 or earlier, edit PHP's 'configure' script and change both occurrences of:

      APACHE_VERSION=`expr $4 \* 1000000 + $5 \* 1000 + $6`

to:

      APACHE_VERSION=`expr $6 \* 1000000 + $7 \* 1000 + $8`

This step is missing from the OHS 11g PHP installation instructions. It is needed because OHS's Apache reports its error number differently. See PHP bug 54084, which I just fixed in the 5.3 and trunk branches of the PHP source code.

9. Clean up if you are repeating the steps and re-running 'configure' with different options:

      make distclean
      rm -rf config.cache autom4te.cache

10. Configure & build PHP with whatever options:

      ./configure --disable-all --with-apxs2=$ORACLE_HOME/ohs/bin/apxs \
                  --with-oci8=$ORACLE_HOME --disable-rpath \
                  --prefix=$ORACLE_HOME --with-config-file-path=$CONFIG_FILE_PATH
      make

This produces a bunch of compiler warnings that would make me very uncomfortable to have running in production.

Notice I used --disable-all. The system libraries are a little different on Itanium and I didn't have access to libraries such as libxml2. Perhaps by working out the installation steps or by building third-party software bundles you could get the dependencies for other PHP extensions.

11. Copy a default php.ini file:

      cp php.ini-devlopment $CONFIG_FILE_PATH/php.ini

      cp php.ini-production $CONFIG_FILE_PATH/php.ini

12. Edit $CONFIG_FILE_PATH/php.ini and add a timezone line (this is mandatory for PHP 5.3):

      date.timezone=America/Los_Angeles

13. Edit $CONFIG_FILE_PATH/php.ini and add 'E' to 'variables_order' so PHP's run-tests.php passes the Oracle environment correctly when running tests:

      variables_order = "EGPCS"

This can be removed later.

14. Modify run-tests.php and change 60 to 600. This prevents long Oracle tests from timing out.

15. Edit ext/oci8/tests/details.inc and set the SYSTEM password and connection string to your Oracle DB.

16. Set the PHP environment so PHP tests don't try to email results to the world:

      export NO_INTERACTION=no

17. Test the PHP command-line binary:

      make test

The OCI8 tests can be run with:

      make test TESTS=ext/oci8

18. Investigate failed tests. Also compare the results with 64 bit Linux results. A significant number of tests do pass on Itanium, which gives some hope that much of the PHP code base works, even if it is delicate and likely to be prone to memory corruption issues.

19. If all is OK, install the PHP binaries:

      make install

20. Edit $CONFIG_FILE_PATH/httpd.conf and add a line:

     AddType application/x-httpd-php .php

21. Restart OHS:

     cd $ORACLE_INSTANCE/bin
     ./opmnctl start
     ./opmnctl stopproc ias-component=ohs1
     ./opmnctl startproc ias-component=ohs1

22. Create a phpinfo file:

      echo '<?php phpinfo(); ?>' > $ORACLE_INSTANCE/config/OHS/ohs1/htdocs/phpinfo.php

23. Check phpinfo on the port OHS is configured for. In my case it was port 7777:

      http://localhost:7777/phpinfo.php

24. Create and run some OCI8 scripts in $ORACLE_INSTANCE/config/OHS/ohs1/htdocs at your leisure.

To repeat my earlier warning, I don't recommend using PHP on Itanium.

Netretail's online retail operation benefits from personal contact

cj — Wed, 16 Mar 2011 14:11:56 -0500

Hot on oracle.com is a snapshot of Netretail Holding B.V. profiling their use of PHP and Oracle technology such as Oracle RAC cluster database to become a leading online retailer across Central and Eastern Europe.

We've also just refreshed our key PHP Scalability and High Availability whitepaper which talks about connection pooling (DRCP) and Fast Application Notification (FAN). We brought it up to date for 11gR2 and PHP 5.3. It now includes the new 11gR2 V$CPOOL_CONN_INFO view, the new columns for DBA_CPOOL_INFO, information about LOGOFF triggers, and information about the support for Client Result Caching with DRCP.

Back to Netretail. Two of their secrets to success are keeping technically up to date, and networking. That is, networking in the business sense. I had the pleasure of meeting Michal Táborský (@whizz), the Chief System Architect, when he was in California for a Velocity conference. Michal took time to visit Oracle HQ and talk with our developers about his then current architecture and future needs. I also met his manager at last year's Oracle OpenWorld conference.

Having built up a relationship with us, Netretail now has access to Oracle Development staff. While this will never bypass Oracle Support (which have tools, systems etc that are needed and useful for resolving issues), it makes communication easier for some classes of questions. It helps discussions that will let us improve Oracle products, and make Netretail stronger.

I like this. And there's no reason why you can't talk with us too. You know where to email me.

More on PHP and Oracle 11gR2 Improvements to Client Result Caching

cj — Thu, 3 Mar 2011 12:36:33 -0600

Oracle 11.2 brought several improvements to Client Result Caching. CRC is way for the results of queries to be cached in the database client process for reuse. In an Oracle OpenWorld presentation "Best Practices for Developing Performant Application" my colleague Luxi Chidambaran had a (non-PHP generated) graph for the Nile benchmark that shows a DB CPU reduction up to 600% and response times up to 22% faster when using CRC.

Sometimes CRC is called the "Consistent Client Cache" because Oracle automatically invalidates the cache if table data is changed. This makes it easy to use without needing application logic rewrites. There are a few simple database settings to turn on and tune CRC, so management is also easy.

PHP OCI8 as a "client" of the database can use CRC. The cache is per-process, so plan carefully before caching large data sets. Tables that are candidates for caching are look-up tables where the network transfer cost dominates.

CRC is really easy in 11.2 - I'll get to that in a moment. It was also pretty easy in Oracle 11.1 but it needed some tiny application changes. In PHP it was used like:

$s = oci_parse($c, "select /*+ result_cache */ * from employees");
oci_execute($s, OCI_NO_AUTO_COMMIT); // Use OCI_DEFAULT in OCI8 <= 1.3
oci_fetch_all($s, $res);

I blogged about this in the past. The query had to include a specific hint that you wanted the results cached, and you needed to turn off auto committing during execution either with the OCI_DEFAULT flag or its new, better-named alias OCI_NO_AUTO_COMMIT. The no-commit flag rule didn't seem reasonable to me because most people wouldn't be specific about the commit state for a query.

Now in Oracle 11.2, DBAs can now nominate tables for caching, either with CREATE TABLE or ALTER TABLE. That means you don't need the query hint anymore. As well, the no-commit flag requirement has been lifted. Your code can now look like:

$s = oci_parse($c, "select * from employees");
oci_execute($s);
oci_fetch_all($s, $res);

Since your code probably already looks like this, your DBA can find the top queries in the database and simply tune the system by turning on CRC in the database and issuing an ALTER TABLE statement for candidate tables. Voila.

Another CRC improvement in Oracle 11.2 is that it works with DRCP connection pooling.

There is some fine print about what is and isn't cached, check the Oracle manuals for details. If you're using 11.1 or non-DRCP "dedicated servers" then make sure you use oci_pconnect() persistent connections. Also in PHP don't bind strings in the query, although binding as SQLT_INT is OK.

Profiling NetBeans 7.0 Beta 2 and Reporting Problems

cj — Tue, 1 Mar 2011 13:12:10 -0600

With NetBeans 7.0 recently going into Beta 2 phase, now is the time to test it out properly and report issues. The development team has been squashing bugs, including memory issues with the PHP bundle.

There are some great new PHP related features in NetBeans 7.0, so you know you want to try it out.

If you identify something wrong with NetBeans, please report it following the guidelines http://wiki.netbeans.org/IssueReportingGuidelines

Depending on the issues, data to attach to the report is mentioned on: http://wiki.netbeans.org/FaqLogMessagesFile and http://wiki.netbeans.org/FaqProfileMeNow

If you have a memory issue then a memory dump would also be useful. Run the jmap tool for this. There is some background information on http://wiki.netbeans.org/FaqMemoryDump. Here's how I used it.

First I set my environment to match the JDK used by NetBeans. In my case I am using a nightly build so the JDK is in the configuration file under $HOME/netbeans-dev-201102210501:

$ egrep netbeans_jdkhome $HOME/netbeans-dev-201102210501/etc/netbeans.conf

netbeans_jdkhome="/home/cjones/src/jdk1.6.0_24"

$ export JAVA_HOME=/home/cjones/src/jdk1.6.0_24

$ export PATH=$JAVA_HOME/bin:$PATH

Next, I found the correct process number to examine:

$ ps -ef | egrep 'netbeans|jdk'
cjones 23230 1 0 16:07 ? 00:00:00 /bin/bash /home/cjones/netbeans-
cjones 23438 23230 2 16:07 ? 00:00:09 /home/cjones/src/jdk1.6.0_24/bin

Finally I used the parent JDK process as the jmap argument:

$ jmap -histo:live 23438
num     #instances         #bytes class name
----------------------------------------------
   1:         12075        9028656 [I
   2:         49535        6581920 <constmethodklass>
   3:         49535        3964128 <methodklass>
   4:         80256        3840776 <symbolklass>
   5:         36093        3635336 [C
   6:          5095        3341312 <constantpoolklass>
   7:          5095        2486016 <instanceklassklass>
   8:          4325        1961432 <constantpoolcacheklass>
   9:         18729        1763976 [B
10:         59952        1438848 java.util.HashMap$Entry
. . .

This histogram memory report will help identify the kind of memory issues you are seeing. It may not be as complete as an often tens of megabyte jmap -dump:live,file=/tmp/nbheap.log 23438 heap dump, but is much more easily attached to a bug report.

If you want to keep up to date with NetBeans, nightly builds are at: http://bits.netbeans.org/download/trunk/nightly/latest/zip/

PHP OCI8 and Oracle 11g DRCP Connection Pooling in Pictures

cj — Mon, 21 Feb 2011 16:32:28 -0600

Here is a screen shot from a PHP OCI8 connection pooling demo that I like to run. It graphically shows how little database host memory is needed when using DRCP connection pooling with Oracle Database 11g.

Migrating to DRCP can be as simple as starting the pool and changing the connection string in your PHP application.

The script that generated the data for this graph was a simple "Parts" query application being run under various simulated user loads. I was running the database on a small Oracle Linux server with just 2G of memory. I used PHP OCI8 1.4. Apache is in pre-fork mode, as needed for PHP.

Each graph has time on the horizontal access in arbitrary 'tick' time units.

Click the image to see it full sized.

Pooled connections

Beginning with the top left graph, At tick time 65 I used Apache's 'ab' tool to start 100 concurrent 'users' running the application. These users connected to the database using DRCP:

    $c = oci_pconnect('phpdemo', 'welcome', 'myhost/orcl:pooled');

A second hundred DRCP users were added to the system at tick 80 and a final hundred users added at tick 100. At about tick 110 I stopped the test and restarted Apache. This closed all the connections.

The bottom left graph shows the number of statements being executed by the database per second, with some spikes for background database activity and some variability for this small test. Each extra batch of users adds another 'step' of load to the system.

Looking at the top right Server Process graph shows the database server processes doing the query work for each web user. As user load is added, the DRCP server pool increases (in green). The pool is initially at its default size 4 and quickly ramps up to about (I'm guessing) 35. At tick time 100 the pool increases to my configured maximum of 40 processes. Those 40 processes are doing the query work for all 300 web users. When I stopped the test at tick 110, the pooled processes remained open waiting for more users to connect. If I had left the test quiet for the DRCP 'inactivity_timeout' period (300 seconds by default), the pool would have shrunk back to 4 processes.

Looking at the bottom right, you can see the amount of memory being consumed by the database. During the initial quiet period about 500M of memory was in use. The absolute number is just an indication of my particular DB configuration. As the number of pooled processes increases, each process needs more memory. You can see the shape of the memory graph echoes the Server Process graph above it. Each of the 300 web users will also need a few kilobytes but this is almost too small to see on the graph.

Non-pooled connections

Compare the DRCP case with using 'dedicated server' processes.

At tick 140 I started 100 web users who did not use pooled connections:

    $c = oci_pconnect('phpdemo', 'welcome', 'myhost/orcl');

This connection string change is the only difference between the two tests.

At ticks 155 and 165 I started two more batches of 100 simulated users each. At about tick 195 I stopped the user load but left Apache running. Apache then gradually returned to its quiescent state, killing idle httpd processes and producing the downward slope at the right of the graphs as the persistent database connection in each Apache process was closed.

The Executions per Second graph on the bottom left shows the same step increases as for the earlier DRCP case. The database is handling this load.

But look at the number of Server processes on the top right graph. There is now a one-to-one correspondence between Apache/PHP processes and DB server processes. Each PHP processes has one DB server processes dedicated to it. Hence the term 'dedicated server'.

The memory required on the database is proportional to all those database server processes started. Almost all my system's memory was consumed. I doubt it would have coped with any more user load.

Summary

Oracle Database 11g DRCP connection pooling significantly reduces database host memory requirements allow more system memory to be allocated for the SGA and allowing the system to scale to handled thousands of concurrent PHP users.

Even for small systems, using DRCP allows more web users to be active.

More information about PHP and DRCP can be found in the PHP Scalability and High Availability chapter of The Underground PHP and Oracle Manual. Update: the whitepaper that the chapter was based on shows a benchmark where we supported 20,000 users on a database using commodity hardware with 2G memory.

Learn to use PHP and Python with Oracle Database

cj — Wed, 15 Dec 2010 20:25:38 -0600

The Oracle Learning Library has posted up the latest "Oracle By Example" labs giving an introduction to PHP & Python with the Oracle Database :

Using PHP with Oracle Database 11g - a basic introduction
Developing a PHP Web Application with Oracle Database 11g - a Zend Framework application using the NetBeans IDE
Using Python With Oracle Database 11g - a basic introduction
Using the Django Framework with Python and Oracle Database 11g - a basic web application

PHP OCI8 1.4.5 is on PECL

cj — Thu, 9 Dec 2010 20:37:59 -0600

I just released OCI8 1.4.5 on PECL. It is the same code base included in PHP 5.3.4. It can be used to upgrade OCI8 on older PHP releases. OCI8 1.4.5 has one change since OCI8 1.4.4 made as part of PHP-wide security fix to disallow null bytes in filenames.

Fast In-Memory Caching with JRuby & Jython Using Oracle Coherence

cj — Mon, 6 Dec 2010 10:27:24 -0600

My Aussie colleage Pas Apicella has been evaluating JRuby & Jython. One test was using them to access Oracle Coherence, a "replicated and distributed (partitioned) data management and caching services on top of a reliable, highly scalable peer-to-peer clustering protocol.". He blogged and shows code samples here.

PHP and Oracle: Christopher Jones

PHP-FPM FastCGI Process Manager with Apache 2

PHP 5.4.0 RPMs for 64bit Oracle Linux 5.x are available

PHP 5.4 is out; your work begins now

PHP VirtualBox VM has been refreshed

PHP 5.3.9 RPMs Available for Testing

PHP OCI8 1.4.7 is available on PECL

PHP oci_password_change() ORA-1017 gotcha with Oracle Database 11.2.0.3

Scripting Language Related Sessions at Oracle OpenWorld and JavaOne, October 2011

Exhibition Hall

Scripting Sessions, Birds-of-a-Feather Meetings, and Hands-on-Labs at OOW

Scripting at JavaOne

Linux

MySQL

Oracle 11.2 XE with newly updated PHP Developer's Guide is available

PHP 5.3.8 RPMs are on oss.oracle.com

PHP OCI8 1.4.6 is Available

Oracle Enhanced Adapter 1.4.0 for Ruby on Rails is Available

Oracle Resources for PHP

Mid conference wrap up

Oracle at the Dutch PHP Conference & php|tek

Oracle Tuxedo: A New way to Run PHP Applications

PHP 5.4 is Gathering Momentum

Configuring PHP GD on Ubuntu 11.04 "Natty"

Malicious website use will never go away: how do you manage it?

SQL*Plus 10.1 Substitution Variables

Contents

1 Introduction

2 Using Substitution Variables

2.1 Creating, Showing and Deleting Substitution Variables

2.2 Referencing Substitution Variables

2.3 Prompting for Undefined Variables

2.4 Difference Between "&" and "&&" Prefixes

2.5 Storing a Query Column Value in a Substitution Variable

2.6 Predefined Substitution Variables

2.7 Script Parameters

2.8 More on Substitution Variables

3 Using Bind Variables

3.1 Assigning Substitution Variables to Bind Variables

3.2 Assigning Bind Variables to Substitution Variables

4 Using System Variables

4.1 System Variables Influencing Substitution Variables

4.1.1 SET CONCAT

4.1.2 SET DEFINE

4.1.3 SET ESCAPE

4.1.4 SET NULL

4.1.5 SET NUMFORMAT

4.1.6 SET NUMWIDTH

4.1.7 SET SQLPROMPT

4.1.8 SET VERIFY

4.2 System Variables in Titles and EXIT

5 SQL*Plus Substitution Variable Commands

5.1 ACCEPT Command

5.2 COLUMN Command

5.3 DEFINE Command

5.4 EDIT Command

5.5 EXIT Command

5.6 HOST Command

5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands

5.7.1 Using "&" Prefixes With Title Variables

5.7.2 Variables and Text Spacing in Titles

5.8 UNDEFINE Command

5.9 WHENEVER Command

6 Substitution Variable Namespace, Types, Formats and Limits

6.1 Substitution Variable Namespace

6.2 Substitution Variable Types

6.3 Substitution Variable Formats

6.4 Substitution Variable Limits

7 iSQL*Plus and Substitution Variables

7.1 iSQL*Plus 9i and SQL*Plus Substitution Variable Compatibility

7.2 iSQL*Plus Parameters

8 Substitution Variable Summary

9 Substitution Variable Examples

9.1 Setting a Substitution Variable's Value

9.2 Using a Substitution Variable

9.3 Finding All Defined Substitution Variables

9.4 Inserting Data Containing "&" Without Being Prompted

9.5 Putting the Current Date in a Spool File Name

9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable

9.7 Putting a Period After a Substitution Variable

7.1 iSQLPlus 9i and SQLPlus Substitution Variable Compatibility