Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle SQL - clob compare

Posted on 2014-02-19
2
381 Views
Last Modified: 2014-02-20
Hi

I am executing the following to compare a clog that is within 2 different tables.

select dbms_lob.compare(a.MESSAGE,b.MESSAGE)
  from CMS_MAIL a,CMR_MAIL b
where b.ORDERNO = '4'
   and a.JOBNO = '6';


I need the query to output if the same = 0 if different = 1

Not sure how to do this.

Thanks

Mike
0
Comment
Question by:mikeysmailbox1
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39871735
Per the docs, it returns 0 if successful so try this:

case when  dbms_lob.compare(a.MESSAGE,b.MESSAGE) = 0 then 0 else 1 end
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39871741
According to the documentation here -> http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_lob.htm#ARPLS66648

It should already return 0 if they are the same, non-zero if different and NULL if one (or both) of the LOBs are invalid.

If you must have 1, then I would add a simple case statement that would evaluate to 1 if it is non-zero.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question