使用DBLINK访问LOB类型表
来源:互联网 发布:江苏省普通发票软件 编辑:程序博客网 时间:2024/05/18 19:43
This is a bit twisted solution, but works. Basically you first need to convert LOB (in these example I refer to CLOB, BLOB is similar but you will probably not use varchar2) to multiple varchar2(4000) on remote server – for example CLOB with size 8100 will be converted to 3 varchar2(4000), then read this data across db-links, then combine it back to CLOB on a local server.
REMOTE SERVER
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------------------------------TABLEWITHCLOBEMPSQL> 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 tablesno rows selectedSQL> select count(*) from zwc.tablewithclob@LOB_DBLINK; COUNT(*)---------- 3SQL>
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-------------------------------------------------------------------------------- 1test1 2test2 3test3
0 0
- 使用DBLINK访问LOB类型表
- Oracle ORA-22804】使用DBLINK访问LOB类型表
- ora-20000:用DBLink访问远程LOB字段
- Oracle DBLink 访问Lob 字段 ORA-22992 解决方法
- Oracle通过DBLink操作数据表,报错“ORA-22992:无法使用从远程表选择的lob定位器”
- Oracle通过DBLink操作数据表,报错“ORA-22992:无法使用从远程表选择的lob定位器”
- Oracle通过DBLink操作数据表,报错“ORA-22992:无法使用从远程表选择的lob定位器”
- oracle通过DBLink访问远程数据库的LOB字段报ORA-22992的解决方法
- oracle通过DBLink访问远程数据库的LOB字段报ORA-22992的解决方法
- mysql 使用federated 实现dblink 远程表访问
- mysql 使用federated 引擎实现dblink 远程表访问
- mysql使用federated引擎实现dblink远程表访问
- Oracle 使用DBLink跨服务迁移数据、并解决Lob 字段 ORA-22992 错误
- Oracle使用 ODBC+DBLINK 访问 Mysql
- Oracle两个数据库互相访问,DBLink使用
- Oracle两个数据库互相访问,DBLink使用
- ORACLE 使用DBLINK访问远程数据库
- oracle 大对象(lob)类型使用性能测试
- iOS 面试题 OC 和 C++的区别
- 利用批处理文件实现自删除技术
- 【Java并发编程】之二十一:并发新特性—阻塞队列和阻塞栈(含代码)
- sublime弹出报错信息
- cocos2dx 常用的构建工具
- 使用DBLINK访问LOB类型表
- SQLServer数据库日志清理清除sqlserver2005日志
- 10474 - Where is the Marble?
- 二分查找
- 【转】软件反编译的方法
- Moon Game (凸四边形个数,数学题)
- jquery CheckBox、RadioButton、DropDownList的取值赋值实现代码/服务器控件 gridview 循环
- 奋斗
- awk 中 用“||“ 作为分隔符的方法