Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Array, TAF & load/unload features of DBI/DBD::Oracle

Oracle Tips by Mladen Gogala

Mladen Gogala is the author of "Easy Oracle PHP".

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:

l       Array interface

l       Transparent application failover (TAF).

l       Direct load/unload


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 @values;

my $INS="INSERT INTO TAB VALUES (:PLACEHOLDER)";

$sth=$db->prepare($INS);

$sth->bind_param_array(":PLACEHOLDER",\@array);

$sth->execute_array( { 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 coded monstrosity.

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?

 

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

l       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

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

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

l       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 Oracle PHP". 

This unique book focuses on removing the guesswork from creating dynamic web pages with Oracle content, and it's only $19.95.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational