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

<p>For instance, you create a dblink with:</p>

<pre><code>create database link TEST_LINK 
  connect to TARGETUSER IDENTIFIED BY password using 'DATABASESID';
</code></pre>

<p>After this you can do stuff like:</p>

<pre><code>select column_a, column_b 
from data_user.sample_table@TEST_LINK
</code></pre>

<p>Except if the column is a LOB, then you get the error:</p>

<pre><code>ORA-22992: cannot use LOB locators selected from remote tables
</code></pre>

<p>This is <a href="http://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl04mng.htm#98328" rel="noreferrer">a documented restriction</a>.</p>

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

<pre><code>CREATE TABLE tmp_hello 
AS SELECT column_a 
from data_user.sample_table@TEST_LINK
</code></pre>

<p>Any other ideas?</p>

[{'answer_id': 38142, 'author': 'hamishmcn', 'author_id': 3590, 'author_profile': 'https://Stackoverflow.com/users/3590', 'pm_score': 0, 'selected': False, 'text': '<p>Do you have a specific scenario in mind?\nFor 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.</p>\n'}, {'answer_id': 38146, 'author': 'morais', 'author_id': 2846, 'author_profile': 'https://Stackoverflow.com/users/2846', 'pm_score': 0, 'selected': False, 'text': '<p>In this specific case can the only way the two systems can communicate is using the dblink.</p>\n\n<p>Also, the table solution is not that terrible, it\'s just messy to have to "cache" the data on my side of the dblink.</p>\n'}, {'answer_id': 40159, 'author': 'hamishmcn', 'author_id': 3590, 'author_profile': 'https://Stackoverflow.com/users/3590', 'pm_score': 4, 'selected': True, 'text': '<p>Yeah, it is messy, I can\'t think of a way to avoid it though.<br>\nYou 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)<br>\nOne 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.  </p>\n'}, {'answer_id': 303002, 'author': 'Luis HGO', 'author_id': 6652, 'author_profile': 'https://Stackoverflow.com/users/6652', 'pm_score': 1, 'selected': False, 'text': '<p>You could use materalized views to handle all the "cache" management. It´s not perfect but works in most cases :)</p>\n'}, {'answer_id': 14547653, 'author': 'user2015502', 'author_id': 2015502, 'author_profile': 'https://Stackoverflow.com/users/2015502', 'pm_score': 4, 'selected': False, 'text': '<p>The best solution by using a query as below, where column_b is a BLOB:</p>\n\n<pre><code>SELECT (select column_b from sample_table@TEST_LINK) AS column_b FROM DUAL\n</code></pre>\n'}, {'answer_id': 39894465, 'author': 'PT_STAR', 'author_id': 3619883, 'author_profile': 'https://Stackoverflow.com/users/3619883', 'pm_score': 2, 'selected': False, 'text': '<p>For query data, the solution of user2015502 is the smartest. If you want to insert or update LOB\'s <strong>AT</strong> the remote database (insert into xxx@yyy ...) you can easily use dynamic SQL for that. See <a href="http://www.armbruster-it.org/index.php/14-it/oracle/29-how-to-transfer-oracle-blob-clob-via-database-link" rel="nofollow">my solution here:</a></p>\n'}]
['https://Stackoverflow.com/questions/38074', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2846/']