Oracle 数据库链接。检查是否存在或覆盖？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9200235/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle database link. Check for existence or overwrite?
提问by Oliver Nilsen
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:
我正在编写一个以此开头的 SQL 脚本：
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?
我该怎么做才能检查数据库链接是否已经存在？
回答by tbone
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;
回答by Mark J. Bobak
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.
Oracle 无法在 DROP 或 CREATE 之前测试是否存在。（好吧，您可以编写一些 PL/SQL，但是，这可能比它的价值更麻烦。）在 Oracle 脚本中，在脚本中简单地执行 DROP 和 CREATE 是非常标准的。如果 DROP 出错，就这样吧。它不会影响脚本的执行。
-Mark
-标记
