In my Oracle 12.1 database, this query...
select CASE WHEN dummy IS NOT NULL THEN 'Y' ELSE 'N' END from dual;
...is a CHAR(1) field. I guess I never paid close attention to it before, but
I expected/assumed it would be a VARCHAR2(1).
It made a difference because I used it in a view and then used that view in a
Java-based framework that saw it was a CHAR field andtreated it differently
than how I wanted.
Oracle does this when the possible values are the same length. So, comparing
to views...
create or replace view matt_test_v1 as SELECT CASE WHEN dummy IS NOT NULL THEN
'Y' ELSE 'N' END test_field FROM dual;
...and...
create or replace view matt_test_v2 as SELECT CASE WHEN dummy IS NOT NULL THEN
'Y' ELSE 'NX' END test_field FROM dual;
we see that MATT_TEST_V1.TEST_FIELD is a CHAR(1) while MATT_TEST_V2.TEST_FIELD
is a VARCHAR2(2)
Can someone explain to me the logic / history behind this behavior? I was
unaware of any advantages that CHAR() has over VARCHAR2(), so I was surprised
that Oracle would use CHAR here when VARCHAR2 would work.
Thanks,Matt
