Lobs Over Database Links

The Problem

Your application is on the application server. Your data is on the database server. Some of your data is stored in a CLOB or BLOB field. Naturally you try a select, but Oracle complains. You find out that you are not allowed to select LOB fields over a database link. How do you get at the data?

The Solution

It seems that there is an exception to the rule. If you are inserting a row into a table, Oracle will let you copy the data across a database link.

The solution is so simple I wish I had thought of it. You create a local table with the appropriate CLOB or BLOB fields that you need. You can then insert the data you need into the local table when you need it. Now you can select it.

You could call a function like this:

procedure get_the_blob (p_blob_id in number) as
begin
  -- clear out the local table
  delete from local_blob_table where blob_id = p_blob_id;
  commit;
  
  -- copy from the remote table
  insert into local_blob_table
    ( blob_id, blob_data )
  select p_blob_id, blob_data 
	from remote_blob_table@my.remote.database
	where blob_id = p_blob_id;
  commit;
end;

That's it. You can now write a select statement to join the local table to any remote tables you need. It may be simpler if you make the local table a copy of the remote table and copy over a whole row at a time. You'll then have fewer joins to make in your select statement.