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

Hi Matt,

It's probably because literal strings in the SQL text are constants, their length doesn't change (as long as you don't change the SQL text or view text), therefore a static-length CHAR datatype is fine (would be interesting to see what happens when the length of the literal string is more than 2000 bytes though).

If you do the same thing with bind variables, the returned datatype changes:

SQL> SELECT DUMP(CASE WHEN dummy = 'X' THEN 'x' ELSE 'y' END) col FROM dual;

COL

SQL> VAR x VARCHAR2(1)
SQL> VAR y VARCHAR2(1)
SQL> EXEC :x:='x'

PL/SQL procedure successfully completed.

SQL> EXEC :y:='y'

PL/SQL procedure successfully completed.

SQL> SELECT DUMP(CASE WHEN dummy = 'X' THEN *:x *ELSE *:y *END) col FROM dual;

COL

SQL> OCI datatype #1 is VARCHAR2 and #96 is CHAR...

--
Tanel Poder
#vConf2021: Troubleshooting Very Complex Oracle Performance Problems
https://tanelpoder.com/conference/


On Wed, Mar 31, 2021 at 6:26 PM mcpeakm_at_tempus-consulting-group.com <
mcpeakm_at_tempus-consulting-group.com> wrote:



> 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 and

> treated 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