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.
- 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
- 来源于