Why is this expression CHAR(1) instead of VARCHAR2(1)?
Date: Wed, 31 Mar 2021 22:26:40 +0000 (UTC)
Message-ID: <633878552.1463873.1617229600618_at_mail.yahoo.com>
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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 01 2021 - 00:26:40 CEST