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
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602

RE: Minor gotcha with bind variables and type CHAR.

I would expect to have to pad the field in my where clause against a char f=
ield, regardless of how I submitted the query to Oracle.

select count (*)=20
from webuserparm=20
where usercd =3D "MYUSER "
and cltid =3D 10101
;

If you want to not have to pad, then it should be a varchar2. You would al=
so save a lot of space on that email address. I pretty much banned char a =
long time ago because of these kinds of problems. 'Is it left or right jus=
tified?' My favorite was one time the value was centered! Excuse me while=
I duck...

You could also use a trim( usercd ), but that kind of sucks.


-----Original Message-----
From: Stephen Carville [mailto:stephen@totalflood.com]
Sent: Thursday, August 10, 2006 9:48 PM
To: DBI Users
Subject: 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=20
CHAR do not behave as expected. For example, one of my DB's has the=20
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=20
so I have an incentive to use bind variables:

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

If I run it

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

$usercd =3D "MYUSER";
$cltid =3D 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 =3D pack("A15",$usercd);

I get a "1" as expected.

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

Perl version 5.8.2 and 5.8.6
DBI version 1.51
OracleDBD version 1.17

--=20
Stephen Carville
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602


The information contained in this message and any attachment may be
proprietary, confidential, and privileged or subject to the work
product doctrine and thus protected from disclosure. If the reader
of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended
recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please notify me
immediately by replying to this message and deleting it and all
copies and backups thereof. Thank you.

Re: Minor gotcha with bind variables and type CHAR.

On Thu, Aug 10, 2006 at 06:48:01PM -0700, Stephen Carville wrote:
> 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.

Works as designed - if not as expected: A CHAR always has a fixed length.
In such cases I use

"select count (*) from webuserparm where usercd = RPAD(?, 15) and cltid = ?"

Peter

--
Peter Marksteiner
Vienna University Computer Center

RE: Minor gotcha with bind variables and type CHAR.

Stephen,

This is old news. A CHAR type in Oracle has always been stored in the
DB padded with spaces to for the length of the column. A VARCHAR2 is a
better choice for variable length character type columns.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc

-----Original Message-----
From: Stephen Carville [mailto:stephen@totalflood.com]=20
Sent: Thursday, August 10, 2006 7:48 PM
To: DBI Users
Subject: 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=20
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=20
so I have an incentive to use bind variables:

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

If I run it

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

$usercd =3D "MYUSER";
$cltid =3D 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 =3D pack("A15",$usercd);

I get a "1" as expected.

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

Perl version 5.8.2 and 5.8.6
DBI version 1.51
OracleDBD version 1.17

--=20
Stephen Carville
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Minor gotcha with bind variables and type CHAR.

Michael.Coll-Barth@VerizonWireless.com wrote:
> I would expect to have to pad the field in my where clause against a
> char field, regardless of how I submitted the query to Oracle.

Until I switched to using bind variables, I'd never had a problem.
Trimmed fields compared fine to CHAR and VARCHAR2 alike. I'm not even
close to expert with Oracle so this behavior surprised me. Now tht I
know it's normal I'll watch out for it.

> select count (*)
> from webuserparm
> where usercd = "MYUSER "
> and cltid = 10101 ;
>
> If you want to not have to pad, then it should be a varchar2. You
> would also save a lot of space on that email address. I pretty much
> banned char a long time ago because of these kinds of problems. 'Is
> it left or right justified?' My favorite was one time the value was
> centered! Excuse me while I duck...

This database stared out at Oracle7, then went to 8, then 9i (I came
onboard right after that migration), now it's at 10g. Some columns have
been converted to varchar2 but there is "don't fix it until it breaks"
philosophy in management. At least they'll let me throw hardware at the
problem so I've been agitating that on the next hardware upgrade (soon)
we rebuild the whole thing as 10g and import the data.

> You could also use a trim( usercd ), but that kind of sucks.

One of the java developers tried that as a workaround for the same
problem in the ODBC drivers but Oracle didn't want to cache the script.
Sorta defeats the purpose of using bind variables.

--
Stephen Carville
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602

Re: Minor gotcha with bind variables and type CHAR.

--2Z2K0IlrPCVsbNpk
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2006-08-11 18:25:39 -0700, Stephen Carville wrote:
> Michael.Coll-Barth@VerizonWireless.com wrote:
> >I would expect to have to pad the field in my where clause against a
> >char field, regardless of how I submitted the query to Oracle.
>=20
> Until I switched to using bind variables, I'd never had a problem.
> Trimmed fields compared fine to CHAR and VARCHAR2 alike.

I think Oracle pads the string with spaces if you don't use
placeholders. You can get it to do that with placeholders, too, if you
explicitely bind the variables (there are several char types, and one of
them does the padding). There have been occasional discussions about
this behaviour over the years, and so far nobody could come up with a
method which "does the right thing" in all cases - the current behaviour
is the one which is least surprising in most cases.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--2Z2K0IlrPCVsbNpk
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iQDQAwUBROAzkFLjemazOuKpAQL6IQXTBk6rpQlzK+ELNv/m5x6sX4qFZsPg l4NA
b/GAYp9LnqgSiL9M2ckOB/OiahTFGOIXJurLETyG9j5FX3E849mzEkfsaKSi kIPL
c+YWsjVJpRS+I8UOqGMYXtxQFtoKa5XInumnFoneNX4aUR5JhTKU0I1mdGHK LqMU
WItJU94SN9rgIJwLNK83fH/DfE6m8gtKJ3HndVZF5KZG5ZiEj9rSIJ8e84mc 8tj8
DRkSinYhXdp4zaQouBJoljHC7w==
=wQTz
-----END PGP SIGNATURE-----

--2Z2K0IlrPCVsbNpk--