Select Oracle clob field from remote table over dblink I am trying to select a clob field thru flink from a remote table.I am able to see the values in editor result but not able to select thru program or any export.

SQL Error: ORA-22992: cannot use LOB locators selected from remote tables
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


I am able to select local fields and use them in application but failed in these.I can view in the toad  result perfectly when running queries in editor.there also not able to export that particular field..
Remote database:

SQL&gt; create table t1(id number, c1 clob);

Table created.

SQL&gt; insert into t1 values(1, 'HELLO WORLD!');

1 row created.

SQL&gt; insert into t1 values(2, LPAD('A', 4000, 'A'));

1 row created.

SQL&gt; update t1 set c1 = c1 || c1 where id = 2;

1 row updated.

SQL&gt; /

1 row updated.

SQL&gt; /

1 row updated.

SQL&gt; /

1 row updated.

SQL&gt; /

1 row updated.

SQL&gt; commit;

Commit complete.

SQL&gt; select id, length(c1) from t1;

        ID LENGTH(C1)
---------- ----------
         1         12
         2     128000


Local database:

SQL&gt; desc t1@s112
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 C1                                                 CLOB

SQL&gt; select c1 from t1@s112 where id = 1;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables



no rows selected

SQL&gt; select to_char(c1)  from t1@s112 where id = 1;

TO_CHAR(C1)
--------------------------------------------------------------------------------
HELLO WORLD!


The problem is, CHAR types are limited to 4000 bytes, so:

SQL&gt; select to_char(c1) from t1@s112 where id = 2;
select to_char(c1) from t1@s112 where id = 2
*
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
128000, maximum: 4000)
ORA-02063: preceding line from S112


A possible workaround:

SQL&gt; create global temporary table t1_tmp on commit preserve rows as select * from t1@s112 where 1 = 2;

Table created.

SQL&gt; insert into t1_tmp select * from t1@s112 where id = 2;

1 row created.

SQL&gt; commit;

Commit complete.

SQL&gt; select id, length(c1) from t1_tmp;

        ID LENGTH(C1)
---------- ----------
         2     128000


The above is from a 11.2 database.

Starting with 12.2, the limitation of selecting a LOB through a dblink was removed, no workaround needed.