oracle-databaseplsqldatabase-link

Oracle database link. Check for existence or overwrite?


I need to check if a database link already exists before I create one. How can I do that?

I am writing an SQL script that starts with this:

DROP DATABASE LINK mydblink

then I create one:

CREATE DATABASE LINK mydblink
CONNECT TO testuser
IDENTIFIED BY mypswd
USING 'mypersonaldb'

I will of course get an error in the first step if the database link doesn't exists. And if I omit the first step and just go ahead and create a db link, I will again get an error saying that it already exists with the same name.

What can I do in order to check if the the database link already exists?


Solution

  • SELECT COUNT(1)
      FROM dba_objects -- user_objects
     WHERE object_type = 'DATABASE LINK'
       AND object_name = 'ARGUS51P';
    

    For example (untested):

    declare
      l_link_cnt pls_integer := 0;
      l_sql varchar2(32767);
    begin
      -- link creation sql (fill in details of how you want this created)
      l_sql := 'create public database link ...';
    
      select count(1)
      into l_link_cnt
      from dba_objects
      where object_type = 'DATABASE LINK'
      and object_name = 'SOME_LINK';
    
      -- create link if it doesn't exist yet
      if (l_link_cnt = 0) then
        -- create link 
        execute immediate l_sql;
    
      end if;
    
    end;