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 🙂