Perl for Oracle DBAs
by Andy Duncan, Jared Still 

This errata page lists errors outstanding in the most recent printing.

If you have technical questions or error reports, you can send them to
booktech@oreilly.com.  Please specify the printing date of your copy.

This page was updated October 21, 2004.

Here's a key to the markup:
	[page-number]: serious technical mistake
	{page-number}: minor technical mistake
	<page-number>: important language/formatting problem
	(page-number): language change or minor formatting problem
	?page-number?: reader question or request for clarification



Confirmed errors:

You can find a word doc that explains how to install several
Perl modules for Windows and Perl version 5.6.1 here -
http://examples.oreilly.com/oracleperl/Perl 5.6.1 and PDBA.doc

Several modules have become difficult to load with ActiveStates
latest build of Perl 5.6.1, and this document explains how to
work around the problems.

It also notes that you cannot use PDBA with Perl 5.8, as there
are not any available DBD-Oracle modules for that version
of Perl.


(xi)

The Chapter 4 bulletted paragraph, has:

"Chapter 4, , discusses..."

This should be:

"Chapter 4, Perl Web Extensions, discusses..."

(where "Perl Web Extensions" is in italics)


{xiv}

The third bottom web address says:

http://www.oreilly.com/catalog/perloracledba

This should be:

http:/www.oreilly.com/catalog/oracleperl


[47]  Getting the latest PPD files;
The site  - http://www.xmlproj.com/PPM   no longer exists:
The xmlproj.com domain was apparently hijacked by an unscrupulous ISP.

Windows binaries ( just version 8i I believe ) may be
found at ftp://ftp.esoftmatic.com/outgoing/DBI


{61}

Example 2-2 has a line:

---
$underline = '=' x ($msg_len + 6);
---

This should be

---
my $underline = '=' x ($msg_len + 6);
---

The full example, should be as below:

========== Code should be as below ==============

#!perl -w

use strict;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:orcl','scott','tiger',
                       {RaiseError =>1, AutoCommit => 0});

my $msg =
   $dbh->selectrow_array(
      "SELECT SYSDATE || ' Hello Cygwin DBI! :-)' message FROM DUAL"
                        );

# Let's have some formatting fun! :-)

my $msg_len = length( $msg );
my $underline = '=' x ($msg_len + 6);

print "\n", $underline, "\n",
"|| ", ' ' x $msg_len, " ||", "\n",
"|| ", $msg, " ||", "\n",
"|| ", ' ' x $msg_len, " ||", "\n",
$underline, "\n\n";

$dbh->disconnect;

========== Code should be as above ==============


[70]

The code in Example 3-1 is missing Step 5.

It says:

---
# Step 5: Launch the Perl/Tk looping process, to display window.MainLoop();
---

It should say:

---
# Step 5: Launch the Perl/Tk looping process, to display window.

MainLoop();
---

The full example, as it should be, is below:

========== Code should be as below ==============

#!/usr/bin/perl

use strict;
use warnings;

# Step 1: Get hold of the main Perl/Tk package.

use Tk;

# Step 2: Create the Main Window.  Use the name of the program,
# held in the special Perl variable $0, to create the title.

my $mw = MainWindow->new(-title=>$0);

# Step 3: Pack a label onto the screen to hold our initial message.

$mw->Label(-text=> "Hello Perl/Tk", -anchor=>'center'
          )->pack(-side=>'top');

# Step 4: Create a button to neatly exit the program.

$mw->Button( -text=>'Exit',
             -command=>\&doExit )->pack(-side=>'bottom');

# Step 5: Launch the Perl/Tk looping process, to display window.MainLoop();

MainLoop();

# Step 6: Create an exit subroutine.

sub doExit { exit 0; }

========== Code should be as above ==============


{71}

Typo in Example 3-2.  Code snippet below is missing a dollar symbol.  Around
half-way down the page, there is a line which currently says:

---
my oracleTime;
---

It should say:

---
my $oracleTime;
---

Full code should be as below:

========== Code should be as below ==============

#!/usr/bin/perl

# Step 1: Get hold of the main Perl/Tk package, DBI, and set the
# Oracle Environment, plus set the database connection and SQL.

use Tk;
use DBI;
use strict;

my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger',
                        {RaiseError=>1, AutoCommit=>0 } );
my $sql = qq{ SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL };
my $mw = MainWindow->new(-title=>$0);

# Step 2: Get the latest time from the Oracle database.

my $oracleTime;

getTheOracleTime();

# Step 3: Pack a simple button onto the screen, to ask Oracle for the
# current SYSDATE time.  Assign the appropriate callback.

$mw->Button(-text=>"What's the Time, according to Oracle?",
            -command=> \&getTheOracleTime )->pack(-side=>'top');

# Step 4: Pack a label onto the screen holding the SYSDATE time.

$mw->Label(-textvariable=> \$oracleTime, -anchor=>'center'
          )->pack(-side=>'bottom');

# Create another button to neatly exit the program.

$mw->Button( -text=>'Exit',
             -command=>\&doExit )->pack(-side=>'bottom');

# Launch the Perl/Tk looping process, to display the window! :-)

MainLoop();

# Step 5: Create the two required subroutines.

sub getTheOracleTime {
   my $sth = $dbh->prepare( $sql );
   $sth->execute();
   ($oracleTime) = $sth->fetchrow_array();
}
sub doExit {
   $dbh->disconnect(); # A clean and gracefull disconnection 8-)
   exit;
}

