I'm writing this while enjoying a delicious Kit Kat ChunKy.
I successfully installed DBI and DBD::Oracle on both Linux and Windows.
Install notes:
- Basic installation is quite straightforward:
perl Makefile.pl
make
make test
make install
- For
make install
you should probably be root or something like that. - For
make test
on DBD::Oracle the environment variable ORACLE_USERID must be set. On Linux "oracle_username/oracle_password" will work (ORACLE_SID must also be set to database name as it appears in TNSNAMES.ORA). On Windows ORACLE_SID had no effect and it worked only with "oracle_username/oracle_password@oracle_sid". - On Windows the make command is nmake. I have a complete installation of Microsoft Visual Studio here, so i already have this tool, as well as a C compiler. If they are not present, i think that they can be downloaded for free from MSDN. Moreover, when i tried to run nmake in a regular cmd window, i received an error about mspdb71.dll. Baffled, i went to the Start menu to look for some Visual Studio auto update feature or maybe help and found something nice called "Visual Studio .NET 2003 Command Prompt". It opens a window which looks and works just like the usual cmd, but with the environment somehow set up for manual compilation. I was lucky, i guess.
Scripting: The main DBI perldoc is mostly fine. It works like this:
#!/usr/bin/perl -w use strict; use warnings; use DBI; # No need to use DBD::Oracle or something like that... # Connecting to the database # Take the SID from TNSNAMES.ORA my $dbh = DBI->connect("dbi:Oracle:SID", "username", "password"); # A simple date fetch # Prepare the SQL statement my $sth = $dbh->prepare("select sysdate from dual"); # NOTICE - no ; after dual! # Execute it, but don't print it yet $sth->execute; # This "loop" prints all the rows (just one, in this case) while (my @row = $sth->fetchrow_array) { print "@row\n"; } # A real SELECT $sth = $dbh->prepare("select * from my_favourite_table"); $sth->execute; # This is a real loop, that prints all the rows. # It's very rudimentary, i'm sure that DBI has better tools # for pretty printing. while (my @next_row_fields = $sth->fetchrow_array) { foreach my $next_field (@next_row_fields) { if (defined $next_field) { print "$next_field"; } else { print "NULL"; } print "\t"; } print "\n"; } exit; __END__
I first ran the SQL statement with a semicolon: prepare("select sysdate from dual;")
and received a rather weird error:
"DBD::Oracle::db prepare failed: ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 22 in 'select * from subscrib<*>;') [for Statement "select * from subscrib;"] at dp_.pl line 28."
At first i thought that DBI appends this <*> to my statement for some reason, but then i understood that it's a marker, to tell me where the error is. I tried running without the semicolon and it worked.