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.


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

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

For more information, here is a quick and dirty FAQ:

Here are some really good on-line samples

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

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(
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/' for module DBD::Oracle: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/ line 230 is at the specified location and 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.
Through the assistance of mobile phone providers are free to do the text message to the lender. And the rest of the information is sent by the Bad Credit Loans Pounds Need for People with Instant Decision lender. People go through a financial arrangement from time to time. Obtaining a loan is not easy and this is especially true if all you need is a small amount to get through.
Post a Comment

<< Home

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