Array, TAF & load/unload features of DBI/DBD::Oracle
Oracle Tips by Mladen Gogala
Mladen Gogala is the author of "Easy
See the whole article:
Perl DBI and Oracle
|Mladen Gogala is available to assist you
with your toughest Perl DBI problems. Just call.
This is the ending part of this, hopefully
useful, document in which the author rides into sunset, after few
remarks and wisecracks. To achieve the true Hollywood happy end, I
must list and comment on the unsupported features and the most
appropriate use of Perl and Oracle combination. Without further
ado, here are the unsupported features that I consider important:
Transparent application failover (TAF).
Oracle DBI Array Interface
The absence of array interface is probably the
single worst problem of DBD::Oracle. DBI does have an array
interface. Here is a code snippet for an array bind:
my $INS="INSERT INTO TAB
ArrayTupleStatus => \my @status });
It will execute like a charm, without reporting
any errors, but it will do so by cheating,
that is by breaking the code above into a loop
in which the statement would be executed once for every iteration,
not just once per loop. To prove that, one can start SQL trace by
activating the event 10046 for that session.
That, of course, takes away all advantages of
array interface. The point is precisely in executing once per an
array and therefore decreasing the time spent in communicating with
database. That means that tools like SQL*Loader will be much, much
faster for loading of large data sets then Perl. Of course, one of
the intended uses for Perl in tandem with Oracle is precisely as a
fancy and a very smart loader.
Perl can parse any file, due to its phenomenal
regular expression capabilities, but the other part, the one that
would help me in writing Perl scripts to load data into my databases
is messed up beyond help and despite numerous promises, array
interface has still not been delivered. The same applies to the
direct loads: it doesn't work. The DBD::Oracle driver doesn't have
anything that would help you to load data faster.
Oracle DBI and RAC
Transparent failover capabilities are also very
important. Assembling RAC database is no longer excruciatingly
expensive. As a matter of fact, it can be done by anyone willing to
invest in 2 PC boxes with at least 1GB per PC, FireWire or multihost
SCSI adapter, corresponding external storage device, two additional
network interfaces (preferrably GigaBit ethernet) and a dedicated
network switch. That price is well within reach of an ordinary DBA
like me, without having to give up food and gas for six months.
As a result, commercial RAC configurations
abound these days. That makes TAF more important then ever. What is
TAF? Short answer to this question is that it is a mechanism that
makes it possible for an application to continue without
reconnecting to the database after the instance to which the
application was connected fails. TAF capabilities have several
levels. The lowest level is that the session content is preserved,
while the highest level implies moving the session transparently to
another instance, without losing the current transaction. In other
words, the user wouldn't even notice that the instance to which he
was connected has just failed.
Unfortunately, the term "user" doesn't cover
people running Perl scripts. If the instance fails, the script fails
too. No failover capabilities whatsoever are built in DBD::Oracle.
When to use the Perl DBI with Oracle
That means that Perl is not necessarily the
right tool for commercial applications. The most important area for
Perl is to help administrators with patchwork and customization not
covered by other, more solid and feature-rich commercial tools. If
someone decided to code the company's financial software in Perl,
the decision would imply a severe performance disadvantage when
compared with other programming languages and no TAF capabilities,
which would annoy users to no end during downtimes, whether
intentional or accidental.
Such an application would have serious
shortcomings when compared with competing application systems. That
is the reason why there are very few pieces of commercial software
which runs on top of Oracle RDBMS and is written in Perl.
In exchange for the tools and features that are
so desperately needed by people using Perl and Oracle, DBI offers a
whole bunch of metadata extraction, profiling and monitoring
capabilities which are, generally speaking, far inferior to the
Oracle RDBMS built in capabilities of the same type and completely
useless for an Oracle developer or a DBA.
That seems to be a general direction of DBI
development. From the recent debate on USENET, I know that DBI V2 is
planned and that the author has envisioned a really cool diagram
which would make it possible for DBI to get infected with
abbreviations like CORBA, SOAP, EJB and alike. Something as trivial
and useless as array interface or direct load capabilities wasn't
even mentioned. Cave people were very good at drawing flowcharts and
diagrams, but they eventually went extinct. I hope that the same
will not happen to DBI which will become huge and useless, color
Perl module for Apache is complex, poorly
documented, few years late and still in its infancy. CGI
applications are on their way out, both for security and performance
reasons. That also severely limits the usefulness of Perl. The best
proof of my statements is the advance of PHP and increasing number
of PHP pages around. PHP is very similar to Perl, easy to learn and
much better suited for web applications.
So, what is Perl good for? Why does it still
make sense to learn it?
Perl is still the best and the most practical
scripting language for writing OS level system scripts, with many
precious capabilities, like in-situ editing.
Wealth of modules on CPAN has solutions for almost any
problem. You can parse PDF documents, write Excel spreadsheets,
cheerfully write XML files and code/decode emails. You can also do
date arithmetics, infinite precision math, create lovely graphics
and sequence genes. Fortunately, nobody has come up with an idea to
look for WMD on CPAN yet, but I wouldn't bet that they're not there.
CPAN, of course, is an abbreviation for "Comprehensive Perl Archive
Network". No other language comes even close to the wealth of the 3rd
party application modules which are available on CPAN. CPAN page was
mentioned on the beginning of this article, but it is so big that
one mention isn't enough. CPAN can be explored at http://www.cpan.org
Perl has a built in report-writing capabilities, which
is a rarity after the demise of programming languages like COBOL and
RPG. In some of the example scripts above, you will find things like
"format" and "write" commands.". Perl can automatically account for
the number of lines per page, formatting, page headers, form feeds
and things like that. In their infinite wisdom, the creators of
Perl decided to remove that part from the next version of Perl
(Perl6) and make it available as a module. In return, the "switch"
command, supported by literally all other programming languages,
will find its place in Perl6. To summarize, Perl6 will not have the
things that made Perl5 stand out among all other programming
languages but will have that what everybody else has, too. Good
trade, if there ever was one! I believe that some people from Enron
and Andersen Consulting have found new jobs on Perl design
committee. They should remember that Perl stands for Practical
Extraction and REPORTING Language.
Perl is still unsurpassed in the ease and elegance
with which it handles regular expressions. Not even the best efforts
of the geniuses from the Perl6 design committee were able to take it
away. At least not yet.
If you do learn Perl now, you'll be able to tell your
grandchildren about the experience. Don't leave your home without
it. It's free!
Finally, what is the answer to the question of
life, universe and everything? 42, of course.
You should have known that!
||For a complete treatment of PHP and Oracle,
see Mladen Gogala's great new book "Easy
This unique book focuses on removing the guesswork from
creating dynamic web pages with Oracle content, and it's