If you create an Oracle dblink you cannot directly access LOB columns in the target tables.
For instance, you create a dblink with:
create database link TEST_LINK
connect to TARGETUSER IDENTIFIED BY password using 'DATABASESID';
After this you can do stuff like:
select column_a, column_b
from data_user.sample_table@TEST_LINK
Except if the column is a LOB, then you get the error:
ORA-22992: cannot use LOB locators selected from remote tables
This is a documented restriction.
The same page suggests you fetch the values into a local table, but that is... kind of messy:
CREATE TABLE tmp_hello
AS SELECT column_a
from data_user.sample_table@TEST_LINK
Any other ideas?
Yeah, it is messy, I can't think of a way to avoid it though.
You 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)
One 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.