title: Best way to handle LOBs in Oracle distributed databases
body: <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>