Oracle and Perl

For some programming languages, there're two general methods to access an Oracle database from the program, using the Oracle tool SQL*Plus and using the language-specific module (library). This article takes Perl as the example language.

1. Piping to SQL*Plus

Most programmers are unaware of this approach, partly because they're more adept at the language than the Oracle database. Taking Perl as an example, the code below shows how

#!/usr/local/bin/perl -w

open ORA, "| $ORACLE_HOME/bin/sqlplus -s $usr/$pasw" or die "Can't pipe to sqlplus: $!";
print ORA "exec mystoredprocedure\n";
print ORA "set trimspool on pagesize 500 linesize 200 colsep '	'\n"
print ORA "spool output.txt\n"
print ORA "select * from mytable;\n";
print ORA "spool off\n";
print ORA "exit\n";
close ORA;
If you don't want to say print ORA every time, you can simply insert a select ORA; before the first print and say just print afterwards. Note that SQL statements have to be followed by a semicolon or slash on the next line, and then \n, enclosed in the Perl string. SQL*Plus commands such as set, exec, spool don't need to be followed by semicolon (but don't forget \n).

In a UNIX shell script, you do something similar with a here document: sqlplus -s usr/pasw <<EOF followed by SQL*Plus commands one line at a time and end with EOF. Or you echo SQL*Plus commands and pipe into sqlplus -s usr/pasw. In Tcl and Expect, you open the pipe with set ORA [ open "|/thepath/sqlplus -s usr/pasw" w ] and possibly with the ability to both write and read. In C, you have to use popen(3) and pclose(3) or more efficiently and securely, use pipe(2), combined with system(3). In the ostensibly powerful Java, you might get away with PipedReader and PipedWriter. I know of no way to achieve this in any flavor of BASIC including VB. However, whatever language you use, you can always launch sqlplus -s usr/pasw @sqlscript.sql arg1 arg2 at one shot, without continually injecting sql commands to sqlplus. But that may be limited in flexibility.

(Also See two-way communication with SQL*Plus in Appendix.)



2. Using a Language-Specific Module

This is almost standard and familiar to most programmers so I won't say too much. In Perl, the example code is as follows:

use DBI;

$dbh=DBI->connect("dbi:Oracle:DBName","usr","pasw",{AutoCommit=>0}) or die "Can't log in: $!";

$sth=$dbh->prepare("select mycolumn from mytable") or die "$DBI::errstr";
$sth->execute or die "Can't execute sth: $DBI::errstr.";

while (($mycolumn)=$sth->fetchrow_array)
 { print "$mycolumn" if defined $mycolumn;

The advantages and disadvantages are just the reverse of the above mentioned.

Which approach you take depends on your project. Suppose you have a choice. The following conditions favor the first approach over the second


Perl only has poor support of two-way communication with an external program. This means that open ORA, "| $ORACLE_HOME/bin/sqlplus -s $usr/$pasw |" does not work. Instead, you have to do this (modified from the example on p.456, Programming Perl 2nd ed or p.431, 3rd ed)

use IPC::Open2;
local (*Reader, *Writer);
$pid = open2(\*Reader, \*Writer, "e:/oracle/ora81/bin/sqlplus -s scott/tiger");
print Writer "set pagesize 100\n";
print Writer "select * from emp;\n";
print Writer "exit\n";
close Writer;	#have to close Writer before read

#have to read and print one line at a time
while (<Reader>)
 { print "$_";

close Reader;
waitpid($pid, 0);	#makes your program cleaner

The above is tested on my PC using ActiveState Perl. Unfortunately, there're two limitations: the Writer has to be closed before you can read from the Reader; you can only read one line at a time. This means you can't continue to do some work in one database connection, and therefore you can't be guaranteed of single transaction consistency.

An alternative is to use the underlying OS's support of two-way communication; specifically the support from the command line shell. You can't do it in DOS. But in UNIX, you can use here documents:

#!/usr/bin/perl -w

$s = qx{ $ORACLE_HOME/bin/sqlplus -s scott/tiger\@ORCL <<EOF
set pages 100 lines 1000 head off
select * from emp;
EOF };

@lines = split /\n/, $s;
for ($i = 0; $i < scalar @lines; $i++)
 { print "This is line $i: $lines[$i].\n";

The operator qx// is equivalent to `` in all shells (Perl also supports ``). Note that $s is an array of all lines, as if the result set was a monolithic document slurped into $s with $/ undefed.

To my Computer Page