Perl, DBI, DBD - part 2

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.

Perl, DBI, DBD - part 1

I'm trying to install an Oracle module for Perl.

What have i learned till now:

• The thing called "Oraperl" about which i kept hearing till now is mostly for Perl 4.
• As far as i understand now, the modern way to access relational darabases from Perl is DBI. It can be described, very roughly, as the Perl equivalent of ODBC - the DBI itself has generic functions, and below it there are Perlish drivers (DBD's) for different kind of database management systems (DBMS's) - Oracle, MySQL, MS-SQL, DB2 etc. There's even a driver for ODBC and then the Perlish DBMS-specific driver is less important, however it is not safe to count on the availability of ODBC on non-Windows platforms.
• I succeeded at building recent versions of DBI and DBD::Oracle on Linux. The README's tell that the minimum requirement to build those packages is Oracle Client and Pro*C. I built it on the same machine on which the actual database server is installed, so i didn't run into any dependency problems. Later i'm gonna try it on a Windows machine, which only has Oracle Client. I can see Pro*C in Oracle Universal Installer under Oracle Homes/OraClient10g_home1/Oracle Client/Oracle Programmer, so i think that it's installed.
• The DBI homepage, dbi.perl.org, points to version 1.16 of DBD::Oracle as the latest version. It was released in 2004. However, there's a brand new 1.17 version, released in February of 2006, but it's labeled under different creator in CPAN, a company called Pythian. The CHANGES file says that Pythian indeed took over the further development of DBD::Oracle from Tim Bunce, the creator of DBI. I'm gonna report it to dbi.perl.org webmasters. Too bad i found out about it only after i already built and installed 1.16. Oh well.

I have five Kit Kat ChunKy's in my drawer. I told myself that i'm not eating any of them until i have a working Perl program which runs on both Linux and Windows and can succesfully SELECT SYSDATE FROM DUAL.

More Simple and Important Things to Learn on The Way

Vanilla text output: SELECT 'Executing backup at ', SYSDATE FROM DUAL;

Executing a stored procedure: EXECUTE PACKAGE_NAME.PROCEDURE_NAME;

Disabling column headings: SET HEADING OFF;

Viewing a View

When you SELECT from a view, it shows the information relevant to the current schema, the one with which you connected.

I spent hours trying to figure out why a view was showing data which had nothing to do with what i expected to see, until i figured out (with a little help from my friends) that it shows quite different things if you're logged in with the SYSTEM schema. I should have viewed it in an application-specific schema.

Little Notes About Creating Views and Tables

I'm doing simple edits to a bunch of view and table creation scripts. Creating a view seems very simple:

CREATE OR REPLACE VIEW view_name
          (
          col1name,
          col2name,
          col3name
          )
AS
SELECT col1,
       col2,
       col3
FROM   table;

I looked into the documentation in Oracle's SQL Reference, hoping to find something to criticize, but found it to be easy to find and well written.

When i ran the script, i got one error, about creating a table. One of the scripts had a CREATE TABLE statement and that table already existed. I asked my team mate for his opinion on this and he said that all table-creating scripts should drop the table before creating. I boldly suggested it to our DBA, and he thought otherwise — he says that this will make the system much more accident-prone. And i should learn why doesn't CREATE TABLE have a OR REPLACE clause, like CREATE VIEW.

This reminded me of my Russian-speaking cousin Slavik, who taught me a lot about computers when i was a teenager. When he spoke about any kind of computer operation called "view" (remember Norton Commander?), he, not knowing much English, would ignore the w and pronounced it vyev ("вьев").

... Coming Back to Life

I neglected this blog a long time ago. Since then i found a new job - and the little i did learn about Oracle 10g certainly helped me get it.

Now i use Oracle here and learn new stuff every day. Suddenly i recalled that i have this blog - so why not record the experiences?

I'm reviving it.