FAQ: Unexpected results with a where clause for a char column

[faq]

I am using a query with a where clause and a string variable for a char column, but I'm not getting any results. When I replace the string variable with a string literal, it works as expected:

select * from emp where ename = 'SCOTT'

This statement returns 1 row.

select * from emp where ename = :v_ename

This statement does not return any row, even though the value of :v_ename is 'SCOTT'.

What's wrong?

 

[support]

String variables in Direct Oracle Access are treated as VARCHAR2 variables on the Oracle Server. If you compare a CHAR column with a VARCHAR2 variable, trailing spaces are significant.

Use a Char variable in the TOracleQuery or TOracleDataSet for this where clause to fix the problem. You should also try to avoid CHAR columns in your database if you can.