Oracle 数据库链接。检查是否存在或覆盖？

发布于 2025-01-03 13:48:34 字数 364 浏览 0 评论 0原文

我需要在创建数据库链接之前检查数据库链接是否已存在。我怎样才能做到这一点？

我正在编写一个以此开头的 SQL 脚本：

DROP DATABASE LINK mydblink

然后创建一个：

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

如果数据库链接不存在，我当然会在第一步中收到错误。如果我省略第一步并继续创建数据库链接，我将再次收到错误消息，指出它已存在同名。

我可以做什么来检查数据库链接是否已经存在？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

身边 2025-01-10 13:48:34

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

例如（未经测试）：

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;

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;

回复收藏 0 原文