Pythian Group

Holy Zarquon just released the initial results of the 2010 Perl Survey.

The slides are also available.

Now for my 2cents worth.
Read the rest of this entry . . .

If you have ever had this message:

Unable to locate an oracle.mk, proc.mk or other suitable *.mk
file in your Oracle installation. (I looked in…)

It can be a very frustrating one to track down.
Read the rest of this entry . . .

I have successfully compiled and installed DBD::Oracle on Windows 2008 Server 64bit operating system today.

I used the latest version of DBD::Oracle 1.24, version 11.2.0.1.0 for 64bit Windows of Oracle’s
Instant Client Package – Basic along with the Instant Client Package – SQL*Plus and finally the Instant Client Package – SDK.

To get it to make and compile correctly I had to download Microsoft’s Visual Studio Ultimate

which should contain all the files you need. It is rather portly at 2+gb so you might want to grab lunch while you are downloading it.

After all the above downloading DBD::Oracle installed right out of the box.

All one has to do is select ‘Start Menu->All Programs->Microsoft Visual Studio 2010->Visual Studio Tools->Visual Studio x64 Win64 Command Prompt (2010)’
which will open a good old ‘dos’ window.

At this point CD to the directory where you downloaded DBD::Oracle

     c:\DBD-Oracle

then set your ‘ORACLE_HOME to the Instant Client directory

     c:\DBD-Oracle set ORACLE_HOME=c:\IC_11

you should also set your NLS like this

     c:\DBD-Oracle set NLS_LANG=.WE8ISO8859P15

Once the above setting are done do a

     c:\DBD-Oracle perl Makefile.PL

and then a

     c:\DBD-Oracle nmake install

Which will produce a whole of warnings (these you can ignore, as they do not seem to effect DBD::Oracle at all) and near the end it should output something like this;

     Generating code
     Finished generating code
     if exist blib\arch\auto\DBD\Oracle\Oracle.dll.manifest mt -nologo -manifest blib\arch\auto\DBD\Oracle\Oracle.dll.manifest -outputresource:blib\arch\auto
\DBD\Oracle\Oracle.dll;2
     if exist blib\arch\auto\DBD\Oracle\Oracle.dll.manifest del blib\arch\auto\DBD\Oracle\Oracle.dll.manifest
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "chmod" -- 755 blib\arch\auto\DBD\Oracle\Oracle.dll
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "cp" -- Oracle.bs blib\arch\auto\DBD\Oracle\Oracle.bs
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "chmod" -- 644 blib\arch\auto\DBD\Oracle\Oracle.bs
     C:\Perl64\bin\perl.exe "-Iblib\arch" "-Iblib\lib" ora_explain.PL ora_explain
Extracted ora_explain from ora_explain.PL with variable substitutions.
     C:\Perl64\bin\perl.exe -MExtUtils::Command -e "cp" -- ora_explain blib\script\ora_explain
        pl2bat.bat blib\script\ora_explain

At this point you are all done.

Well almost.

It is important that you test your code before you install but you will have to set a few things up first to get it to fully test correctly.

You will need a TNSNAMES.ORA file that points to a valid DB in the Instant Client Directory

Next you will need to set the ORACLE_USER_ID to a valid user

     c:\DBD-Oracle set ORACLE_USER_ID=system/system@XE

You will have to set up TNS_ADMIN to point to the Instant Client Directory

     c:\DBD-Oracle set TNS_ADMIN=c:\IC_11

Most importantly you will have to add the Instant Client directory to your path like this

     c:\DBD-Oracle path = c:\IC_11;%path%

If you do not do this step you will run into the dreaded

Can’t load ‘C:/Perl/lib/auto/DBD/Oracle/Oracle.dll’ for module DBD::Oracle: load_file:%1 is not a valid Win32 application at C:/Perl/lib/DynaLoader.pm line 202.

Error later on after the compile when you try to use DBD::Oracle.

What is actually going on is that Perl cannot find oci.dll (or one of the other .dlls it needs to run) the

C:/Perl/lib/auto/DBD/Oracle/Oracle.dll’ and the DynaLoader error

is just a false trail as perl is very limited in what it Windows errors it can report on. For more complet info on this sort of error check out this page;

Oracle Troubleshooter HOWTO

by Alexander Foken. It is rather dated but the facts of why perl did not find a dll are still valid.

now you can do this

     c:\DBD-Oracle nmake test

and all the tests should run and it will report.

Finally simply do a

     c:\DBD-Oracle nmake install

and you are all set.

That is about it.

At this point you might want to add the Instant Client directory permanently to your path so you will not run into the Dynaloader error again.

As well you do not need to keep Visual Studio around to use DBD::Oracle so you can uninstall that as well.

The “Beer” version of DBD::Oracle (1.24) has been released. You can find it at CPAN DBD::Oracle.

