We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Interesting Oracle question DECODE vs CASE with CLOB conversion

mrjoltcola
mrjoltcola asked
on
Medium Priority
4,794 Views
Last Modified: 2012-05-07
I noticed something when answering a question today. Assume a table with a CLOB field.

create table t(text clob);

-- insert some data


Both of these queries throw ORA-00932

SQL> select case when 1=1 then '' else text end from t;
SQL> select case when 1=1 then cast('' as clob) else text end from t;

                          *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB



But these all work. Note the decode() does not use any casting or conversion.

SQL> select case when 1=1 then to_clob('') else text end from t;
SQL> select case when 1=1 then null else text end from t;
SQL> select decode(1, 1, '', text)  from t;


In one case, the '' is either being implicitly cast to CLOB or the whole expression is being cast to VARCHAR.


Anyone have some insight?
Comment
Watch Question

Top Expert 2009

Author

Commented:
I can understand perhaps why the others work, null is typeless, and to_clob('') is an explicit conversion.

What I don't get is why cast('' as clob) does not work and why decode() works without any casting or conversion at all.
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
very interesting indeed . the only explanations for difference in implicit and explicit clob conversions that implicit works after execution plan and by then expression is wrong.Decode seem to have clob overlay that case doesn't
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
per 24790367

"null is typeless"

that's not necessarily true.

The keyword NULL has no type.

''  is a typed NULL of varchar2 type.

with decode and clob,  as noted in 24790528, the type of the function is based on the type of the first expression returned
Top Expert 2009

Author

Commented:
>>>>"null is typeless"

>>that's not necessarily true.

>>The keyword NULL has no type.


Yes, the keyword NULL is typeless. Not sure what you are saying here. "Has no type" and "typeless" are equivalent to me. When used in an expression, NULL has no type meaning unless gathered by surrounding context.


But, my only remaining question is regarding the CASE statement and CAST from '' to CLOB, it appears casting with CAST keyword from '' to CLOB is not allowed or just doesn't work?

Information Technology Specialist
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2009

Author

Commented:
I should have looked that up myself. Thanks!
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
sorry to chime in late...

when I said "typeless" and "has no type" I meant them to be equivalent.

What I was trying to clarify was your statement "null is typeless",  a null value is not necessarily typeless.  

 the word "NULL" by itself is typeless

but '' is null but is has a varchar2 type.


similarly  to_number(NULL)  is a null number


that's all, nothing super insightful.  :)



Top Expert 2009

Author

Commented:
Okey doke. I suppose I agree with you now.. :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.