Oracle

How to handle Oracle BLOB columns via Database Link

Published: Wednesday, 05 October 2016 22:57 | Written by Stefan | Print | Email | Hits: 83599

Oracle 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

Published: Sunday, 26 July 2015 16:04 | Written by Stefan | Print | Email | Hits: 34184

... 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!