Pythian Group

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

The “Sesame Street” Version of DBD::Oracle (1.23) has been released.

You can find it at CPAN DBD::Oracle.

DBD::Oracle is the 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 The Pythian Group as open source/free software.

This release is largely a maintenance release that fixes a number of bugs.

New stuff includes the ability to fetch Oracle embedded types directly into an oracle object; a big thank you goes out to Tomas Pokorny for that patch.

Also, UTF8 support has been expanded and cleaned up for BLOBs and execute_array and thanks go out to Milo van der Leij, David Mansfield for most of the work on this.

Also a big thanks Alex Buttery, Jim McCullars, Charles Jardine, Eric Simon, and Chris Underhill, who helped out with some clean up of the code, READMEs, and the POD.

I have also now added two private statement functions ora_stmt_type_name and ora_stmt_type which will get the OCI type name and type for the currently prepared statement.

The complete change list

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

There seems to be a bug in DBD::Oracle’s execute_array when working with 11g.

If you tell DBD::Oracle to autocommit, it seems that in 11g this commit will not take place when an error occurs during the processing of one of the tuples that you passed into execute_array. So given this table:

CREATE TABLE test_array (
	    row_1 INTEGER NOT NULL,
	    row_2 INTEGER NOT NULL,
	    row_3 INTEGER NOT NULL)

the following code will result in no records being added to the db.

# create a database handle
my $dbh = DBI->connect('dbi:Oracle:','xxx@xxx','xx',{
	RaiseError			=> 1,
	PrintError			=> 0,
	ShowErrorStatement		=> 1,
	AutoCommit			=> 1

}) || die "Unable to establish connection with Oracle server: $DBI::errstr";

my $rv;
my @var1         = (1,1,1,1,1,1,1,1,1,1);
my @var2         = (2,2,2,2,'s',2,2,2,2,2);
my @var3         = (3,3,3,3,3,3,3,3,3,3);

my $tuple_status = [];
my $dumped ;
my $rows = [];

$sth->execute_array(
        {ArrayTupleStatus => $tuple_status},
          \@var1,
          \@var2,
          \@var2,
   );

The workaround is to set AutoCommit => 0 and then use $dbh-->commit() after the statement.

So far, the jury is out whether this is a bug only in 11g, but as this code works correctly in 9 and 10 (it adds the 9 rows), it seems likely. I have noted the bug here: http://forums.oracle.com/forums/thread.jspa?messageID=2663832#2663832.

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, under the auspices of The Pythian Group as open source/free software.

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.

Changes in DBD-Oracle 1.21(svn rev 11067) 11th April 2008

• Added Notes to README.win32.txt on installing Instant Client 1.1.0.6.0; from John Scoles
• Added the oci_typecode_name method to get the name rather than just the number of an OCI_TYPECODE; from John Scoles
• Fixed a unreported bug with Embedded Objects; from John Scoles
• Fixes for #34621 & 33791 from RT cpan
• Added patch to allow faster fetch from REF CURSORs; from Biswadeep Chowdhury
• Updated the Todo file for next version; from John Scoles
• Added support for the 10.2 Data Interface for Persistent LOBs; by John Scoles
• Changed the way pre-fetching is done; by John Scoles
• Added support for Scrollable cursors; from John Scoles
• Changed the max size of cache_rows to a sb4 rather than a int and or a ub4; from John Scoles
• Added support for Lobs in select of OCI Embedded Objects; from John Scoles with a big thankyou to Paul Weiss
• Fixed for embedded object in object; from Paul Weiss
• Added support for direct insert of large XML data into XMLType fields; from Hendrik Fuss & John Scoles
• Fixed memory leak (not releasing Temp Lob with OCILobFreeTemporary) when created for a bind; from John Scoles
• Added support for bind_param_inout_array for use with execute_array; from John Scoles
• Added enhancement for Embedded Objects handling; from Paul G. Weiss
• Fixed to Makefile.PL let it read makefiles from other makes from; Alexander V Alekseev
• Updated POD to tell users to Avoid Using “SQL Call”; from Charles Jardine
• Updated POD to account for rt.cpan.org #30910: “DBD-Oracle crashes when trying to read empty LOB”; from John Scoles
• Added DBD::Oracle impdata/threads patch; from Jeffrey Klein

In this release there are a number of new features. One important one you might want to have a look at is support for binding an array for in-out operations with execute_array. Given this SQL:

INSERT INTO foo (id, bar) VALUES (foo_id_seq.nextval, ?) RETURNING id INTO ?

and using execute_array to insert 200 records, you will get an array back of the 200 new IDs inserted.

I have also added support for directly inserting of large XML files into an XMLType field.

Another important addition is support for Persistent LOBs if you are using Oracle 10.2 or later. You can now treat LOBs as if they were any other form of data, so say goodbye to LOB locators.

Finally, I have added support for native Oracle ‘Scrolling Cursors’.

There were also a number of bug fixes and code updates as listed above.

Cheers, John Scoles.