Is it possible to read a CLOB from a remote Oracle database?
This answer on a question on SO says
... you can read a LONG from a remote database, but you can't read a CLOB
I did not find anything about this on the internet, is it true? Any documentation or citings for开发者_JAVA技巧 this will be helpful.
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.
From the errors manual:
Cause: A remote LOB column cannot be referenced.
Action: Remove references to LOBs in remote tables.
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 view for example.
@Peter Ilfrich: Doesn't that throw an exception when trying to access any clobs over 4000 bytes?
This is a little more convaluted, but it means you can safely pull back small clobs (< 4000) over a dblink.
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 possible with this PL/SQL package:
https://github.com/HowdPrescott/Lob_Over_DBLink
If both DB schemes are in the same Oracle instance, you can use the following workaround:
select (select <columnName> from dual) <columnName> from <table>@<link>
This will return you the same as if you would access a local LOB column.
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.
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.
You can create a Materialized View from remote table and then use from that for your needs
https://webcache.googleusercontent.com/search?q=cache:LG2eG1gThV8J:https://community.oracle.com/thread/471047%3Fstart%3D0%26tstart%3D0+&cd=2&hl=en&ct=clnk&gl=ir
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
Works for clobs > 4000.
Tested only for CLOB!
精彩评论