Problems with DBD::Oracle package: function plsql_errstr

[mjegh]

Copied from https://rt.cpan.org/Ticket/Display.html?id=90068

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

plsql_errstr.zip