SQL 在 Oracle 分布式数据库中处理 LOB 的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to handle LOBs in Oracle distributed databases
提问by morais
If you create an Oracle dblink you cannot directly access LOB columns in the target tables.
如果创建 Oracle dblink，则无法直接访问目标表中的 LOB 列。
For instance, you create a dblink with:
例如，您使用以下命令创建 dblink：
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
Except if the column is a LOB, then you get the error:
除非该列是 LOB，否则您会收到错误：
ORA-22992: cannot use LOB locators selected from remote tables
This is a documented restriction.
这是记录在案的限制。
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
Any other ideas?
还有其他想法吗？
采纳答案by hamishmcn
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 作业定期运行并删除任何剩余的表。
回答by user2015502
The best solution by using a query as below, where column_b is a BLOB:
使用如下查询的最佳解决方案，其中 column_b 是一个 BLOB：
SELECT (select column_b from sample_table@TEST_LINK) AS column_b FROM DUAL
回答by Luis HGO
You could use materalized views to handle all the "cache" management. It′s not perfect but works in most cases :)
您可以使用物化视图来处理所有“缓存”管理。它并不完美，但在大多数情况下都有效:)
回答by PT_STAR
For query data, the solution of user2015502 is the smartest. If you want to insert or update LOB's ATthe remote database (insert into xxx@yyy ...) you can easily use dynamic SQL for that. See my solution here:
对于查询数据，user2015502的解决方案是最聪明的。如果你想插入或更新LOB的AT远程数据库（INSERT INTO XXX @ YYY ......），你可以轻松地使用动态SQL这一点。在此处查看我的解决方案：
回答by hamishmcn
Do you have a specific scenario in mind? For example, if the LOB holds files, and you are on a company intranet, perhaps you can write a stored procedure to extract the files to a known directory on the network and access them from there.
你有什么特定的场景吗？例如，如果 LOB 保存文件，并且您在公司内部网上，也许您可以编写一个存储过程将文件提取到网络上的已知目录并从那里访问它们。
回答by morais
In this specific case can the only way the two systems can communicate is using the dblink.
在这种特定情况下，两个系统可以进行通信的唯一方式是使用 dblink。
Also, the table solution is not that terrible, it's just messy to have to "cache" the data on my side of the dblink.
此外，表解决方案并没有那么糟糕，必须在我这边的 dblink 上“缓存”数据很麻烦。
