Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Oracle's RPAD problematic via Perl's DBI module

Oracle's RPAD problematic via Perl's DBI module

From: <dn_perl_at_hotmail.com>
Date: 24 Aug 2004 22:50:41 -0700
Message-ID: <97314b5b.0408242150.216b914c@posting.google.com>


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";

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


Received on Wed Aug 25 2004 - 00:50:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US