Thank you for providing this - please see below.
On 03/11/2013 12:06, Tim Bunce wrote:
- Forwarded message from Eljot na Onet.pl eljot_...@poczta.onet.pl -
Date: Sat, 02 Nov 2013 21:11:48 +0100
From: Eljot na Onet.pl eljot_...@poczta.onet.pl
To: t...@cpan.org
Subject: Problems with DBD::Oracle package: function plsql_errstr
Hi,
I have found two problems with your DBD::Oracle package.
(you have all examples in the attachment plsql_errstr.zip, please read
README.txt file)
which was missing unfortunately - perhaps when it was forwarded on.
problems concern sub plsql_errstr described in
http://search.cpan.org/~pythian/DBD-Oracle-1.64/lib/DBD/Oracle.pm
What's the point? You use view *user_errors* to fetch info about
errors in *last* query parsed. But the view returns list of ALL
current user errors - as name of the view means.
So if I execute example code from pod :
EXAMPLE 1: (01-example-1-sub.sh, calls pl/01-example-1-sub.pl)
# Show the errors if CREATE PROCEDURE fails
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
die $dbh-errstr if ! defined $msg;
die $msg if $msg;
}
I will get msg as in attachment 01-example-01.log containing the error
info I have expected. But the following code will not work properly:
(please execute 00.example-del-subs.sh first)
EXAMPLE 2: (02-example-2-subs.sh, calls pl/02-example-2-subs.pl)
# Show the errors if CREATE PROCEDURE fails
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test_1st as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
warn $dbh-errstr, \n if ! defined $msg defined $dbh-errstr;
warn $msg, \n if $msg;
}
# but this works not exactly as one should expect...
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE perl_dbd_oracle_test_2nd as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
warn $dbh-errstr, \n if ! defined $msg defined $dbh-errstr;
warn $msg, \n if $msg;
}
ooops - first we've got info about procedure perl_dbd_oracle_test_1st
(that's ok) but then we've got info about *both* procedures:
perl_dbd_oracle_test_1st and perl_dbd_oracle_test_2nd as well...
The second problem is more subtle. I think the common idea due to
security reasons is to have only one database user which has resource
and CREATE USER privilege, and the only user creates other users and
tables, views, etc for the them. Other users can do only DML queries.
In this case the view user_errors will obviously return the empty row
list. So the code:
EXAMPLE 3 (03-example-other-user.sh)
# Show the errors if CREATE PROCEDURE fails
$dbh-{RaiseError} = 0;
if ( $dbh-do( q{
CREATE OR REPLACE PROCEDURE myuser.perl_dbd_oracle_test as
BEGIN
PROCEDURE filltab( stuff OUT TAB ); asdf
END; } ) ) {} # Statement succeeded
}
elsif ( 6550 != $dbh-err ) { die $dbh-errstr; } # Utter failure
else {
my $msg = $dbh-func( 'plsql_errstr' );
die $dbh-errstr if ! defined $msg;
die $msg if $msg;
}
will return no info at all!
I have solved both problems, would You look at my version of
plsql_errstr in file 04-example-new-plsql-errstr.pl, please?
I don't know if you omitted an attachment of it didn't get passed on
when it was forwarded to dbi-users list. Would you mind sending me your
full email again with your new files/changes.
Alternatively, if you use git you could always submit a pull request to
the github repository (which you'll find at
https://github.com/pythian/DBD-Oracle).
As You can see I have defined *sub* *plsql_errarray* which select
error info from database and returns them in array reference much more
convinient for later use; *plsql_errstr* only converts that array into
a single string. Of course one should provide at least two additional
parameters to new plsql_errstr function.
Martin
--
Martin J. Evans
Wetherby, UK