========== Code should be as above ==============


{116} Example 4-1

 It may be necessary to specify the ORACLE_HOME value
 in your environment.

 You may need to change add the following line:

   $ENV{ORACLE_HOME} = '/u02/app/oracle/product/8.1.7';

 The actual value needs to be the ORACLE_HOME for your environment.

 Add this line just above this line:

    my $dbh = DBI->connect('dbi:Oracle:ts01','system','sl3add',
      { RaiseError=>1} );


(136)
First line of chapter has two paired commas:

"...in Chapter 4, , is an easy..."

This should be:

"...in Chapter 4, Perl Web Extensions, is an easy..."

(with "Perl Web Extensions" in italics)
=========

(190)  comment at bottom of page;
just do that little bit extra

change to

just to do that little bit extra



[197]

In Example 7-3, please change the following two lines (they work
on Solaris, but not Linux):

===
bless $dbh => 'OCIEnvPtr';
OCIHandleAlloc($dbh, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0);
===

to

===
bless $dbh => 'OCIEnvPtr';
OCIHandleAlloc($dbh, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0);

# Alternative to two lines above, if they fail to work on your OS.
#my $env = get_oci_handle($dbh, OCI_HTYPE_ENV);
#OCIHandleAlloc($env, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0);
===

The bottom two lines work on Linux.

The full example is now:

========== Code should be as below ==============

#!/usr/bin/perl -w

use strict;

# Blended DBI and OCI

use DBI qw(neat);
use Oracle::OCI qw(:all);
# Step 1: Get the environment right, and set up your target
# database and user.
$ENV{ORACLE_SID} ||= 'ORCL';
my $dbuser = $ENV{ORACLE_USERID} || 'scott/tiger';

# Steps 2 & 3: We initialize and log onto the database.
my ($user, $pass) = split /\//, $dbuser;
my $dbh = DBI->connect("dbi:Oracle:$ENV{ORACLE_SID}", $user, $pass);

# Step 4: Now prepare the description of the target table, this time
# using OCI, after we've established our connection with DBI.
# Notice the frequent use of the Perl DBI $dbh variable.
my $tablename = $ARGV[0];

bless $dbh => 'OCIEnvPtr';
OCIHandleAlloc($dbh, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0);

# Alternative to two lines above, if they fail to work on your OS.
#my $env = get_oci_handle($dbh, OCI_HTYPE_ENV);
#OCIHandleAlloc($env, my $dschp, OCI_HTYPE_DESCRIBE, 0, 0);

bless $dschp => 'OCIDescribePtr';
OCIDescribeAny ($dbh, $dbh, oci_buf_len($tablename), OCI_OTYPE_NAME,
                1, OCI_PTYPE_TABLE, $dschp);

# Get the parameter descriptor.

OCIAttrGet ($dschp, OCI_HTYPE_DESCRIBE, my $parmp, 0, OCI_ATTR_PARAM,
            $dbh, 'OCIDescribePtr');

# Get the table list, number of columns and description.

OCIAttrGet ($parmp, OCI_DTYPE_PARAM, my $collst, 0,
            OCI_ATTR_LIST_COLUMNS, $dbh, 'OCIParamPtr');

OCIAttrGet ($parmp, OCI_DTYPE_PARAM, my $numcols, 0,
            OCI_ATTR_NUM_COLS, $dbh, 'OCIParamPtr');
my $errstr;

# Describe the target table.

printf ("\n------------------\n");
printf ("TABLE : %s \n", $tablename);
printf ("------------------\n");

my %col_attr = (
   OCI_ATTR_NAME       =>  "ColName",
   OCI_ATTR_IS_NULL    =>  "NULL?",
);

my $status;
foreach my $colnum (1..$$numcols) {

   my $col_parmdp_int = 0;
   my $col_parmdp = bless \$col_parmdp_int => 'OCIParamPtr';
   OCIParamGet($collst, OCI_DTYPE_PARAM, $dbh, $col_parmdp, $colnum);

   my $describe_attr = {
      OCI_ATTR_NAME       =>  0,
      OCI_ATTR_IS_NULL    =>  1,
   };

   printf "\n";
   foreach my $attr (sort keys %$describe_attr) {
      my $type = $describe_attr->{$attr};
      no strict 'refs';
      $status = OCIAttrGet( $col_parmdp, OCI_DTYPE_PARAM,
                            oci_buf_len(my $tmp, 90),
                            &$attr, $dbh, $type);
      warn "$attr: ".get_oci_error($dbh, $status, 'OCIAttrGet')
         if $status;
      warn get_oci_error($dbh, $status) if $status;
      printf "%-20s: %s\n", $col_attr{$attr}, neat($tmp);
    }
}

# Steps 5 & 6: Logout, clean-up and check out.
$dbh->disconnect;  # Bye, Bye !!! >=8+)

========== Code should be as above ==============


(201)

First line has:

"...ItC's..." (where the capital 'C' is a copyright symbol)

This should be:

"...It's..."


<221>

Nasty one this, The second bottom paragraph has an horribly ambiguous typo.  It
says:

"...-but one with a huge potential problem."

It should be:

"...-but one with a huge potential problem solving ability."


{497}

First line of table (\0) should be deleted