About char/varchar2 conversions

[sem]

Can anybody coment the following results:

SQL> select dump('abc') from dual;

DUMP('ABC')
----------------------
Typ=96 Len=3: 97,98,99

SQL> select dump(trim('abc')) from dual;

DUMP(TRIM('ABC'))
---------------------
Typ=1 Len=3: 97,98,99

SQL> select dump(a) from
(select 'abc' a from dual
union all
select trim('abc') a from dual)

DUMP(A)
--------------------------------------------------------------------------------
Typ=1 Len=3: 97,98,99
Typ=1 Len=3: 97,98,99

Moreover,

SQL> select dump(a) from
(select 'abc' a from dual
union all
select 'abcde' a from dual)

DUMP(A)
--------------------------------------------------------------------------------
Typ=1 Len=3: 97,98,99
Typ=1 Len=5: 97,98,99,100,101

Obviously, trim() returns ARCHAR2 and string literal is treated as CHAR, so the results of 2 first queries are predictable. But the third one is a bit strange: the field is not casted to the type in the first subquery. So it seems that all strings of different subtypes (lengths) are casted to varchar2. Can anybody provide some official source to prove or refute it?

Regards, Dima

 

[SantaMufasa]

Dima,

You actually provided the "...official source to prove or refute it..." -- the Oracle interpreter, which is ALWAYS the best official source to prove or refute. If there is ever a conflict between Oracle behavior and Oracle documentation, it's a bug. Since I cannot recall any Oracle documentation source that confirms casting behaviors for literals, then we must take BEHAVIOR as the "official source".

Your post also implied the following behavior of interest, when compared to your other examples:

select dump(a) from
(select 'abc' a from dual
union all
select 'abc' a from dual);

DUMP(A)
----------------------
Typ=96 Len=3: 97,98,99
Typ=96 Len=3: 97,98,99

Sorry, Dima, that my reply is not what you wanted, but I believe it is all that is available.

Cheers,

Dave

 

[sem]

In fact I need an official approvement to be able to rely upon this behaviour in future. So unfortunately the fact that it acts this way now doesn't suit me and I still need other opinions.

Regards, Dima