Oracle

Oracle – reading CLOB’s over database links



Reading CLOB’s within a cursor over databases links will return “ORA-22992: cannot use LOB locators selected from remote tables”. An example of this might be the following (assuming the field “content” is of datatype CLOB):

BEGIN
  FOR i IN (SELECT * FROM table_with_clob_fields@dblink) LOOP
    UPDATE table_with_clob_fields -- local copy           
       SET content = i.content,
           other_fields = i.other_fields
     WHERE primary_key = i.primary_key;
  END LOOP;
END;
/
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 2
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

CLOB’s cannot be read over database links unless it is the only piece of information being read. So by re-writing this to

BEGIN
  FOR i IN (SELECT primary_key, other_fields FROM table_with_clob_fields@dblink) LOOP
    UPDATE table_with_clob_fields -- local copy
       SET content = (SELECT content FROM FROM table_with_clob_fields@dblink WHERE primary_key = i.primary_key),
           other_fields = i.other_fields
     WHERE primary_key = i.primary_key;
  END LOOP;
END;
/

This will work as it is reading the CLOB content as a single item across the database link.

Tags :, , ,