是否可以从远程 Oracle 数据库读取 CLOB?
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3594449/
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
Is it possible to read a CLOB from a remote Oracle database?
提问by Moeb
This answeron a question on SO says
... you can read a LONG from a remote database, but you can't read a CLOB
...您可以从远程数据库读取 LONG,但不能读取 CLOB
I did not find anything about this on the internet, is it true? Any documentation or citings for this will be helpful.
我在互联网上没有找到任何关于此的信息,是真的吗?任何有关此的文档或引文都会有所帮助。
采纳答案by Nick Craver
The answer is correct in a certain context, for simple select statements over a DB link, you'll get this error:
答案在特定上下文中是正确的,对于数据库链接上的简单选择语句,您将收到此错误:
ORA-22992: cannot use LOB locators selected from remote tables.
ORA-22992:无法使用从远程表中选择的 LOB 定位器。
From the errors manual:
从错误手册:
Cause: A remote LOB column cannot be referenced.
Action: Remove references to LOBs in remote tables.
原因:无法引用远程 LOB 列。
行动:删除对远程表中的 LOB 的引用。
I also had trouble finding definitive documentation on this...but we just ran into the same issue in our data warehouse. However, there are several work-arounds available, pulling the data over or creating a viewfor example.
我也很难找到这方面的权威文档……但我们刚刚在我们的数据仓库中遇到了同样的问题。但是,有多种变通方法可用,例如将数据拉出或创建视图。
回答by Howd
@Peter Ilfrich: Doesn't that throw an exception when trying to access any clobs over 4000 bytes?
@Peter Ilfrich:在尝试访问超过 4000 字节的任何 clob 时,这不会引发异常吗?
This is a little more convaluted, but it means you can safely pull back small clobs (< 4000) over a dblink.
这更有价值,但这意味着您可以安全地通过 dblink 拉回小块 (< 4000)。
select dbms_lob.substr@<link>((select <columnName> from dual@<link>), 4000, 1)
from <table>@<link>
where dbms_lob.getlength@<link>((select <columnName> from dual@<link>)) <= 4000;
Reading a CLOB (or a BLOB) over a dblink is possiblewith this PL/SQL package:
https://github.com/HowdPrescott/Lob_Over_DBLink
使用此 PL/SQL 包可以通过dblink 读取 CLOB(或 BLOB):https:
//github.com/HowdPrescott/Lob_Over_DBLink
回答by Jon Heller
Oracle 12.2 finally added support for distributed LOBs. We can now read data types like CLOB and XMLType over database links without any workarounds.
Oracle 12.2 终于添加了对分布式 LOB 的支持。我们现在可以通过数据库链接读取数据类型,如 CLOB 和 XMLType,而无需任何解决方法。
回答by podongfeng
I had the same trouble yesterday. This is My solution: create a romote view on the romote table, when comes the CLOB cols, use to_char(),such as to_char(col2). Then you can select data from the view. It may not be a good solution, but it works.
我昨天也遇到了同样的问题。这是我的解决方案:在 romote 表上创建一个 romote 视图,当 CLOB cols 出现时,使用 to_char(),例如 to_char(col2)。然后您可以从视图中选择数据。这可能不是一个好的解决方案,但它有效。
回答by Peter Ilfrich
Ifboth DB schemes are in the same Oracle instance, you can use the following workaround:
如果两个数据库方案都在同一个 Oracle 实例中,您可以使用以下解决方法:
select (select <columnName> from dual) <columnName> from <table>@<link>
select (select <columnName> from dual) <columnName> from <table>@<link>
This will return you the same as if you would access a local LOB column.
这将返回与访问本地 LOB 列相同的结果。
回答by adramazany
You can create a Materialized View from remote table and then use from that for your needs
您可以从远程表创建物化视图,然后根据您的需要使用它
回答by F.Lazarescu
When your table from dblink it's an Oracle Big Data External table (in my case an external table over a Hive table), you need to create a materialized view over it and use that materialized view with the above mentioned pl/sql package: https://github.com/HowdPrescott/Lob_Over_DBLink
当来自 dblink 的表是 Oracle 大数据外部表(在我的情况下是 Hive 表上的外部表)时,您需要在其上创建一个物化视图,并将该物化视图与上述 pl/sql 包一起使用:https: //github.com/HowdPrescott/Lob_Over_DBLink
Works for clobs > 4000.
适用于 clob > 4000。
Tested only for CLOB!
仅针对 CLOB 进行测试!