ora-22992 通过DBLINK 访问远程CLOB表问题

在本地用select语句访问远程,如果远程表有CLOB字段的话则会有错:ora-22992;
如果真的想看到clob字段的内容的话就得在本地建立一个表,用下面两条语句:
我刚才试验insert into table select * from remote table成功
remote table含有CLOB
总结：在我的环境中成功
（1）create table aaa select * from remote table
（2）insert into table select * from remote table

不过网上也有以下说法,虽然有错,不过也是一种方法,

通过临时表从DBLink中获取Blob对象2006-12-05 20:37做系统集成时，通过Database Link共享数据是不错的选择。不过真正使用DBLink时却碰到一个不小的问题：从远程数据库上查询Blob字段时总返回ORA-22992错误，如下：

select blobcolumn from remoteTable@dl_remote;

ORA-22992:无法使用从远程表选择的 LOB 定位器

查找了一下解决方法，有人提出了采用物化视图可以解决这个问题。物化视图唯一的缺陷在于同步机制的问题，如果同步时间设置过短，则占用大量的系统资源，给服务器带来极大的压力；如果设置时间过长，前台用户不可接受。

后来还是AskTom给出了极好的解决方案：使用全局临时表。

SQL>createglobaltemporary table foo

2(

3X BLOB

4)

5on commit delete rows;

Table created

SQL>insert into foo select blobcolumn from remoteTable@dl_remote where rownum=1;

1 row inserted

SQL>

插入本地临时表之后，在本地的操作就没有任何问题了。

-- 另外一篇

Oracle官方论坛关于DBLink problem ORA-22992的讨论

我做了一下整理，最终那句是最后的答案，相信不用怎么翻译大家都应该能够看懂说些什么，这一点可是搞IT所必须的。

don't know if this is related, but we were also having a problem that was causing the ORA-22992 error, and the solution turned out to be surprisingly simple. A full day of searching the web didn't turn up this answer, but then one of our DBAs accidentally stumbled over something buried in some Oracle documentation that provided the answer.

We have a database table that contains a couple primary key fields (a varchar and an integer), plus a BLOB that holds Word documents. One of our programs needs to be able to connect to a remote Oracle instance and copy Word documents based on certain primary keys.

Our code first attempted to do that like this:

insert into [local Word doc table] ([key column1], [key column 2], [blob column])
values ('[key 1 literal]', [key 2 literal],
(select [blob column] from [Word doc table]@[remote instance]
where [keys = remote keys])

Attempting to execute that was giving us the "cannot use LOB locators selected from remote tables" error.

The documentation that our DBA turned up included a bunch of SQL examples of using remote BLOBs which he thought would be helpful. But what provided the solution was the sentence following the SQL examples: "In statements structured like the preceding examples, only standalone LOB columns are allowed in the select list".

I took that to mean that if you're going to access a BLOB on a remote database, then that BLOB column has to be the ONLY column you're referencing. So I broke our program's SQL up into this:

insert into [local Word doc table] ([key 1 col], [key 2 col], [blob col]) values
('[key 1]', [key 2], NULL)

update [local Word doc table] set [blob col] =
(select [blob col] from [Word doc table]@[remote instance]
where [keys = remote keys])
where [keys = local keys]

I was amazed to find that the above works like a charm. We've got a 100 meg Word document going from one Oracle instance to the other with no problem.

Since doing a Google search on "cannot use LOB locators selected from remote tables" turns this page up near the top of its list of links, I'm hoping that by posting this I can save another programmer somewhere the two or three days of banging your head against the screen that I just went though.