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
select * from employees)
(select * from employees
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 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;

   FOR rec IN (SELECT TABLE_NAME FROM user_tables@gbr30060_DB11GR24_hr) LOOP
         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 ||' -> -ERROR- '||SQLERRM);

and the code

   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
         DBMS_OUTPUT.PUT_LINE('Processing Object: ' || rec.NAME );
         execute immediate 'create ' || rec.code;
      exception when others then
         DBMS_OUTPUT.PUT_LINE('Skipped : ' || rec.type || '' || ||' --ERROR-- '||SQLERRM);




5 Responses to “Automatic database links in SQLDeveloper”

  1. Doesn’t seem to work for me. I am using version
    Running the following gives ORA-02019: connection description for remote database not found. Also if the connection has any special characters, for example a connection named xe-sys, I get a ORA-00933: SQL command not properly ended error.
    set autodblink on
    create table emptest as select * from hr.employees@xesys;

  2. I have the same issue with the ORA-02019. I’m on

  3. […] Post: Automatic database links in SQLDeveloper […]

  4. mchital Says:

    Thanks a lot, this helped me.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: