Home -> Mailing Lists -> Oracle-L -> Why is this expression CHAR(1) instead of VARCHAR2(1)?

Why is this expression CHAR(1) instead of VARCHAR2(1)?

From: <mcpeakm_at_tempus-consulting-group.com>
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












Original text of this message