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.

This release is largely a maintenance release that fixes a number of bugs (new and old) and cleans up the POD. I have also spent a good deal of time cleaning up the tests, compile warnings, and the Makefile.PL. A thank-you goes out to Martin Evans and H. Merijn Brand for their help on that.

As for new items, the ORA_VERBOSE attribute was added so that you can debug/trace DBD::Oracle without seeing any DBI debug/trace output. OCILobGetLength was added to the LOB functions, thanks to Milo van der Leij, I have also dropped support for Oracle 8 and earlier clients; see the POD for details on this.

Finally, I have also cleaned up and expanded how persistent LOBs work. A thank-you to Eric Simon for his work on this.

The release has been fully tested with version (1.601) of DBI. Below is the list of the changes and/or fixes in this release.

(more…)

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:

(more…)

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.

Building High-Performance Drivers for Oracle Database 11g: OCI Tips and Tricks (PDF). I had the opportunity to have an early look at this white paper by Luxi Chidamdaram.

The paper goes over what needs to be done to use OCI effectively, especially in a web environment. The step-by-step approach taken in this document is very readable and the well explained code snippets make it a very good reference — great for comparing your code to the “proper” way to do it.

The novice OCI programmer will find the initial sections of great benefit, as it takes a lot of mystery out of OCI programming. For the more advanced OCI-head, the document is full of good examples of how to manage connections, sessions, and pooling–with an examination of what type of pooling is needed in a given situation.

One highlight for me was the section on database events, which are covered very effectively with some practical examples.

In short, this white paper is a must-read for both the OCI master and the novice, with some great pointers on how to use what is already in OCI and what is new in 11.

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.

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. (more…)

The latest release of DBD::Oracle is now ready and can be found at:

CPAN DBD::Oracle

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

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

• Fixed execute_array to comply with DBI standard from Martin J. Evans, Xho Jingleheimerschmidt and others
• Fixed execute_array so it will not throw a Perl warning on undef values in Tuples from John Scoles
• Fixed execute_array so it will take the ora_array_chunk_size DB handle attribute
• Fixed some typos in code and READMEs from John Scoles
• Fixed a few other little bugs dealing with compatibility with Oracle 8 Changes to README from Karl Auer
• Suppress warning in 26exe_array.t from Philip Garrett
• Added support for array context aware execute_for_fetch from Martin J. Evans
• Fixed Makefile.PL for an incompatibility with ExtUtils::MM_Unix v1.50 (invoked byExtUtils::MakeMaker) from Dennis McRitchie
• Updated POD to reflect that OCI after 9.2 no longer strips trailing spaces

Please enjoy,

John Scoles.

Hello!

I am happy to present the very first post to my new group blog, where I will be inviting Pythian DBAs with interesting thoughts to contribute to prepare blogs of their own. You can read the group blog either one author at a time, by clicking the author’s name in the right-hand side of this column, or if you prefer, you can see all the posts on one page by going to the main group blog page.

My subject for this first posting: DBD::Oracle. If you have used perl to interact with a database, you are aware of the DBI that Tim Bunce and his collaborators first developed in the 1990s in order to interact with a database abstraction layer. DBD::Oracle is one of the database layer drivers that allows the DBI to communicate with Oracle. Pythian is a big user of perl and DBD::Oracle internally, as our problem tracking groupware, Support Track, and our availability monitoring software, avail, are all written using the DBI to communicate with the underlying database.

Late last year, Tim posted to the DBI users’ mailing list looking for a volunteer that would become the maintainer for DBD::Oracle. He explained that as he was no longer a user of Oracle, the task of keeping the software current was becoming increasingly difficult and distracting for him. Pythian volunteered and I am proud to announce that we made our first release, DBD::Oracle 1.17 last month. This release adds support for Oracle XE, Oracle’s free low-end database, and for installation using the Oracle Instant Client, as well as troubleshooting a variety of installation gotchas and bugs in the makefile.

Seah Hull at the Oracle Open Source blog interviewed me on the subject of Pythian taking on the stewardship of DBD::Oracle and even has a podcast of the interview available on his site.

We cover the following material:

1. Why don’t you tell us a bit about yourself, and your company?
2. When a perl programmer wants to interface with a database, what
technology do they use?
3. How did that DBI project get started, and by whom?
4. How does the DBD::Oracle component come into play? What language is it written in, and how does it interface with Oracle’s own libraries?
5. What has Pythian’s role been with this project in the past, and how has that changed recently?
6. From a business perspective, why would a company like Pythian want to contribute to this project for free? What is the advantage?
7. How have Open Source technologies benefited Pythian?
8. Do you have any other comments on Open Source, benefits, industry
threats, myths, and so on? Is everything really just free? And
furthermore won’t everyone eventually go the way of a free database?

Check it out!

Cheers,

Paul