[adsense id=”0514458240″ width=”468″ height=”60″]
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.