Oracle search clob field

Experts,

I am trying to search text in a couple clob fields.  I saw where you can use dbms_lob.substr but not sure. Here is my query

	SELECT *
		FROM EMAIL_REPORTING ER
		WHERE  ER.MESSAGE_TYPE = 0 -- 'Inbound' 
	            AND NOT(
                    ER.TEXT_BODY in ('Ticket Notification%')
                     OR ER.SUBJECT in ('Your conference ended')
                     OR ER.SUBJECT like ('Undeliverable%')
                     )

Open in new window



 ORA-00932: inconsistent datatypes: expected - got CLOB
Maliki HassaniAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Not sure what you are doing with your 'IN' clauses there.

Why not just use a LIKE?

It will be slow but it should work.

If you have a LOT of data and the CLOB's are large, I might look at Oracle Text and use a CONTAINS query.

If you can provide more information, we can provide better possibilities.
0
 
Maliki HassaniAuthor Commented:
Correction it should have been a like statement.
0
 
slightwv (䄆 Netminder) Commented:
What is your specific version or Oracle (all 4 numbers please)?
Which columns are CLOBs?
0
 
Maliki HassaniAuthor Commented:
So it seems that I fixed the error by using the like.  Since this is a one time run there isn't a further need for a fix.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.