Accessing Oracle from CGI Scripts Using Perl

Danny Lawrence and Scott Beimer

How do you communicate with an Oracle database through the World Wide Web? You can use Common Gateway Interface (CGI) scripts to communicate with the Web, but there's no direct way for a CGI script to access an Oracle database. Oracle provides a library of C functions known as the Oracle Call Interface (OCI), which allows C programs to communicate with the database, but these calls are cumbersome and require too much setup time for simple applications. We needed something easier to use than OCI calls - something that could be used for CGI code as well as for reports and scripts.

So, we decided to write an extension to Perl. Perl is already widely used for reports and CGI scripts, so it makes a natural link between CGI and Oracle. Although we have since discovered others doing similar work, we still like our extension. It is simple and does what we want. We can run it against V6 and V7 Oracle databases, and we can execute any select, DML, or PL/SQL block. We run it with Perl 5.001 on ISC 486 and Sun Solaris, and our scripts can run unmodified on either platform.

Perl

Perl is popular for its flexibility and for the ease with which the language can be extended with C subroutines. You can link in your own external libraries, and then call these libraries from within a Perl script. Often, just a few wrapper functions are needed to call the library and to convert datatypes back and forth between C and Perl. These wrapper functions are written in the XS language, which is mostly C interspersed with directives to the preprocessor xsubpp. Through the use of XS directives, datatype conversions and stack manipulations are mostly hidden. The Perl source distribution includes very good documentation.

The Oracle Call Interface

C programs can directly save and retrieve data in Oracle via a set of functions known as OCI functions. These are very powerful. You only need a few to accomplish everything related to connecting to a database, converting datatypes, buffering, and error checking. Unfortunately, these functions are too complex to be used directly in simple, slap-together programs. By the time you've declared and initialized various private Oracle structs, parsed the SQL statement, set up input and output buffers, and checked for errors, you may wonder if you will ever get out alive. And, you still haven't retrieved one row of data. These OCI calls are fully explained in the Oracle documentation, and examples of usage are in the XS code included in this article. But, you don't need to know anything about OCI to use this Perl extension.

Oracle7 introduced several new functions for OCI programs. The V6 interfaces are still available, but run considerably slower on the V7 platform. Our OCI package was originally written for V6, so it doesn't take advantage of any of the new functions yet.

The OCI Perl Extension

Our Oci package is a set of routines that glue the Oracle OCI calls to Perl, but we didn't just make the function usage a carryover from the low-level oracle OCI syntax. We tried to hide as many details as possible. Our Oci routines handle cursor allocation and freeing, variable binding, and input buffering almost invisibly. In fact, the Perl usage was designed to be very similar to PL/SQL, while still being Perlish. Table 1 shows a summary of the Perl functions in the Oci package. (See Listing 1 for the entire program.)

Perl wrapper subroutines that check the return status and print error messages are included in ociwrap.pl. These subroutines reduce the clutter in the main script and explain errors as much as possible. For example, the ckcursor() subroutine displays the actual text of the SQL statement when there is an error, which is a big help.

Usage

The function usage is fairly straightforward, but a couple of hints and examples might be useful. The descriptions below are given in the order in which you would use the functions in your scripts.

Connecting

oraconnect() must be called before any other Oci functions. The connect string is formatted the same as in all other Oracle products. If the connect string is coded directly into the script, use single quotes to protect login names such as ops$scott from being altered by Perl. If SQL*Net is up and running, connect strings like scott/tiger@T:host:DB work fine. Remember to call oradisconnect() before your script exits.

Cursors

You need a cursor for every SQL statement. Every time you use oracursor() to parse a statement, a cursor is returned. Oracle uses this cursor to remember things about the statement, such as the text of the statement, the current status, and buffer locations. In this Perl package, cursors can be viewed as similar to C FILE handles. The value returned by fopen() is used in other C file I/O operations. Similarly, the value returned by oracursor() is used in other Oracle calls.

The perl here-document syntax provides an elegant mechanism for declaring multiline SQL code:

($retval, $c) = oracursor(<<EOS);
select 'X'
from dual
EOS

or

($retval, $c) = oracursor(<<EOS);
begin
:X := :Y;
end;
EOS

Cursor variables (the $c above) can be local to subroutines. When the variable goes out of scope, Oracle and Perl resources allocated to that cursor are automatically released. You can use the same cursor variable for different statements at different times. Perl knows when to free resources. Note also that PL/SQL blocks require the trailing semicolon, but straight SQL blocks do not.

Binding

There are two ways to bind Perl data to SQL substitution variables - by value and by reference. If a variable is bound by value, it cannot be changed by a PL/SQL block. If a variable is bound by reference, then variable assignments within the PL/SQL block will be visible outside the block. Furthermore, the contents of a variable bound by value are set at the time of the orabind() call, while referenced binds are bound at either oraopen() or oraexec() time. Referenced binding is more flexible, but sometimes binding by value is more useful.

