In Oracle, how to select LOBs from remote database without copying to local tables?

Question

I am writing some scripts for data migration and the insert part (insert into ... select from) works great so far. However the verification statements gives the following error:

22992. 00000 -  "cannot use LOB locators selected from remote tables"

Which is caused by selecting a CLOB column from a remote DB:

 select sum(owa_opt_lock.checksum(clobCol)) into x_var from TableA@OLD_DB;   
 select sum(owa_opt_lock.checksum(clobCol)) into y_var from TableA;
 --Compare the check sums to verify if migration succeed

After researching it seems like the standard solution to this error is to copy the data to a local table beforehand. But this is defeating the purpose of verification since corruption may occur while copying to local table!

Another solution from the web I found is to do the following:

SELECT (select column_b from sample_table@TEST_LINK) AS column_b FROM DUAL

which is said to be work on earlier versions of Oracle but doesn't work in 12C.

What should I do?

Answer 1

You don't need to pull the CLOB across the dblink.
Just call the owa_opt_lock.checksum function on the remote DB as well.
That way only the resulting number is passed back across.

 select sum(owa_opt_lock.checksum@OLD_DB(clobCol)) into x_var from TableA@OLD_DB;

---

If you really needed to pull the CLOB across the dblink, you could use the lob_over_dblink.get_clob function.
https://github.com/HowdPrescott/Lob_Over_DBLink

 select sum(owa_opt_lock.checksum(
            lob_over_dblink.get_clob('OLD_DB', 'TableA', 'clobCol', rowid)
        )) into x_var from TableA@OLD_DB;

---

For completeness I'll mention that the owa_opt_lock.checksum throws the ORA-22835 exception for CLOBs longer than 4000 bytes.
If you wanted use it to pull across individual CLOB checksums safely, you could do it like this:

 select owa_opt_lock.checksum@OLD_DB(clobCol)
   from TableA@OLD_DB
  where dbms_lob.getlength@OLD_DB((select clobCol from dual@OLD_DB)) <= 4000;