学步园

SQL> create table ZWC.TableWithClob (myid number, myclob clob);

Table created.

SQL> insert into ZWC.TableWithClob (myid, myclob) values (1, 'test1');

1 row created.

SQL> insert into ZWC.TableWithClob (myid, myclob) values (2, 'test2');

1 row created.

SQL> insert into ZWC.TableWithClob (myid, myclob) values (3, 'test3');

1 row created.

SQL> commit;

Commit complete.

then we need to create a function for converting CLOB to multiple varchar2 and hide it behind view. Please note I tried to use collection here, but you cannot query collection across db-link, you will
be facing error ORA-22804: remote operations not permitted on object tables or user-defined type columns thus I convert the clob to basically multi-row view (number of records is variable and depends on size of LOB). 

SQL> CREATE TYPE object_row_type AS OBJECT (
  2   MYORDER            NUMBER,
  3   MYID               NUMBER,
  4   MYCLOB_AS_VARCHAR  VARCHAR2(4000));
  5  /

Type created.

SQL> select tname from tab;

TNAME
------------------------------
TABLEWITHCLOB
EMP

SQL> CREATE TYPE object_table_type AS TABLE OF object_row_type;
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION CONVERT_CLOB_TO_VARCHAR RETURN object_table_type
  2    PIPELINED IS
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4  
  5    v_clob_length  number;
  6    v_loops        number;
  7    v_varchar_size number := 4000;
  8  
  9  BEGIN
 10  
 11    FOR cur IN (SELECT myid, myclob from TableWithClob) LOOP
 12      v_clob_length := dbms_lob.getlength(cur.MyClob);
 13      v_loops       := trunc(v_clob_length / v_varchar_size) +
 14                       sign(mod(v_clob_length, v_varchar_size)) - 1;
 15    
 16      FOR i IN 0 .. v_loops LOOP
 17        PIPE ROW(object_row_type(i + 1,
 18                                 cur.myid,
 19                                 dbms_lob.substr(cur.MyClob,
 20                                                 v_varchar_size,
 21                                                 v_varchar_size * i + 1)));
 22      END LOOP;
 23    
 24    END LOOP;
 25    COMMIT;
 26    RETURN;
 27  END CONVERT_CLOB_TO_VARCHAR;
 28  /

Function created.

SQL> 

SQL> CREATE VIEW myRemoteData as
  2  SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;

View created.

LOCAL SERVER

SQL> create database link LOB_DBLINK
  2    connect to dsg identified by dsg
  3    using '(DESCRIPTION =
  4      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
  5      (CONNECT_DATA =
  6        (SERVER = DEDICATED)
  7        (SERVICE_NAME = zwc)
  8      )
  9    )';

Database link created.

SQL> select * from zwc.tablewithclob@LOB_DBLINK;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables



no rows selected

SQL> select count(*) from zwc.tablewithclob@LOB_DBLINK;

  COUNT(*)
----------
         3

SQL> 

SQL> CREATE OR REPLACE FUNCTION F_VARCHAR_TO_CLOB(input_table_of_varchar myTableType)
  2    RETURN CLOB IS
  3    PRAGMA AUTONOMOUS_TRANSACTION;
  4    v_clob clob;
  5  BEGIN
  6    FOR i IN 1 .. input_table_of_varchar.COUNT LOOP
  7      v_clob := v_clob || input_table_of_varchar(i);
  8    END LOOP;
  9    RETURN v_clob;
 10  END F_VARCHAR_TO_CLOB;
 11  /

Function created.

SQL> 

SQL> CREATE OR REPLACE VIEW myRemoteData as
  2  SELECT a.myid,
  3         F_VARCHAR_TO_CLOB( CAST(MULTISET(SELECT b.MYCLOB_AS_VARCHAR
  4                                            FROM ZWC.myRemoteData@lob_dblink b
  5                                           WHERE a.MYID = b.MYID
  6                                        ORDER BY MYORDER   ) as myTableType)
  7                          ) myClob
  8  FROM ZWC.TABLEWITHCLOB@lob_dblink a;

View created.

SQL> select * from myRemoteData;

      MYID
----------
MYCLOB
--------------------------------------------------------------------------------
         1
test1

         2
test2

         3
test3