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

Peter Marksteiner wrote:

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

I too received the correct result using the OP's test case under Oracle 9i client and server.

Only Oracle 8.1.7.4 client (terminal release of 8i product) is certified for connecting to Oracle 9i server. Earlier versions (such as 8.1.7.3) are not, and you may also face other issues, such as mysterious errors related to the DATE datatype. Highly recommended to recompile your DBD module using the Oracle 9i libraries if you haven't done so.

You might also find help in the Perl documentation under Database Handle Attributes, "ora_ph_type". Your use of the CHAR datatype in your table is unusual in my experience, one almost always uses VARCHAR2 instead. Try searching for "blank-padded comparison semantics" at http://tahiti.oracle.com

ORA_VARCHAR2 - Strip trailing spaces and allow embedded \0 bytes.

              This is the normal default placeholder type.

ORA_STRING - Don't strip trailing spaces and end the string at

              the first \0.

ORA_CHAR - Don't strip trailing spaces and allow embedded \0.

              Force 'blank-padded comparison semantics'.

--Mark Bole Received on Wed Aug 25 2004 - 06:29:27 CDT