在Oracle分布式数据库中处理LOB的最佳方法 [英] Best way to handle LOBs in Oracle distributed databases

问题描述

如果创建Oracle dblink，则无法直接访问目标表中的LOB列.

If you create an Oracle dblink you cannot directly access LOB columns in the target tables.

例如，您使用以下命令创建一个dblink:

For instance, you create a dblink with:

create database link TEST_LINK 
  connect to TARGETUSER IDENTIFIED BY password using 'DATABASESID';

之后，您可以执行以下操作:

After this you can do stuff like:

select column_a, column_b 
from data_user.sample_table@TEST_LINK

除非该列是LOB，否则您将收到错误:

Except if the column is a LOB, then you get the error:

ORA-22992: cannot use LOB locators selected from remote tables

这是已记录的限制.

同一页上建议您将值提取到本地表中，但这很杂乱:

The same page suggests you fetch the values into a local table, but that is... kind of messy:

CREATE TABLE tmp_hello 
AS SELECT column_a 
from data_user.sample_table@TEST_LINK

还有其他想法吗?

推荐答案

是的，这很混乱，但是我想不出一种避免它的方法.
通过将临时表创建放入存储过程中(并使用立即执行"来创建表)，可以对客户端隐藏一些麻烦.
您需要注意的一件事是保留在临时表上(如果有一半时间在会话进行过程中失败，而您没有时间清理它)-您可以安排一个oracle作业来定期运行并删除所有遗留的表.

Yeah, it is messy, I can't think of a way to avoid it though.
You could hide some of the messiness from the client by putting the temporary table creation in a stored procedure (and using "execute immediate" to create they table)
One thing you will need to watch out for is left over temporary tables (should something fail half way through a session, before you have had time to clean it up) - you could schedule an oracle job to periodically run and remove any left over tables.

这篇关于在Oracle分布式数据库中处理LOB的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！