On Sat, Mar 19, 2005 at 02:36:11AM -0500, Matthew Persico wrote:
> I had an occasion to use the func('plsql_errstr') extension of
> DBD::Oracle today. It didn't exactly fit my needs. The problem is that
> the function as currently defined returns ALL of the contents of
> user_errors table, some of which are very old from pacakges that are
> not what I am loading. As implemented, it is the functional equivilent
> of
>
> show errors
>
> when I really need
>
> show errors table foo
>
> Soooo, I put the following in my script:
>
> package DBD::Oracle::db;
> sub plsql_errstr2 {
> # original version thanks to Bob Menteer
> my $sth = shift->prepare_cached(q{
> SELECT name, type, line, position, text
> FROM user_errors ORDER BY name, type, sequence
> }) or return undef;
> $sth->execute or return undef;
> my ( @msg, $oname, $otype, $name, $type, $line, $pos, $text, %msg_idx );
> $oname = $otype = 0;
> while ( ( $name, $type, $line, $pos, $text ) = $sth->fetchrow_array ) {
> if ( $oname ne $name || $otype ne $type ) {
> push @msg, "Errors for $type $name:";
> $oname = $name;
> $otype = $type;
> $msg_idx{$name} = {
> 'name' => $name,
> 'type' => $type,
> msg => []
> };
> }
> push @msg, "$line.$pos: $text";
> push @{$msg_idx{$name}->{'msg'}}, "$line.$pos: $text";
> }
> if([EMAIL PROTECTED]) {
> return join( "\n", @msg );
> } else {
> return join( "\n", {$msg_idx{$_[0]}->{'msg'}} );
Um, isn't there an @ missing on that line?
> }
> }
>
> and called
>
> $dbh->func('mytrigger','plsql_errstr2'')
>
> after creating my trigger. Worked like a charm. May I suggest
> replacing the current version of plqsl_errstr with this one.
Ideally I'd rather it was implemented by using the argument to
qualify the select statement - rather than selecting everything
then discarding much of it. But I guess it's not a big deal as most
of the time the volume of records is very low.
But do please use a more traditional parameter style
sub plsql_errstr {
my ($dbh, @names) = @_;
...
> If accpeted, point me to where I should patch the corresponding tests and
> I will do so.
t/25plsql.t has some tests already but they're commented out.
Would be great to uncomment, polish up, and extend them.
And, of course, don't forget the docs :)
Thanks!
> Now, you will notice that I had to name the function plsql_errstr2.
> When I named it plsql_errstr, it did not override the existing
> function. My suspicion is that either DBI does some unusualy funky
> dispatch stuff OR I simply don't understand how to override functions
> without subclassing. Would anyone care to enlighten me as to why I
> failed to override plsql_errstr?
I can't think of any reason off the top of my head.
Tim.
