• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

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
0
Maliki Hassani
Asked:
Maliki Hassani
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) 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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now