Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle SQL - clob compare

Posted on 2014-02-19
2
Medium Priority
?
396 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
[X]
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
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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 35

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

610 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