Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

nvl function changes dataType from char to varchar2

Aketi JyuuzouJun 21 2011 — edited Jun 21 2011
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table fullJoinTest1(col1 char(14));

Table created.

SQL> create table fullJoinTest2(col1 char(14));

Table created.

SQL> insert all
  2  into fullJoinTest1 values('aaa')
  3  into fullJoinTest1 values('bbb')
  4  into fullJoinTest2 values('aaa')
  5  into fullJoinTest2 values('ccc')
  6  select 1 from dual;

4 rows created.

SQL> select nvl(a.col1,b.col1) as aaaa
  2    from fullJoinTest1 a full join fullJoinTest2 b
  3      on a.col1=b.col1
  4   where nvl(a.col1,b.col1)= 'ccc';

no rows selected

SQL> create table fullJoinTest3 as
  2  select nvl(a.col1,b.col1) as "WhatIsType?"
  3    from fullJoinTest1 a full join fullJoinTest2 b
  4      on a.col1=b.col1
  5   where nvl(a.col1,b.col1)= 'ccc';

Table created.

SQL> desc fullJoinTest3
 Name          Null?    Type
 ------------- -------- ------------
 WhatIsType?            VARCHAR2(14)

SQL> create table fullJoinTest4 as
  2  select case when a.col1 is not null then a.col1 else b.col1 end as "WhatIsType?"
  3    from fullJoinTest1 a full join fullJoinTest2 b
  4      on a.col1=b.col1;

Table created.

SQL> desc fullJoinTest4
 Name        Null?    Type
 ----------- -------- --------
 WhatIsType?          CHAR(14)
My question is why nvl function changes dataType from char to varchar2 ?
Even case expression does not change dataType.
I insist that this is a nvl functions bug is not it.
This post has been answered by Dom Brooks on Jun 21 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 19 2011
Added on Jun 21 2011
4 comments
3,710 views