For instance, if you want to ensure the Perl variable is read-only, bind by value. Even assignments to the corresponding substitution variable within the PL/SQL block will not be seen by Perl. Binding by value is also useful within Perl subroutines. If the subroutine is called with parameters that are to be bound to substitution variables, then binding by reference won't work. The variable's locations are different for every call. In this case, it is better to re-bind by value on every call to the subroutine.

A bind by value can be as simple as using a string:

$retval = orabind($c, ":X", "value");

In this case, the SQL can use the value of :X, but cannot assign values to it. In the bind below, however, values can be assigned to :Y because a reference to $y is passed. If the PL/SQL block corresponding to $c assigns a value to :Y, the value of $y will be set accordingly.

$y = "";
$retval = orabind($c, ":Y",$y, 25);

Note the use of a backslash before the $y. This is Perl's syntax for a reference to $y. When references are used, the referenced variable must already have been defined. Assigning a null string, as with $y above, is sufficient.

The fourth parameter, length, is used to ensure $y has enough space even though, as in the example above, it might still be too short for what you expect to retrieve. One neat thing about referenced binds is that the value of the referenced variable can change between orabind() and oraopen() or oraexec(). The last value assigned is the one that will be bound, unlike binds by value.

Opening, Closing, and Execing

Depending on the type of statement used in the oracursor() call, you will want to use either the pair oraopen()/oraclose(), or the single function oraexec(), but not both. oraopen() and oraclose() are equivalent to PL/SQL's open and close for select cursors. All other types of cursors should use oraexec().

Fetching

orafetch() tries to be Perlish by returning the selected rows as an array of scalars. In this way, it is a mirror image of PL/SQL's fetch. When PL/SQL says:

fetch foo_cur into var1, var2;

the equivalent Perl is:

($retval, $var1, $var2) = orafetch($foo_cur);

The variables are retrieved in the same order as the items in the SQL select list. You must make sure you use the right number of Perl variables in the right order. When there are no more rows to fetch, $oerr is set to $ORA_NO_DATA_FOUND.

Examples

Examples of how to use these functions can be in tabhtml.cgi and comment.cgi. tabhtml.cgi prompts for an Oracle connect string and then displays a description of all tables owned by the login user. The html code returned is generated dynamically based on data in the system views.

comment.cgi is a script that simplifies documenting your tables and fields in the database. You enter the table or field name and a comment, and the form inserts it. The next time tabhtml.cgi is executed, the comments will be displayed.

Installation

Installation specifics for Oci vary by platform because of the different locations of Oracle headers and libraries. First, download the sources and put them in their own directory. You'll see a file called Makefile.PL. This file must be edited, and the LIBS, DEFINE, and INC parameters must be set for your system. On our Sparc 20 running Solaris 2.4 and Oracle 7.2, we use the following Makefile.PL:

use ExtUtils::MakeMaker;
# See lib/ExtUtils/MakeMaker.pm for details of how to
# influence the contents of the Makefile that is written.
WriteMakefile(
'NAME' => 'Oci',
'VERSION' => '0.1',
'LIBS' => ['-L/disk00/app/oracle/product/7.2.2/ \
lib -locic -L. -lsqlnet -lora -lsqlnet -lora \
-lnlsrtl3 -lc3v6 -lcore3 -lnlsrtl3 -lcore3 \
-losntab -lsqlnet -lora -lc3v6'],
'DEFINE' => '-DORACLE7',
'INC' => '-I${ORACLE_HOME}/sqllib/public
-I${ORACLE_HOME}/rdbms/demo',
);

The -losntab is our own invention. Oracle provides osntab.o, but because of this MakeMaker syntax, only .a archives are allowed. So, we built an archive libosntab.a containing only osntab.o. Also note that we spelled out the library path in the LIBS section instead of using a variable such as ${ORACLE_HOME}. There is probably an easier way to do this, but, alas, we're not Perl-building experts.

Once you've edited Makefile.PL, type:

perl Makefile.PL
make perl

If you got the parameters right, a new Perl executable will be sitting in the directory ready to be used. If you are a mere mortal, though, you will discover that the LIBS parameter takes a lot of twiddling. The Oracle demo makefiles for your system should provide enough information for you to get started.

There are other parameters in Makefile.PL that you might want to set. LINKTYPE lets you specify static or dynamic linking on platforms that support it. The Perl XS Tutorial document in the Perl 5.002 release explains all about Makefile.PL and building Perl.

Last Bit

Since we built this extension, we've had all sorts of fun with it. CGI scripts are almost too easy now, and we've discovered that even Oracle reporting is easier. We don't even need the Oracle reports module. We use SQL*Forms to collect report parameters, then have SQL*Forms spawn a Perl script in the background. The report runs, and the user gets to use his/her terminal right away, instead of waiting for the report to finish.

Our OCI extension is very simple, so if you need a more industrial-strength extension, check out the DBI/DBD extensions on the Hermetica web site: http://www.hermetica.com/technologia/DBI/index.html.

About the Authors

Danny's business card says he is Computer Systems Manager at the Grants Pass Daily Courier in Oregon. Scott's says he is Computer Services (we don't know exactly what that means). They have a combined 872 years experience in writing in the third person. They can be reached at danny@magick.net or scottb@blarg.net.