Menu
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
290
You can use a regular expression for that. Try this: declare Data1 Clob; begin Data1 := 'ab c d'; -- replace all the whitespace in Data1 with null if dbms_lob. getlength(regexp_replace(Data1,'[[:space:]]'))=0 then dbms_output.
an empty_clob() is a value - it is not null, it is just "empty", no data in it yet. The empty_clob() is not NULL, it is an empty clob of length zero.
Similarly, to check for empty CLOB , change the variable type to clob and replace the empty_blob() function with empty_clob() function in the above PL/SQL code.
Here is an example of how to use the Oracle IS NOT NULL condition in a SELECT statement: SELECT * FROM customers WHERE customer_name IS NOT NULL; This Oracle IS NOT NULL example will return all records from the customers table where the customer_name does not contain a null value.
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.
82
SELECT * FROM SIEBEL.S_AUDIT_ITEM WHERE length(AUDIT_LOG) > 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
