Pythian Group

I think it was Gabor who was mentioning that we should promote a little bit more the distributions we upload on CPAN. He’s right. A falling tree might or might not make a sound if no-one is present, but it sure doesn’t hurt to yell “Timber!”.

…

Hum. Okay, that was a remarkably bad metaphor, but you hopefully know what I mean. Anyway, all that to say welcome to New & Improved blog entries, where I quickly showcase any new distribution o’ mine that have new stuff worth mentioning.

To start the show, two distributions for the price of one: Dancer::Plugin::Cache::CHI and DBD::Oracle.

Read the rest of this entry . . .

I already aluded to the fact in a few posts already, but just to make it a wee bit more public: a few weeks ago, someone had a lapse of common sense and knighted me Maintainer of DBD::Oracle.

mouahahaha

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

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

1, 2, 3, 4, 5, 6, 9, or 10. It ain’t no Feist song, but she got it right. Notice that she mentions no 7 or 8 in it. Well, that is also true for version support in release 1.21 of DBD::Oracle.

With some of the new functionality that was introduced in DBD::Oracle 1.21, you can no longer use the Oracle 7 and most early 8 clients to build DBD::Oracle.

I hope this little table will help you choose which version of DBD::Oracle is right for you.

As there are dozens and dozens of different versions of Oracle’s clients, I did not bother to list any of them, just the major release versions of Oracle that are out there.

Note that one can still connect to any Oracle version with the older DBD::Oracle versions. The only problem you will have is that some of the newer OCI and Oracle features available in later DBD::Oracle releases will not be available to you.

So to make a short story a little longer:

Read the rest of this entry . . .

Karun Dutt and I managed to get DBD::Oracle 1.21 to install on a 64-bit Linux OS against the Oracle 11 full client. Here’s what we did.

As root, we downloaded DBD::Oracle from CPAN.

# perl -MCPAN -eshell
cpan> get DBD::Oracle
...

We replaced the distribution makefile with: http://svn.perl.org/modules/dbd-oracle/trunk/Makefile.PL (this is the latest Makefile.PL).

# cd /root/.cpan/build/DBD-Oracle-1.21
# export ORACLE_HOME=<actual value of Oracle Home>
# export ORACLE_SID=<actual value of ORACLE_SID>
# export ORACLE_USERID=<a working ORACLE_USERID>
# export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
# perl Makefile.PL
...
# make
...
# make test
...
# make install
...

It works!

I have just managed to get DBD::Oracle to successfully compile, install, and test with the 11.1.0.6.0 Instant Client (IC) on a Linux (32bit) OS. It seems Oracle, in its wisdom, has changed the folder structure yet again, so to get it to compile, try this.

The table structure of the IC out of the RPMs is:

/usr/lib/oracle/11.1.0.1/client/ (Instant Client Package Basic)
/usr/share/oracle/11.1.0.1/client/ (SQL*Plus)
/usr/include/oracle/11.1.0.1/client/ (Instant Client – SDK)

So, doing the following . . .

export ORACLE_HOME=/usr/lib/oracle/11.1.0.1/client
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$PATH

perl Makefile.PL -m /usr/share/oracle/11.1.0.1/client/demo.mk -h /usr/include/oracle/11.1.0.1/client/

. . . will work.

I have also updated the Makefile.PL, which can be found here: http://svn.perl.org/modules/dbd-oracle/trunk/Makefile.PL.

Cheers, John Scoles.

The latest release of DBD::Oracle is now ready and can be found at: CPAN DBD::Oracle. It is a Perl module that works with the DBI module to provide access to Oracle databases. It is maintained by me, John Scoles as open source/free software, under the auspices of The Pythian Group.

The release has been fully tested with the latest version of DBI (1.601).

Below is the list of the changes and/or fixes in this release. Read the rest of this entry . . .