Oracle：在存储过程中使用数据库链接：表或视图不存在。

Question

我目前遇到的问题是在存储过程中无法引用链接数据库中的表。我收到了以下错误信息：

ORA-00942：表或视图不存在

以下是我在主机上（运行oracle 10g）执行的设置与远程数据库（运行oracle 11g）建立数据库链接的步骤。这些步骤是准确的，但有些名称已更改，尽管它们保持一致。

1. 更新tnsnames.ora，添加一个新条目：

REMOTE_DB =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)
                   (HOST = 10.10.10.10)
                   (QUEUESIZE = 20)
                   (PORT = 1521)
        )
        (CONNECT_DATA =
                   (SERVICE_NAME = remote_service)
        )
    )

以后将创建和执行存储过程的用户身份创建数据库链接：

create database link remote_link
connect to "remote_user"
identified by "remote_pass"
using 'REMOTE_DB';

证明数据库链接正常工作，通过从中进行选择来实现：

select id from remote_table@remote_link;

id
--------------------------------------------------------------------------------
8ac6eb9b-fcc1-4574-8604-c9fd4412b917
c9e7ee51-2314-4002-a684-7817b181267b
cc395a81-56dd-4d68-9bba-fa926dad4fc7
d6b450e0-3f36-411a-ba14-2acc18b9c008

创建一个依赖于工作数据库链接的存储过程：

create or replace
PROCEDURE test_remote_db_link
AS
v_id varchar(50);
BEGIN   
    select id into v_id from remote_table@remote_link where id = 'c9e7ee51-2314-4002-a684-7817b181267b';
    dbms_output.put_line('v_id : ' || v_id);
END test_remote_db_link;

在整整一天的工作时间里盯着下面的错误信息看了一个晚上后，差点让自己崩溃：

Error(10,27): PL/SQL: ORA-00942: table or view does not exist

我尝试过许多方法来解决这个问题，包括：

1. 在创建数据库链接时，不使用引号包围用户名和密码。链接可以成功创建，但从中选择时会出现以下错误：

   ERROR at line 1:
   ORA-01017: invalid username/password; logon denied
   ORA-02063: preceding line from TWS_LINK
   

2. 尝试了各种大小写组合的用户名和密码，收到了与1相同的错误。

3. 尝试使用单引号而非双引号来包围用户名和密码。收到了以下错误：

ERROR at line 1:
ORA-00987: missing or invalid username(s)

4. 通过使用sqlplus连接到远程数据库证明我具有完全访问权限：

[oracle]$ sqlplus remote_user/remote_pass@REMOTE_DB

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 20 22:23:12 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

我不确定接下来该怎么做。可能的下一步是开始查看远程数据库上的问题，也许可以尝试连接其他数据库。另一种方法是查看从主机10g到远程11g的不兼容性。

- Clarkey

---

如果您的选择权限来自角色，则可能会出现相同的症状。您必须对对象进行直接授权才能在PL/SQL程序中使用它。请参见http://www.dba-oracle.com/concepts/roles_security.htm。 - Tim Peters

Answer 1

好的，我成功地将其实现了（某种程度上）。

事实证明，在创建数据库链接时，用户名和密码字段周围的双引号是导致问题的原因。总结一下：

如果它们存在，并且链接被创建为：

create database link remote_link
connect to "remote_user"
identified by "remote_pass"
using 'REMOTE_DB';

1. 可以通过SQL查询远程数据库。
2. 无法编译存储过程，收到ORA-942错误。
3. 由于无法编译该过程，因此无法执行。

当双引号不存在时：

create database link remote_link
connect to remote_user
identified by remote_pass
using 'REMOTE_DB';

1. 通过SQL无法查询远程数据库，会收到密码错误提示（详见问题）
2. 存储过程可以编译通过，没有出现错误。
3. 存储过程按预期执行，从跨数据库链接中检索数据并显示它。

因此，尽管远程数据库无法通过SQL查询且收到密码错误提示，但使用相同连接信息的存储过程可以正常编译和执行。

我相信你会认为这是一个奇怪的事件，并且我真的偶然发现了在我的情况下让它工作。我不确定是否可以称之为解决方案，因为还有很多未解答的问题。

希望如果有人通过谷歌来到这里，他们会发现这个答案有用，并且至少能使他们的代码运行起来。

GC。

- Clarkey

---

还是放不下这个问题...看起来11g的密码是区分大小写的，并且最好用引号括起来。从这篇博客文章中发现的：http://pbarut.blogspot.com/2008/08/db-link-to-oracle-11g.html - Clarkey

Answer 2

在11gR2上，我遇到了同样的问题，感谢这个论坛帮助我找到了问题所在。使得在SQL和过程中都能够使用db链接的方法是按照以下语法（只将密码用双引号括起来）。

create database link remote_link
connect to remote_user
identified by "remote_pass"
using 'REMOTE_DB';

- Sanjog

Answer 3

我觉得这里有个问题。执行存储过程的用户和创建存储过程的用户是同一个用户吗？

你说：“创建数据库链接，使用以后将要执行存储过程的用户”。如果创建数据库链接的用户和创建存储过程的用户不同，那可能就是问题所在。

尝试使用同一用户创建存储过程和数据库链接，或者创建公共数据库链接。

由于Oracle默认使用定义者权限，只要被授予了存储过程的执行权限，任何人都可以执行该存储过程。

- Mark J. Bobak

---

嗨，谢谢回复。我用相同的用户完成了所有操作，即创建了数据库链接并尝试使用相同的用户创建存储过程。公共数据库链接是个好主意，我会尝试使用它来解决问题。 - Clarkey

你是在编译时还是运行时遇到ORA-942错误？（我猜测是编译时？） - Mark J. Bobak

是的，我在编译时遇到ORA-942错误。我尝试使用公共数据库链接，但仍然遇到相同的错误。与正常的数据库链接没有区别 :s - Clarkey