ORA-00932: inconsistent datatypes: expected - got CLOB

Replace

nullif(select_clause,'COL1')

with

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

NULLIF can't handle CLOB. Use CASE:

CASE

  WHEN select_clause = to_clob('col1') THEN CAST(NULL AS CLOB)

  ELSE select_clause

END

SY.

Solomon Yakobson wrote:

 

NULLIF can't handle CLOB. Use CASE:

 

CASE

WHEN select_clause = to_clob('col1') THEN CAST(NULL AS CLOB)

ELSE select_clause

END

 

SY.

This, like the original solution you posted (now removed), will fail for the same reason, and the same reason NULLIF fails. CASE doesn't allow a CLOB in an equality comparison in the WHEN clause. Happily, LIKE doesn't have the same restriction.

select case when to_clob('COL1') = to_clob('COL1') then null end from dual;

                                  *

 

ORA-00932: inconsistent datatypes: expected - got CLOB

00932. 00000 -  "inconsistent datatypes: expected %s got %s"

*Cause:   

*Action:

Error at Line: 11 Column: 18

 

 

 

select case when to_clob('COL1') like 'COL1' then null end from dual;

 

 

CASEWHENTO_CLOB('COL1')LIKE'COL1'THENNULLEND

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

 

 

(1 row selected)

The latter also works with a CLOB as the right-hand side operand to LIKE.

If NULLIF cannot handle clobs then the documentation should state so. We don't know your version, but the 19c doc says:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NULLIF.html#GUID-445FC268-7FFA-4850-98C9-D53D88AB240…

if the arguments are not numeric, then they must be of the same data type, or Oracle returns an error.

If both are CLOB, then according to the doc it should not return an error.

The documentation doesn't show it, but if you try NULLIF(to_clob('COL1'), to_clob('COL1')) you will get the same error, at least in Oracle 12.2 (my version).

The data types allowed in the WHEN clause of a simple CASE expression are stated explicitly in the documentation, and they don't include CLOB. The documentation for searched CASE doesn't say why "clob = other clob" is not allowed; perhaps the parser re-writes it as a simple CASE expression, and then it fails when comparing CLOB? (And then, the same would explain why NULLIF doesn't accept 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                

Just for fun:

REGEXP_REPLACE(SELECT_CLAUSE,'^COL1$')

SY.

mathguy wrote:

 

In any case, LIKE does work with CLOB, and it does not truncate.

I wouldn't be so affirmative.

Let's see what happens with some other CLOB sizes :

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 Y

Boundary size seems to be 31999 for the RHS operand, as shown below :

with t (cl1, cl2) as (
  select lpad(to_clob('x'), 100000, 'x')
       , lpad(to_clob('x'), 31999, '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       31999 N

Not equal in that case.

Funny thing is that the comparison is apparently done from right to left, e.g. after appending a character to both operands :

with t (cl1, cl2) as (
  select lpad(to_clob('x'), 100000, 'x') || 'a'
       , lpad(to_clob('x'), 31999, 'x') || 'a'
  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
----------- ----------- ---------
     100001       32000 Y

Unexpectedly equal again.

And if the size of the RHS operand exceeds 32767 :

with t (cl1, cl2) as (
  select lpad(to_clob('x'), 100000, 'x')
       , lpad(to_clob('x'), 32768, 'x')
  from dual
)
select length(cl1), length(cl2),
       case when cl1 like cl2 then 'Y' else 'N' end as "CL1 = CL2"
from t

ORA-22828: input pattern or replacement parameters exceed 32K size limit

Back to OP's question, I think the best solution requires knowing what this 'COL1' could be in reality.

If it's always a value in the VARCHAR2 range, then it's probably OK to use LIKE, though I personally wouldn't rely on undocumented feature (or bug, depending on the POV).

If it could be an arbitrary CLOB, then DBMS_LOB.COMPARE is the safest option.