Oracle SQL Select where clob field is not empty/null

5
votes

I am trying to get rows from an archive database for populating a test environment DB. I need rows where a specific field called "AUDIT_LOG" which is CLOB cannot be empty null.

I have tried the following simple select statements but I get

ORA-00932: inconsistent datatypes: expected - got CLOB

The statements I have tried:

SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE AUDIT_LOG = ''

SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE AUDIT_LOG != NULL

SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE AUDIT_LOG = 0

Does a NULL in CLOB equate to an empty character string. I have a feeling maybe length can be used for the column.

Thanks

3
sqloracle10gclob

3 Answers

10
votes

to check NULL, regardless of datatype, you use IS NOT NULL or IS NULL

WHERE AUDIT_LOG IS NOT NULL

But keep in mind that for CLOBs, an EMPTY_CLOB() has no characters but is not the same as NULL. If you want to exclude EMPTY_CLOB(), use @user3837669's answer that uses a LENGTH comparison.

6
votes
SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE length(AUDIT_LOG) > 0
0
votes

None of the above methods worked for me in Oracle 10g: I have to write it in a bad way:

select * from SIEBEL.S_AUDIT_ITEM where to_char(AUDIT_LOG) != 'null' ;