Automatic database links in SQLDeveloper
April 7, 2014
People invent really complicated ways to achieve simple tasks such as copying a table from one database to another. With database link (which is arguably RDBMS’s the most under appreciated feature) it is just one SQL command:
create table emp as select * from scott.emp@dblink1;
But who has time to create database links? In SQLDeveloper 4.0 you don’t have to. Assuming that you have the right connection the script
set autodblink on create table emp as select * from scott.emp@connection1; -- assuming there is connection "connection1"
would create temporary db link, then run the statement and, finally, drop the link.
Another example, which benefits from this functionality is comparing a table in two different databases:
set autodblink on (select * from employees@connection1 minus select * from employees) union (select * from employees minus select * from employees@connection1);
Watch out for pitfalls: if connection info doesn’t store a password, you won’t be asked for it — the fix is scheduled for 4.0.2 patch.