CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Reinaldo Peres
Reinaldo Peres

Reputation: 372

NVL function with CLOB datatype

I have this query:

select LENGTH(NVL(exam.vl_result,' ')) from exam;

And I got this error: ORA-00932: inconsistent datatypes: expected - got CLOB

The column vl_result is CLOB, but I need to use NVL in this case. Is there any way to use NVL with datatype CLOB? If not, what can be the best way to get a similar query.

Upvotes: 0

Views: 2385

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Hmmm . . . how about running length() first:

coalesce(length(exam.vl_result), 1)

Upvotes: 1

Popeye
Popeye

Reputation: 35920

Use case..when as follows:

select case when exam.vl_result is null then 1 
            else LENGTH(exam.vl_result) 
       end 
  from exam;

Note: To find the length of clob use dbms_lob.getlength

Upvotes: 1

Related Questions