sqloracle-databaseoracle11gora-00932

How to test value of Alien_Body_Part (NCLOB) in Oracle projection list?


I have a table with three NCLOB columns. For each NCLOB I want a count of how many are not 'TC' or 'NC'. The case when ... end approach works for NVARCHAR2 columns but not for NCLOB. How can I test the values of an NCLOB in the projection list?

Oracle Database 11g Release 11.1.0.6.0

This minimal example demonstrates the root issue.

create table t (
  alien_body_part nclob
);

insert into t(alien_body_part) values(null);
insert into t(alien_body_part) values('TC');
insert into t(alien_body_part) values('NC');
insert into t(alien_body_part) values('Extended Mandible');

select case when alien_body_part in ('TC', 'NC') then 0 else 1 end from t
                 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got NCLOB

Solution

  • Only compare the first characters:

    SQL> SELECT dbms_lob.substr(alien_body_part, 4000, 1) body_part,
      2         CASE
      3            WHEN dbms_lob.substr(alien_body_part, 4000, 1)
      4                 IN ('TC', 'NC') THEN
      5             0
      6            ELSE
      7             1
      8         END is_nc_or_tc
      9    FROM t;
    
    BODY_PART              IS_NC_OR_TC
    ---------------------- -----------
                                     1
    TC                               0
    NC                               0
    Extended Mandible                1
    

    In this case since one side of the comparison is only 2 characters long, comparing the first 3 characters would be sufficient (as a NCLOB will be equal to 'TC' only if it is 2 characters long AND those characters equal 'TC' obviously).

    Also neither the CASE nor the IN is the cause of the error here (you can't compare directly a CLOB/NCLOB in SQL), consider:

    SQL> select * from t where alien_body_part = 'TC';
    
    select * from t where alien_body_part = 'TC'
    
    ORA-00932: inconsistent datatypes: expected - got NCLOB