in reply to DBI Deletes with placeholders

Corion prompted me to check the type of the column that I was using in the where clause. It was a CHAR(6). I was looking for a 5 character string. I padded the string with a trailing space and everything worked properly.

Yeah, that is a well-known issue on Oracle, making use of the CHAR type rather error-prone.

CHAR is a fixed length type, and all data gets padded with spaces. If you want to query the table, you also have to pad the constants in your WHERE clause accordingly, or they will not match. When directly interpolating into the SQL string (which is discouraged), Oracle will add the padding automatically, but when using bind variables, it will not. Unless your data is really fixed (and maybe even then) you should consider VARCHAR2.

Replies are listed 'Best First'.
Re^2: DBI Deletes with placeholders
by AKSHUN (Novice) on Feb 05, 2008 at 15:15 UTC
    Unfortunately, or fortunately depending on one's perspective, I don't get to make any design decisions on this database or table. Basically, it is how it is and I have to work with it. But again, I appreciate everyone's help.