Oracle database link. Check for existence or overwrite?

Question

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?

score 9 · Accepted Answer · edited Sep 15 '20 at 12:13

9

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;

edited Sep 15 '20 at 12:13

Barbaros Özhan

59,113
10
31
55

answered Feb 08 '12 at 19:51

tbone

15,107
3
33
40

I have nothing returned from this query even though I just created a link. What does count(1) do? – Oliver Nilsen Feb 08 '12 at 19:58
1

@OliverNilsen count(1) is really same as count(*). I could have said count('Oliver') or count('tbone'). – tbone Feb 08 '12 at 20:09
@Gaius in some situations the user running this script might not be the owner of the link (eg, owner might be PUBLIC). But yes, often more than 1 way to skin a cat – tbone Feb 08 '12 at 20:12
Can you provide a sample code how I could check if the link exists, if not then create one, or else continue with the rest of the script. I am new to PL/SQL. – Oliver Nilsen Feb 08 '12 at 20:16
THX for the code. It turns out that you cannot use, DDL statements inside a BEGIN block. Ah bummer – Oliver Nilsen Feb 09 '12 at 10:09
Is there a difference between `SELECT 1` and `SELECT count(1)`? – sampathsris Nov 11 '14 at 09:35

score 0 · Answer 2 · answered Feb 08 '12 at 20:02

0

Oracle has no way to test for existence before a DROP or CREATE. (Well, ok, you could write some PL/SQL, but, that's probably more trouble than it's worth.) In Oracle scripting, it's pretty standard to simply do both the DROP and the CREATE in a script. If the DROP errors out, so be it. It won't affect execution of the script.

-Mark

answered Feb 08 '12 at 20:02

Mark J. Bobak

13,720
6
39
67

I tried running it as a script. When it hits DROP DATABASE LINK statement it fails, and the rest of the script is not executed. Can you give me a hint what I could do in PL/SQL? – Oliver Nilsen Feb 08 '12 at 20:13
Does your script have an 'whenever sqlerror ...' clause? Cause if you do, that's causing the exit. If you just put a bunch of DROP/CREATE in a script and run it in SQL*Plus, it should work. I'll look at updating my answer with PL/SQL example. – Mark J. Bobak Feb 08 '12 at 22:41

Oracle database link. Check for existence or overwrite?

2 Answers2