I am trying to delete some entries based on 'name'. I have a table t1 with different columns, one of them being name and of type NCLOB.
I have a method to delete these entries in Java, and I am using a named query. My question is, how can I solve the NCLOB issue so I'll be able to remove the data. I don't want to change the column type.
The name query looks like this:
"DELETE FROM table1 t1 WHERE t1.name = :name"
Error:
Inconsistent datatypes: expected %s got %s"
How can I solve this?
You cannot apply an equality predicate to a LOB of any kind. Instead, either substring out and cast a nvarchar2 and apply the predicate to it, or use dbms_lob.compare, or use LIKE without a %:
delete from table1 t1 where dbms_lob.compare(t1.name,:name) = 0
or
delete from table1 t1 WHERE CAST(SUBSTR(t1.name,1,2000) AS nvarchar2(2000)) = :name
or
delete from table1 t1 where t1.name like :name
But while this works, it raises the more important question of why you are using a LOB datatype for an identifier column. The fact that you find yourself doing this suggests that a LOB is the incorrect datatype for your needs. The above DMLs will work, but will not perform well. LOBs are meant for unstructured data that should not contain pieces of information that are programmatically meaningful.