If you want try to select CLOB column via database link then you get error ORA-22992

select * from <TABLE_NAME>@<DATABASE LINK>;

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.

This article shows how to avoid this error

Prepare test data

I use two database ORA11G(source database) and ORA12C(destination database) to show the problem and how to fix it

dummy data on ORA11G

ORA11G> CONNECT / AS SYSDBA
CREATE USER test_user IDENTIFIED BY test_user;
GRANT DBA TO test_user;

ORA11G> CONNECT test_user/test_user
CREATE TABLE test_tbl(id1 CLOB);
INSERT INTO test_tbl VALUES('simple clob data');
COMMIT;

dummy data on ORA12C

ORA12C> CONNECT / AS SYSDBA
CREATE USER test_user IDENTIFIED BY test_user;
GRANT DBA TO test_user;

ORA12C> CONNECT test_user/test_user
CREATE DATABASE LINK ora11g CONNECT TO test_user
IDENTIFIED BY test_user USING 'ORA11G';

Now it’s easy to simulate error ORA-22992 on ORA12C. Use always connections as test_user on both databases.

ORA12C> CONNECT test_user/test_user
SELECT * FROM test_tbl@ora11G;

ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

Solutions

• create table as select then scan data

ORA12C> CREATE TABLE test_tbl1
AS
SELECT * FROM test_tbl@ora11g;

SELECT * FROM test_tbl1;

ID1
------------------
simple clob data

• create empty table then insert data

ORA12C> CREATE TABLE test_tbl2 (id1 CLOB);

INSERT INTO test_tbl2 SELECT * FROM test_tbl@ora11G;


SELECT * FROM test_tbl2;

ID1
------------------
simple clob data

• create view on source database. Yet it’s limited to 4000 characters.

ORA11G> CREATE VIEW test_tbl_vw
AS
SELECT dbms_lob.substr(id1, 200, 1) id1
FROM test_tbl;

DESC test_tbl_vw

 Name       Null?    Type
 ---------- -------- ----------------------------
 ID1                 VARCHAR2(4000)

ORA12C> SELECT * FROM test_tbl_vw@ora11g;

ID1
------------------
simple clob data

All the solution except DBMS_LOB.SUBSTR will not work for fixed tables like V$SQLAREA because they use abstract CLOB type and cause ORA-64202

ORA12C> CREATE TABLE sqlarea_dump
AS
SELECT sql_fulltext FROM vsqlarea@ora11g;

ORA-64202: remote temporary or abstract LOB locator is encountered

In this case first dump fixed table on source database and use one of above solutions

ORA11G> CREATE TABLE sqlarea_dump
AS
SELECT sql_fulltext
  FROM v$sqlarea;

ORA12C> CREATE TABLE sqlarea_dump
AS
SELECT * FROM sqlarea_dump@ora11g;

SELECT count(*) FROM sqlarea_dump;

  COUNT(*)
----------
       594

Have a fun 🙂

Tomasz