My Remarks!

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

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/templates/stefans_blog_05/functions.php on line 182

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!

Visits

Today 51 | Week 829 | Month 1374 | All 1169038

Challenges Completed

Vätternsee Club des Cinglés du Mont-Ventoux Styrkeproven 24hVelo Belchen³ Guitar Rehearsal
Warning: unlink(/home/strato/http/premium/rid/76/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM101.php): No such file or directory in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/default.php on line 54
Warning: include(/mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM100.php): failed to open stream: No such file or directory in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/view1.php on line 55 Warning: include(): Failed opening '/home/strato/http/premium/rid/76/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM100.php' for inclusion (include_path='.:/opt/RZphp74/includes') in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/view1.php on line 55
Warning: unlink(/home/strato/http/premium/rid/76/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM100.php): No such file or directory in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/default.php on line 54
Warning: unlink(/home/strato/http/premium/rid/76/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM103.php): No such file or directory in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/default.php on line 54

StackOverflow

profile for PT_STAR at Stack Overflow, Q&A for professional and enthusiast programmers
Warning: include(/mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM97.php): failed to open stream: No such file or directory in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/view1.php on line 55 Warning: include(): Failed opening '/home/strato/http/premium/rid/76/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM97.php' for inclusion (include_path='.:/opt/RZphp74/includes') in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/view1.php on line 55
Warning: unlink(/home/strato/http/premium/rid/76/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/tempBM97.php): No such file or directory in /mnt/web018/d2/82/51207682/htdocs/armbruster-it/joomla33/modules/mod_blank250/tmpl/default.php on line 54