ORA-00932: inconsistent datatypes: expected - got CLOB

mathguy wrote:

 

Replace

 

nullif(select_clause,'COL1')

 

with

 

case when select_clause like 'COL1' then null else select_clause end

I don't agree with your approach at all.  You're assuming...

1. OP will always be using the input 'COL1', what if it actually parameterised and the input contains wildcards?  e.g. 'COL1%' or 'COL_123'?

2. LIKE will always support CLOBs in all Oracle versions and in the manner you're intending.

3. LIKE isn't truncating during comparison.

The correct approach is to use the LOB comparison function Oracle have specifically provided, which is also optimised for LOB comparisons

case when DBMS_LOB.Compare(select_clause, 'COL1') = 0 then null else select_clause end

You don't agree with my approach at all? Not even a little bit? Not even in the context of all the other attempts offered in this thread? You are hurting my feelings!

Judging by your suggestion, though, you do agree with it somewhat. You do agree that we need a CASE expression, NULLIF won't work. You do agree that a simple CASE expression won't work. And you do agree that in it we can't use equality of CLOB, and instead we need a searched CASE expression with a logical condition other than equality of CLOB. I wouldn't call that "at all".

Then, regarding your objections:  (1) If the RHS has % or _ in it, we can use the ESCAPE clause of LIKE (that's precisely why that clause exists). Besides, the OP may, in fact, at some point, want to use LIKE with wildcard characters; I don't know if DBMS_LOB.COMPARE can do that. REGEXP_LIKE can, if LIKE is not OK to use. (3) doesn't even make sense if the RHS is strictly less than 4000 characters; LIKE will be true for the full CLOB if and only if it is true for the CLOB truncated to 4000 characters. (Or - rephrase that in bytes, for multi-byte character sets.) And, in any case, CLOB's are not truncated in the LIKE condition, as shown below.

The only valid objection is that the documentation of LIKE doesn't seem to allow CLOB. I don't know if this is a documentation flaw, or if in fact CLOB in LIKE is not meant to be used. (I also didn't check yet to see if CLOB in LIKE is in fact documented elsewhere, but not in the "obvious place"; that happens often with Oracle documentation). In any case, LIKE does work with CLOB, and it does not truncate.

Nothing wrong with using DBMS_LOB.COMPARE, of course; but your comments about my approach don't seem to hold much water.

Test to show that LIKE does not truncate CLOB, on either side:

with

  t (cl1, cl2) as (

    select to_clob('x') || lpad('x', 3999, 'x'), to_clob('x') || lpad('x', 4000, 'x')

    from   dual

  )

select length(cl1), length(cl2),

       case when cl1 like cl2 then 'Y' else 'N' end as rhs_is_truncated,

       case when cl2 like cl1 then 'Y' else 'N' end as lhs_is_truncated

from   t

;

 

LENGTH(CL1) LENGTH(CL2) RHS_IS_TRUNCATED     LHS_IS_TRUNCATED  

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

       4000        4001 N                    N                

I do agree that DBMS_LOB.* is the safe way to go for CLOBs. My reply to Paulzip was more about the editorial comment than the actual content.

With that said:

(1) If I understand the OP's attempted code, he's building an aggregate of column names, and he is checking to see that the list doesn't simply consist of a single, specific column name. So the RHS is limited to a small number of characters.

(2) What you show, about truncating CLOBs in the LIKE condition, seems version-specific. (Which still shouldn't be relied on in any manner, if not documented... but in our case, if the RHS is relatively short VARCHAR2, then NO truncation of the LHS will lead to wrong results). For example, on my machine I get

with t (cl1, cl2) as (

  select lpad(to_clob('x'), 100000, 'x')

      , lpad(to_clob('x'), 32000, 'x')

  from dual

)

select length(cl1), length(cl2),

      case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"

from t

;

 

LENGTH(CL1) LENGTH(CL2) CL1 = CL2

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

     100000       32000         N

(Where you got Y)  My version is 12.2.0.1.  And CL2 can't go past 32,767 bytes.

mathguy wrote:

 

(2) What you show, about truncating CLOBs in the LIKE condition, seems version-specific.

[...]

My version is 12.2.0.1.

Yes, forgot to mention the db version in my post : it's 12.1.0.2.

I confirm it works too on 18.4.

And if the RHS is indeed always a VARCHAR2, then the following is another option :

case when length(LHS) = length(RHS) and dbms_lob.substr(LHS) = RHS then null else LHS end