I have a problem with the following scenario:
I have an Oracle 8i database with a synonym to Oracle 8.0.5 tables. This Oracle 8 table contains a column of datatype CLOB.
When I try to bring this column over to 8i (using select myclobcol into v_myclobvar from myOracle8Synonym), I get this error:
ORA-22992: cannot use LOB locators selected from remote tables
I have search everywhere for a solution, but cannot find one.
I know that it is specifically an Oracle 8 issue.
Looking forward to your solutions :)
Distributed LOBs are not supported. Therefore, you cannot use a remote locator in SELECT or WHERE clauses of queries or in functions of the DBMS_LOB package.
However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:
UPDATE t SET lobcol = (SELECT lobcol FROM able1@remote_site);
only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB APIs on LOBs are not supported. For example, the following statement is supported:
CREATE TABLE AS SELECT clob_col FROM tab@dbs2;
The "create table" methid works, thanks very much - you can also bring in a primary key field to help with identification.
Again, thanks :)