0

I've got read-only access to a database containing two schema with tables like this:

schema1.A.unique_id, schema1.A.content
schema2.B.unique_id, schema2.B.content

A.unique_id and B.unique_id will match while A.content and B.content are *LOB columns that should match (wasn't my idea lol). What I'd like to do is compare the contents of the content fields and see how many are equal. However, one is a CLOB and one is a BLOB.

DBMS_LOB.COMPARE() is an obvious helper, however it only compares two *LOBs of the same type (e.g. CLOB vs. CLOB).

In lieu of writing a script to get the content of the fields and compare them in memory, how can I perform this comparison in straight-up PL/SQL? Is there some way I can convert one of the fields on-the-fly so that the types match (again keep in mind I only have read-only access)?

Thanks!

dmn
  • 965
  • 3
  • 13
  • 24
  • 1
    At a minimum, you'd need to know how to convert the `CLOB` into a `BLOB`-- the binary representation could be using a different character set which is why the `convertToCLOB` method takes a character set parameter. You could call `dbms_lob.convertToCLOB` on the BLOB columns (or `convertToBLOB` on the CLOB columns) and then compare but that isn't going to be hugely efficient. – Justin Cave Sep 26 '16 at 18:52
  • @JustinCave Can I use `convertToCLOB()` if I don't have write access? Or do you think comparing it in memory on the local machine with a script will be faster than doing `convertToCLOB()` + `compare()`? – dmn Sep 26 '16 at 19:07
  • 1
    `dbms_lob.convertToCLOB` doesn't write anything. Assuming you can write an anonymous PL/SQL block, you can use local `clob` (or `blob`) variables. – Justin Cave Sep 26 '16 at 19:12
  • Ok I see. I found another answer of yours (http://stackoverflow.com/questions/31902029/oracle-dbms-lob-using-dbms-lob-converttoblob-multiple-times-in-plsql-block) and it looks like I need to store the converted output in a pre-defined `BLOB`. Maybe I should just write a script lol. Thanks for your help :D – dmn Sep 26 '16 at 20:14

0 Answers0