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);
In releases prior to SQLDeveloper 4.1, however, there was a major omission — a failure to run individual query which outputs result set into a grid (as opposed to running it as a script). Admittedly, there was a technical culprit for that. When a query is executed into a grid, the contract between the server (RDBMS) and client (SQLDeveloper) is somewhat loosely defined. Specifically, the client asks the server to execute the query, then fetches 50 records. The cursor remains open “just in case” if user might want to scroll down beyond the first batch of records. In other words, the end of sql execution is not precisely demarcated. How does this affect SQLDeveloper automatic DB link feature? Well, SQLDeveloper needs to know when to delete temporary database links, and the only solution is attaching listener to the event of grid closure. Hence, after you you set dblinks on, and execute a query into a grid, but before closing the grid, you’ll witness database link under the links node in the navigator. Please be aware that deleting temporary links from the client is not bulletproof; for example, server process going down with ORA-600 and taking user session with it, would result in leftover database links. If your database development work revolves around environment with strict security requirements, it is advisable to reevaluate using this feature.
In releases prior to SQLDeveloper 4.1 there were also smaller snags. For example, PL/SQL anonymous blocks execution was not accompanied by database link creation. Also, database link names with identifiers separated by dots were not recognized. Regarding the second issue, please keep in mind that SQLDeveloper’s syntax for connection names is less restricted than that of database links. For example, something like 126.96.36.199_SCOTT is legitimate connection alias, but is not a database link.
With these bug fixes behind, let’s venture into applications. How about copying [remote] database schema? In introductory computer science class students learn that there are two kinds of objects in programming: data and code. Here is the command which copies the data:
set autodblink on; BEGIN FOR rec IN (SELECT TABLE_NAME FROM user_tables@gbr30060_DB11GR24_hr) LOOP begin DBMS_OUTPUT.PUT_LINE('Processing Table: ' || rec.TABLE_NAME ); execute immediate 'create table ' || rec.TABLE_NAME || ' as select * from ' || rec.TABLE_NAME || '@gbr30060_DB11GR24_hr'; exception when others then DBMS_OUTPUT.PUT_LINE('Skipped Table: ' || rec.TABLE_NAME ||' -&amp;gt; -ERROR- '||SQLERRM); end; END LOOP; END; /
and the code
BEGIN FOR rec IN (SELECT name, type, LISTAGG(text, 'chr(13)') WITHIN GROUP (ORDER BY line) AS code FROM user_source@gbr30060_DB11GR24_hr GROUP BY name, type ) LOOP begin DBMS_OUTPUT.PUT_LINE('Processing Object: ' || rec.NAME ); execute immediate 'create ' || rec.code; exception when others then DBMS_OUTPUT.PUT_LINE('Skipped : ' || rec.type || '' || rec.name ||' --ERROR-- '||SQLERRM); end; END LOOP; END; /