DBD::Oracle multiple SQL statements - PL/SQL example

[Colin . Woodford]

DBI-Users,

The DBI prepare statement documentation 
http://search.cpan.org/author/TIMB/DBI-1.32/DBI.pm#prepare says prepare "Prepares a 
single statement for later execution by the database engine"

The DBD::Oracle PL/SQL examples 
http://search.cpan.org/author/TIMB/DBD-Oracle-1.12/Oracle.pm#PL_SQL_Examples 
illustrates multiple statements in a SQL string.

When I try to test this the following error from 'plsql_errstr' indicates that it is 
not possible to have multiple statements in a SQL string. The PL/SQL statements are 
not being treated individually.

Errors for PACKAGE PLSQL_EXAMPLE:
6.6: PLS-00103: Encountered the symbol "CREATE" 

I would be grateful for any feedback about preparing and executing multiple SQL 
statements using DBD::Oracle.

The test code I used is :

#!/perl/5.8.0/bin/perl

# pragmata
use strict;
use warnings;

use DBI;

{
my($msg, $dbh, $sth, $ret_val);

my $sid="DATABASED1";
my $user="username";
my $passwd="password";

$dbh = DBI->connect("dbi:Oracle:$sid", $user, $passwd);

# we assume this package already exists
my $plsql = q{

      CREATE OR REPLACE PACKAGE plsql_example
      IS
            PROCEDURE proc_np;
      END plsql_example;

      CREATE OR REPLACE PACKAGE BODY plsql_example
      IS
            PROCEDURE proc_np
      IS
            whoami VARCHAR2(20) := NULL;
      BEGIN
            SELECT USER INTO whoami FROM DUAL;
      END;
      END plsql_example;
      };

      $dbh->{RaiseError} = 0;

      $sth = $dbh->prepare($plsql);
      $sth->execute;

      $msg = $dbh->func( 'plsql_errstr' );
      die $dbh->errstr if ! defined $msg;
      die $msg if $msg;
}
# END OF CODE

Many Thanks,
Colin Woodford

Visit our website at http://www.ubswarburg.com

This message contains confidential information and is intended only
for the individual named.  If you are not the named addressee you
should not disseminate, distribute or copy this e-mail.  Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses.  The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission.  If
verification is required please request a hard-copy version.  This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.