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;

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.)

Advantages

• Calling SQL*Plus does not need the necessary database access module or library such as DBI. It's possible you have not installed that module or library or not installed correctly. Don't underestimate this possibility. Even a version difference between Oracle and Perl DBI module or DBD driver may stop you from using the module (driver).
• Secondly, Oracle SQL*Plus has its superb reporting capability not many programmers are familiar with. It can break the output into sections based on the range of values for a particular column and do some calculation on each section. You would write a lot of code on your own to generate such a report, probably in a much less efficient way than Oracle does it.
• Lastly, the output of the query is usually sent to a file especially in Perl (Perl does not support two-way piping unless you guarantee non-buffered stream as in the UNIX command cat -u, and you use IPC::Open2 or IPC::Open3; see Appendix). Writing to a file means much less memory requirement, particularly if the result set is huge. You can use Perl to process the result one line at a time by reading the output file (tip set colsep to a very uncommon character such as backtick or string so it won't occur in varchar2 text; set trimspool on). Note that calculating memory requirement purely based on array sizes is very misleading. There're a lot of factors beyond how many bytes your string takes and how many strings are in your arrays. I once worked on a project where the previous programmer wrote VB code using multiple huge multidimensional arrays to contain the query result set. The program stopped working for no apparent reason and the programmer could never reproduce the error when he worked on a small sample set. I debugged and gave up and rewrote it in Perl in about one-fifth code length and never heard any crash from our client. I spooled the result to a text file.

• The most important disadvantage may be the inability or complexity of some languages to support this approach. It may be so cumbersome, if possible, that you won't even consider launching SQL*Plus as an external command as an option. It's quite easy in most scripting languages. But I think pipes in most compiled languages are poorly supported.
• Since you rely on SQL*Plus, you have to have that tool and probably the entire Oracle client software installed on the machine. (Of course, using the language specific module such as DBI also relies on Oracle SQL*Net, except Java thin client.)
• With the power of SQL and SQL*Plus comes with the complexity. Most programmers know basic SQL but have very limited skills in SQL*Plus (most don't even know their difference). This is exacerbated by the demand of excellent skills in text string processing and regular expressions when you read the output file. If you predict your query result will need more and more processing in the future of your project, don't take this approach. I learned a lesson the hard way by starting a project with piping to SQL*Plus and ending with more and more time rewriting regular expressions to parse the output file, because managers kept demanding more business logic.
• If you write the result to a file, obviously your program is slower than if you manipulate all results in memory. This is not necessarily a disadvantage, rather a trade-off between memory usage versus speed.

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;
 }

$sth->finish;
$dbh->disconnect;

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

• the query has only a few columns in the select list (or in the outermost select list if it contains subqueries)
• the query returns too many rows to the extent that you suspect there's a scaleability problem
• the query result doesn't need complicated re-processing and the processing can be easily done by sqlplus

Appendix

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;
exit
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