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

From: Peter Marksteiner <pm_at_katz.cc.univie.ac.at>
Date: 25 Aug 2004 09:44:24 GMT
Message-ID: <412c5f78$0$11094$3b214f66@usenet.univie.ac.at>

In comp.lang.perl.misc dn_perl_at_hotmail.com <dn_perl_at_hotmail.com> wrote:
: Say the table is : students(name CHAR(8))
: Entries in Students are : ' ' (8 blanks),
: 'bob ' and 'dave ' .

: 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.

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

What Oracle Version are you using? There are some subtle differences in the handling of trailing blanks between Oracle 8 and 9. I have a table "foo" containing two blank entries. When running the following code

my $ss = "SELECT COUNT(*) FROM foo WHERE bar = RPAD(?,8)";
my $blank = ' ';
my $sth = $dbh->prepare($ss) or die;

$sth->execute($blank) or die;
print "Blank: ", $sth->fetch->[0], "\n";

I get the following result:

Blank: 2        # Oracle 9 database using local Oracle 9 client 
Blank: 2        # Oracle 9 client connecting to a remote Oracle 8 database
Blank: 0        # Oracle 8 database using local Oracle 8 client
Blank: 0        # Oracle 8 client connecting to a remote Oracle 9 database

   Peter

-- 
Peter Marksteiner
Vienna University Computer Center

Received on Wed Aug 25 2004 - 04:44:24 CDT