| Subject: | Problems with DBD::Oracle package: function plsql_errstr |
I'm reporting this on behalf of Jarek Lubczyński as I haven't got the tuits right now to look at and hope someone else can pick it up.
His email forwarded by Tim was:
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)
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?
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.
-- Greetings Jarek Lubczyński There are 10 kinds of people: Those who understand binary and those who don't
--
Martin J. Evans
Wetherby, UK
| Subject: | plsql_errstr.zip |
Message body not shown because it is not plain text.