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 


 

 

 


 

 

 

 

 

Connecting to Oracle with the Perl DBI

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.

So, let's start with the article. In order to execute the examples from the article the reader will have to have access to an Oracle database and also  access to Perl interpreter with DBI and DBD::Oracle installed. Finding Perl isn't a problem. Verifying that all necessary modules are installed is also very easy. So, how do we tell that DBI and DBD::Oracle are installed and how do we tell which versions are installed? The answer is  really simple:

$ perl -e 'use DBI; print $DBI::VERSION,"\n";'

1.50

$ perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'

1.17

Therefore, we have DBI 1.50 and and DBD::Oracle 1.17. At the moment, these are the latest and the greatest versions.  Here is the Oracle version:

$ sqlplus scott/tiger
 
SQL*Plus: Release 10.2.0.2.0 - Production on Sun Apr 16 23:09:35 2006
 
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
 

This username and password (SCOTT/TIGER) will be used throughout this article, as well as the accompanying EMP and DEPT tables which will be used for almost all of the examples. EMP and DEPT tables do not have LOB columns, so I will have to create my own infrastructure for dealing with LOB data types.


Connecting to Oracle with the Perl DBI

DBI uses the following syntax to connect to an Oracle instance:

my $db=DBI->connect("dbi:Oracle:local", "scott", "tiger");

The string on the beginning contains the following elements, separated by colons: the string "dbi", driver type, in this case "Oracle" and the database name, also known as TNS descriptor. In the line above, the database name was "local". The first argument, therefore, defines the database to connect to. The 2nd and 3rd arguments are, of course, username and password. Of course, everyone who has ever worked with databases knows that connection errors happen from time to time, so it is prudent to check for errors:

 my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd ) ||

           die( $DBI::errstr . "\n" );

In case of connection error, database handle $dbh is not created and the error string "errstr" comes from the DBI class itself. If, on the other hand, the connection attempt is successful and the database handle is created, there are several properties that can be very helpful with further programming:

  • AutoCommit: when set to 1, DBI issues a commit after each successful SQL statement. This is very dangerous and on by default. Setting it to 0 is a good idea. This handle property is not Oracle specific, it is available for any database.
     
  • RaiseError: When turned on (it is off by default)  it sends an exception to your script and terminates it. It is a good idea to kill the script if a non-handled Oracle exception happens. This handle property is not Oracle specific, it is available for any database.
     
  • ora_check_sql: Oracle has a performance enhancing trick called "deferred parse". When used, it decreases the number of the needed database calls, by bundling "parse" and "execute" phases of the SQL execution. Unfortunately, this feature is not turned on by default. To turn it on, you have to set ora_check_sql to 0. This is Oracle specific.
     
  • RowCacheSize: this instructs the driver to create a local pre-fetch cache and defines its size.
     
  • Both DBI database and statement handles always have errstr and err member variables which contain error message and code respectively. In case of a failed connection attempt, when database handle is not created, errstr and err can be used in class context, as $DBI::errstr and $DBI::err. That is used in the examples throughout the article.
     
  • This list is by no means complete, I mentioned only the properties most frequently used in the scripts. For the complete list, please consult the online DBI documentation. In further text, I'll introduce two more handle properties, used to deal with LOB data types.

These properties are keys of an associative array, and here is how they're set:

    $dbh->{AutoCommit}    = 0;

    $dbh->{RaiseError}    = 1;

    $dbh->{ora_check_sql} = 0;

    $dbh->{RowCacheSize}  = 16;

So, this turns off auto-commit,  instructs DBI to terminate the script if SQL error is incurred, instructs Oracle driver to use deferred parse and wait with parsing the SQL until an execution is attempted for the first time. It also creates a local pre-fetch cache with place for 16 rows.

Unfortunately, DBD::Oracle does not support array interface, so the effects of this local cache are not as great as one would expect. As a matter of fact,  this lack of support for Oracle array interface is the feature that severely limits the use of Perl with Oracle and makes it unsuitable for large data loads or massive transactions. Features that are also not supported are TAF (Transparent Application Failover) and direct loads. In other words, Perl with DBI and DBD::Oracle is not an industry strength tool for a production environment.


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