Minor gotcha with bind variables and type CHAR.

A note on bind variables:

I'm running Oracle 10gR2 and select stateens with bind variables of type 
CHAR do not behave as expected.  For example, one of my DB's has the 
following table in it:

SQL> desc webuserparm;
  Name               Null?    Type
  ------------------ -------- -------------
  USERCD             NOT NULL CHAR(15)
  USERNAME           NOT NULL VARCHAR2(25)
  USERPWD            NOT NULL CHAR(15)
  GRPID              NOT NULL NUMBER(5)
  ACTIVEFLAG         NOT NULL CHAR(1)
  USERTYPE                    CHAR(1)
  CLTID                       NUMBER(8)
  EMAILID                     CHAR(200)
  BUSINESSPHONE               VARCHAR2(10)
  BUSINESSPHONEEXTN           CHAR(4)
  BUSINESSTITLE               VARCHAR2(50)
  DPSIMTGPROVIDERID           VARCHAR2(15)
  DPSIMTGCOMPANYID            VARCHAR2(15)
  DPSIMTGUSERID               VARCHAR2(15)
  DPSIMTGUSERPWD              VARCHAR2(15)

I run the following SQL against this table several thousand time a day 
so I have an incentive to use bind variables:

$script =
  "select count (*) from webuserparm where usercd = ? and cltid = ?";

If I run it

$sth = $dbh->prepare($script);

$usercd = "MYUSER";
$cltid = 10101;

$sth->execute($usercd,$cltid);

I get a "0" even tho MYUSER and 10101 are in the same row.

If I add just before the execute a function padding the string with spaces:

$usercd = pack("A15",$usercd);

I get a "1" as expected.

Apparently Oracle compares a space padded copy of a CHAR to the 
submitted value.

Perl version 5.8.2 and 5.8.6
DBI version 1.51
OracleDBD version 1.17

-- 
Stephen Carville <stephen@totalflood.com>
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602

• Minor gotcha with bind variables and type CHAR. by Stephen Carville
• RE: Minor gotcha with bind variables and type CHAR. by Michael.Coll-Barth
• Re: Minor gotcha with bind variables and type CHAR. by Stephen Carville
• Re: Minor gotcha with bind variables and type CHAR. by Peter J. Holzer
• RE: Minor gotcha with bind variables and type CHAR. by Reidy, Ron
• Re: Minor gotcha with bind variables and type CHAR. by Peter Marksteiner