Re: Oracle's RPAD problematic via Perl's DBI module

dn_perl_at_hotmail.com wrote:

> The following code is returning an unexpected result.
> (Untested cut-n-paste; apologies)
>
>
> Say the table is : students(name CHAR(8))
> Entries in Students are : ' ' (8 blanks),
> 'bob ' and 'dave ' .
>
> use strict ;
> use DBI ;
>
> my $st_name = " " ; # non-blank name
> my $dstmt = $dbh->prepare("select count(*) from students
> where name = RPAD(?,8) ") ; # STMT AA
> $dstmt->execute($st_name) or die "sql call failed";
> my $num_entries = $dstmt->fetchrow() ;
> $dstmt->finish ;
>
> $num_entries should be set to 1; instead it is set to 0.
> This problem occurs only for a blank string.
> If $st_name = "dave" , then the statements work properly.
>
> If I run the query via sqlplus :
> select count(*) from students where name = RPAD(' ',8) ,
> the result is 1, as expected.
>
> I am using the getaround :
> my $dstmt = $dbh->prepare("select count(*) from students
> where trim(name) = ? or (name = ' ' and trim(?) is null) ") ;
> $dstmt->execute($st_name, $st_name) or die "sql call failed";

This is doing a full table scan.  

> But still I am surprised why STMT AA (above) fails to return
> the expected result.
>
> -----------

For a start its very unusual to rpad fields in a database.

The underlying problem probably has to do with Oracle/dbi treatment of '' and NULL.

gtoomey Received on Wed Aug 25 2004 - 04:53:00 CDT