Oracle ORA-22804】使用DBLINK访问LOB类型表

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).

1. SQL> CREATE TYPE object_row_type AS OBJECT (
2. 2 MYORDER NUMBER,
3. 3 MYID NUMBER,
4. 4 MYCLOB_AS_VARCHAR VARCHAR2(4000));
5. 5 /
7. Type created.
9. SQL> select tname from tab;
11. TNAME
12. ------------------------------
13. TABLEWITHCLOB
14. EMP
16. SQL> CREATE TYPE object_table_type AS TABLE OF object_row_type;
17. 2 /
19. Type created.

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

1. SQL> CREATE VIEW myRemoteData as
2. 2 SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;
4. View created.

1. SQL> create database link LOB_DBLINK
2. 2 connect to dsg identified by dsg
3. 3 using '(DESCRIPTION =
4. 4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
5. 5 (CONNECT_DATA =
6. 6 (SERVER = DEDICATED)
7. 7 (SERVICE_NAME = zwc)
8. 8 )
9. 9 )';
11. Database link created.
13. SQL> select * from zwc.tablewithclob@LOB_DBLINK;
14. ERROR:
15. ORA-22992: cannot use LOB locators selected from remote tables
19. no rows selected
21. SQL> select count(*) from zwc.tablewithclob@LOB_DBLINK;
23. COUNT(*)
24. ----------
25. 3
27. SQL>

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

1. SQL> CREATE OR REPLACE VIEW myRemoteData as
2. 2 SELECT a.myid,
3. 3 F_VARCHAR_TO_CLOB( CAST(MULTISET(SELECT b.MYCLOB_AS_VARCHAR
4. 4 FROM ZWC.myRemoteData@lob_dblink b
5. 5 WHERE a.MYID = b.MYID
6. 6 ORDER BY MYORDER ) as myTableType)
7. 7 ) myClob
8. 8 FROM ZWC.TABLEWITHCLOB@lob_dblink a;
10. View created.
12. SQL> select * from myRemoteData;
14. MYID
15. ----------
16. MYCLOB
17. --------------------------------------------------------------------------------
18. 1
19. test1
21. 2
22. test2
24. 3
25. test3
26. 来源于

收藏 ，转自：http://blog.csdn.net/staricqxyz/article/details/17511165