DBD::Oracle is a Perl module that works with the DBI module to provide access to Oracle databases. It is maintained by me, John Scoles, under the auspices of Pythian as Open Source/Free Software.

This is largely a maintenance release that fixes a number of bugs. New stuff includes some more improvements to embedded types from Charles Jardine.

Timestamps in Embedded Object now use precision to 6 places.

The big enhancement this round is extending the code to allow more than one row to be returned with a single fetch. Those of you who use Rowcache size will now find that your queries will run much faster.

I have also added the ora_ncs_buff_mtpl parameter, so you can now have very fine grain control over the size of the data buffer using for NCHAR conversions of lobs.

One more little note — thanks to my mistake in compressing a file last week, the version number on CPAN is 1.24a, while the internal Version is still 1.24.

Here is the complete change list: Read the rest of this entry . . .

My old arch-nemesis, the in-line if ($q = $q == $a ? $b : $c;) reared its ugly little head again.

This time, it was in context of an web page that displayed some form values, something like this:

CGI::textfield({name  => 'price_dollars',
                        width => '5',
                        value => ($mode eq 'Edit' ? $line_item[$count]->price_dollars() : '0') });
CGI::textfield({name  => 'price_cents',
                         value => ($mode eq 'Edit' ? $line_item[$count]->price_cents(): '00' });

This carried on for 15 other fields on the form. So, we have 17 if else statements all checking to see if the form is in ‘Edit’ mode. If there were, say, ten line items on the form . . .  well no need to go any further, other than to say that is a whole lot of if statements.

While this does not take up much space, this multiplicity of ifs is not really necessary or even good, since to the compiler, an inline if and a bracketed one are the same. The inline is only a shorthand to make our code more readable.

We could of course just declare 17 scalars at each iteration, and then a use single if statment to set these scalars, like this . . . 

Read the rest of this entry . . .

One thing I find fascinating in Perl is that I am always seeing new ways to perform the same mundane task.

Today I had to output some tabular data, so I thought it would be nice if I alternated colours for each row. Easy enough in Perl—just create a hash with your colours as the value and then the swapping variable as the key, like this:

my %colours=(1=>'red', 0=>'green');
my $swap=1;

foreach $item (@stuff) {
    my $colour=$colours{$swap};
...

Then I though about how to flip the $swap value. I could simply use the tried and true—and like me—Luddite style: Read the rest of this entry . . .

I finally had the opportunity the other day to try and install DBD::Oracle on an IBM AIX 5.1 box, and for once I have some good news to tell.

Anyone who has ever tried this will know of some of the troubles I speak of. When dealing with DBI and any DBD on a AIX box, you either must either be lucky enough to have the same compiler installed that built the version of Perl that comes with the box (I have never seen this happen); or you have to spend a great deal of time downloading and installing your own GCC and the building your own version of Perl.

Fortunately, all the hard work was done for me by other members of my team, and I was only a Johnny-come-lately to the whole process.

In our case, these are the steps that we followed:

1. get a working version of GCC on the box
2. rebuild and install your own version of Perl
3. build and install DBI using your local version of Perl

All of the above worked without a major problem. It was only when we tried to build DBD::Oracle that we ran into a problem:

Read the rest of this entry . . .

This is a little story of a little bug. This gremlin suddenly appeared in a CGI.PM web-based application I work with. To make a long story short, an email was coming out something like this . . . 

389939
Subject:Update to Report #389939 by B. bloggins Description:389939 #389939: TPDD Now Deploying to monitoring for the MySQL servers.

 . . . when it should have been some thing like this:

Subject: TPDD Update to Report #389939 by B. bloggins

TPDD Now Deploying to monitoring for the MySQL servers.

After about an hour tracking things back, my team and I narrowed it down to this line of code:
Read the rest of this entry . . .

No, I do not have squiggly worms in my head, and no, I haven’t gone over to the dark-side. It’s just that I had an opportunity over the past few days to attempt to use Microsoft’s Server Management Objects (SMO) with Perl to manage a SQL Server 2005 DB.

To make a long story into a short post, I blundered into the Win32::CLR module on CPAN, a little gem from Toshiyuki Yamato.

Here is all you need to get started. Read the rest of this entry . . .

Thanks to a patch from Tomas Pokorny, you can now select Oracle user-defined types directly into a Perl object.

User-defined types include Object, Varray, and Table. These have been around in Oracle since 8i but have never really gained any sort of popularity for any number or reasons. The most common reason I have heard from DBAs and modelers is that they break relational integrity. As the objects in Oracle are “Embedded,” I do not see how that is possible. The more practical reason is that the SQL to retrieve and query this sort of object tends to be rather involved, and is sometimes slower than a simple table join; also, this sort of object can be hidden or unavailable to many kinds of modeling software.

With the new functionality of DBD::Oracle, selects of these objects are simple to work with in Perl. Lets take the example below for a web site that has a number of different “user” types. Read the rest of this entry . . .