Oracle's RPAD problematic via Perl's DBI module

[dn_perl]

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.

-----------

 

[Peter Marksteiner]

: 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

 

[Gregory Toomey]

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

 

[Mark Bole]

: 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

 

[J]

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.

-----------

Check this out
RPAD(?,8)

Does this work?Not probably!