Re: (Fwd) Problems with DBD::Oracle package: function plsql_errstr

Thank you for providing this - please see below.

On 03/11/2013 12:06, Tim Bunce wrote:
> ----- Forwarded message from "Eljot na Onet.pl" <[email protected]> -----
>
> Date: Sat, 02 Nov 2013 21:11:48 +0100
> From: "Eljot na Onet.pl" <[email protected]>
> To: [email protected]
> 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