Tuesday, March 07, 2006

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.

Monday, January 16, 2006

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;

Thursday, January 12, 2006

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.

Tuesday, January 03, 2006

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 ("вьев").

Sunday, January 01, 2006

... 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.

Saturday, September 25, 2004

A Tiger Ate Scott While He Was Listening to TNS

The studies are going on erratically but there are results.

After trying to recall what was it that the DBA's at my school at workplace did when they showed me how to connect to the database with the notorious SCOTT account and SELECT * FROM T_EMP;, i finally resorted to "SQL*Plus User's Guide and Reference". The SQL*Plus Quick Start chapter said that i have to unlock the HR user. I have to clarify this confusion between user and schema ASAP! So i unlocked it using the EM, but still couldn't run DESCRIBE HR.COUNTRIES.

I tried it with the SYSTEM account and it worked. I guess that something's wrong with AMIRA's privileges or roles or something in that vein. So i compared AMIRA to SCOTT account, which was also locked. SCOTT had CONNECT and RESOURCE roles, while AMIRA had only CONNECT. AMIRA also had no privileges, while SCOTT had UNLIMITED TABLESPACE. So i granted AMIRA everything SCOTT had, but it still didn't help. Then i looked at SYSTEM privileges, and there were, of course many more, including SELECT ANY TABLE. Maybe that's what it need. Also, SYSTEM had UNLIMITED TABLESPACE checked as "Admin Option", while SCOTT and AMIRA had not. Interesting! I'm still not changing anything; i'd better read chapter 7 of 2 Day DBA -- "Administering Users and Security".

It says that Oracle used SCOTT for training formerly, but now is phasing it out in favor of sample schemas HR, OE etc. I like the concept -- every schema is not only an example of a common relational table structure, but also an example of management concepts (warehouse, multimedia, object-oriented, etc.) What it don't like is the names: While HR is a common English abbreviation for Human Resources, i'm unfamiliar with OE as Order Entry. But maybe i don't know enough about English business writing.

And i go on.