How to update CLOB column from a table to another table using dblink in a trigger?

Question

I have 2 tables in Oracle databse, one of them which I'm trying to update is on another database connected using a dblink. In my tables there are two columns of type CLOB. I am writing a trigger in one database that should update the new CLOB columns' values to the remote table on the remote database. When I want to update the Clob columns I get the following error:

SQL Error: ORA-02055: distributed update operation failed; rollback required ORA-22992: cannot use LOB locators selected from remote tables ORA-04088: error during execution of trigger 'SPECTRA.REPORT_UPDATE' 02055. 00000 - "distributed update operation failed; rollback required" *Cause: a failure during distributed update operation may not have rolled back all effects of the operation. Since some sites may be inconsistent, the transaction must roll back to savepoint or entirely *Action: rollback to a savepoint or rollback transaction and resubmit

How can I update the values of CLOB columns in a remote database?

Answer 1

Have you tried to use DBMS_LOB package ? i.e. functions

  DBMS_LOB.COPY (
  dest_lob    IN OUT NOCOPY BLOB,
  src_lob     IN            BLOB,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

or

DBMS_LOB.COPY ( 
  dest_lob    IN OUT NOCOPY CLOB  CHARACTER SET ANY_CS,
  src_lob     IN            CLOB  CHARACTER SET dest_lob%CHARSET,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#i997674

Or paste your code...