Thursday, December 15, 2005

Oracle and Perl

I work with several different databases for several different clients on several different operating systems, often with only remote access to a shell (as opposed to a desktop). Therefore I like to become familiar with very common and simple technologies, like vi, sqlplus (for Oracle) and Perl.

Writing my useful tools in Perl is very handy because I can run them anywhere, and they can work for all databases just by having a switch on the connection string. But of course today's example will focus on Oracle. Perl is also very good at handling and manipulating data: perfect for database utility scripts.

There are many different ways to write code in Perl. Some refer to Perl as a write-only language because it is a lot easier to write than it is to read. But I'll keep my sample simple.

#!/usr/local/perl

use DBI;

# Get a database handle by connecting to the database
$dbh = DBI->connect("dbi:Oracle:host=servername;sid=dbname",
'scott','tiger', {RaiseError => 1, AutoCommit => 1})
or die "Can't connect to database $DBI::errstr\n";

# Put together your query string
my $sql = 'SELECT * FROM emp';

# Instead you could do $dbh->do($sql) or execute
$sth = $dbh->prepare($sql);
while (@rows = $sth->fetchrow_array()) {

# You can access a specific field like this: $rows[0];
print "@rows\t";
}
print "\n";
$sth->finish();

# If you did an update, you could $dbh->commit()
# or $dbh->rollback() before disconnecting
$dbh->disconnect();


For more information, here is a quick and dirty FAQ:
http://www.orafaq.com/faqperl.htm

Here are some really good on-line samples
http://www.cri.univ-rennes1.fr/documentations/DBDOracle.html

There is a book on Oracle and Perl that described a bunch of Oracle DBA utilities written in Perl. I have not read it myself, so check it out before purchasing (and let me know what you thought).
"Perl for Oracle DBAs" by Andy Duncan and Jared Still
http://www.amazon.com/gp/product/0596002106/002-3253639-8672853?v=glance&n=283155

Comments:
I’m trying to connect to a remote database using Perl through Oracle Client. I can connect from the SQL> prompt. I’ve installed DBD::Oracle but get an error when I tried to connect with:

sub Connect {
my $dbh = DBI->connect(
"dbi:Oracle:host=x.x.x.x;sid=xxxx;port=152x",
"user",
"password",
{ora_verbose=>6})
or die "Can't connect to database $DBI::errstr\n";
return $dbh;
}

I get a general processing error unless I use CGI::Carp (ora_verbose and die don’t show any errors):

install_driver(Oracle) failed: Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230

Oracle.so is at the specified location and libclntsh.so.10.1 is located at /var/lib/oracle/xe/app/oracle/product/10.2.0/client/lib/

Any ideas as to what is wrong?
 
Found the answer. I was trying to set the environmental variable LD_LIBRARY_PATH in Apache's httpd.conf file but with no success. I finally tried setting it in an .htaccess file and that did the trick.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?