How easy it is to forget!

On creating a view with a literal (alphanumeric) value, Oracle will use the CHAR datatype.

 SQL> create table t1 (id number, val varchar2(20));

Table created.

SQL> create view v1 as select id, val, 'Tweedle' dum from t1;

View created.

SQL> desc v1;

Name Null? Type

--------- -------- -----------------

ID                   NUMBER

VAL                VARCHAR2(20)

DUM             CHAR(7)

This is no problem, since the value is a constant. But what if we mix the datatypes?

SQL> create view v2 as select id, val, 'Doodle' dum from t1;

View created.

SQL> desc v2;
Name Null? Type
--------- -------- ---------------

ID                    NUMBER
VAL                 VARCHAR2(20)
DUM               CHAR(6)

SQL> create view v3 as select * from v1 union select * from v2;

View created.

SQL> desc v3;
Name Null? Type
--------- -------- ---------------

ID                   NUMBER
VAL                VARCHAR2(20)
DUM              VARCHAR2(7)

Oracle automagically changed the datatype to VARCHAR2. (This is also documented in the SQL Reference for UNION (ALL). But it can still lead to some funny results:

SQL> create table t2 (val char(7));

Table created.

SQL> insert into t2 values ('Doodle');

1 row created.

SQL> select * From v3 where dum in (select val from t2);

no rows selected

SQL> select * From v2 where dum in (select val from t2);

ID VAL DUM
---------- -------------------- ------
1 Test Doodle

SQL> select val||'x' from t2;

VAL||'X'
--------
Doodle x

Never a dull moment with Oracle and creative developers 🙂