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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With