Geek Answers Handbook

Link to the Oracle database. Check availability or overwrite?

I need to check if the database link exists before I create it. How can i do this?

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'

Of course, I will get an error in the first step if the link to the database does not exist. And if I omit the first step and just go ahead and create a link to db, I will again get an error saying that it already exists with the same name.

What can I do to check if a database link exists?

+5
source share
2 answers
select count(1) from dba_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;
+8
source

Oracle DROP CREATE. (, , PL/SQL, , , , .) Oracle DROP CREATE script. DROP , . script.

-

0

More articles:


All Articles