My Remarks!

the Life, the Universe and almost Everything what's Left

How to handle Oracle BLOB columns via Database Link

BlobFishOracle Developers are often faced with the problem to handle LOB data between several oracle instances via a database link. With plain SQL it is not allowed to select, insert or update a LOB on the remote system. This ends up with a error like this: "ORA-22992 cannot use lob locators selected from remote tables". There are three solutions for this

Solution 1: SQL Subquery

Thanks to user2015502 for this very smart solution in StackOverflow

-- General
SELECT (select <Blob-Column> from <Remote-Table>@<DB-Link> where ...) AS blob_column FROM DUAL;

How to create a relation from Oracle to another physical database

... exactly this was the question of a customer, because he wants to join tables between two Oracle databases. Well, the idea for this solution is based upon the fact, that we can have a foreign key constraint on a view. So let's go ahead:

 

1. We create a view which points to a table/view on the foreign database, using a database link:

create view test_view_dblink as
select * from some_table@external_oracle_database;

2. We create a foreign key constraint on that view

alter view test_view_dblink
add constraint test_view_dblink_fk foreign key (column_name)
references table_in_local_database(column_name) disable;

The "disable" clause at the end of the statement is the important thing because constraints on views must be disabled.

2b. If we would need a primary key we also can define one:

alter view test_view_dblink
add constraint test_view_dblink_pk primary key (column_name) disable;

Last but not least: with this technique, we can create relations to all databases for which we have a oracle connector/gateway (e.g. IBM DB2, ...). Check it out!