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

Oracle: Using a database link in a stored procedure : table or view does not exist

提问by Clarkey

I currently have an issue whereby I cannot reference a table in a linked database within a stored procedure. I get the error message:

我目前有一个问题，即我无法在存储过程中引用链接数据库中的表。我收到错误消息：

ORA-00942: table or view does not exist

ORA-00942: 表或视图不存在

Here are the steps I took on the host machine (running oracle 10g) to set up the database link to the remote database (running oracle 11g). The steps are accurate, but some some names have been changed, though they have been kept consistent.

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

1. Update tnsnames.ora, adding a new entry:

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

2. Create database link, as the user who will later be creating and executing the stored procedure:

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

3. Prove database link is working by selecting from it:

   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
   

4. Create stored procedure that depends on working database link:

   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;
   

5. Explode own head after staring at the following error message for over an entire working day:

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

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

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

2. 创建数据库链接，作为稍后将创建和执行存储过程的用户：

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

3. 通过从中选择来证明数据库链接正在工作：

   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
   

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

   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;
   

5. 盯着下面的错误信息看了一整天后，自己的脑袋炸了：

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

I have tried many things to try to sort this issue out, including:

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

1. When creating the database link, not using quotes around the username and password. Link creates fine, but selecting from it gives me this error:

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

2. Tried various combinations of username and password in upper/lowercase. Received same error as 1.

3. Tried single quotes instead of double quotes around username and password. Recieved this error:

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

4. Proved I have full access to the remote db by connecting into it with 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> 
   

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> 
   

I'm not sure what to do next. The possible next step is to start looking at issues on the remote database, and perhaps see if other databases can connect to it. Another would be to look at incompatibilities going from host 10g to remote 11g.

我不知道接下来要做什么。下一步可能是开始查看远程数据库上的问题，也许看看其他数据库是否可以连接到它。另一种方法是查看从主机 10g 到远程 11g 的不兼容性。

采纳答案by Clarkey

OK so I was able to get this working, of sorts.

好的，所以我能够让这个工作，各种各样。

It turns out that when creating the database link, the double quotes around the username and password fields were causing the issue. To summarise:

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

If they were present, and the link created as so:

如果它们存在，并且链接是这样创建的：

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

1. The remote database couldbe queried via sql
2. The stored procedure could notbe compiled, recieving the ORA-942 error
3. As the procedure could not be compiled, it could not be executed

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

When the double quotes are not present:

当双引号不存在时：

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

1. The remote database could notbe queried via sql, recieving an invalid password error (detailed in the question)
2. The stored procedure couldbe compiled with no errors.
3. The stored procedure executes as expected, retrieving data from across the database link, and displaying it.

1. 无法通过sql查询远程数据库，收到无效密码错误（在问题中有详细说明）
2. 可以无错误地编译存储过程。
3. 存储过程按预期执行，从数据库链接中检索数据并显示它。

So, even though the remote database cannot be querued via sql, recieving an invalid password error, the procedure that uses this same connection information compiles and executes normally.

因此，即使远程数据库无法通过 sql 查询，收到无效密码错误，使用相同连接信息的过程也能正常编译和执行。

I'm sure you'll agree, this is a curious state of events, and I genuinely stumbled across making it work in my scenario. I'm not quite sure I would call it a solution, as there are plenty of unanswered questions.

我相信你会同意，这是一个奇怪的事件状态，我真的偶然发现让它在我的场景中工作。我不太确定我会把它称为解决方案，因为有很多悬而未决的问题。

Hopefully if someone comes here via google, they'll find this answer useful, and at least get their code running.

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

GC.

GC。

回答by Sanjog

I faced the same issue on 11gR2, and I'm thankful to this forum for helping me find the problem. The way to make the db link work in both SQL and procedure is to follow the below syntax (enclose only the password within double quotes).

我在 11gR2 上遇到了同样的问题，我很感谢这个论坛帮助我找到了问题。使数据库链接在 SQL 和过程中都能工作的方法是遵循以下语法（仅将密码括在双引号内）。

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

回答by Mark J. Bobak

I think I see a problem here. Is the user who is executing the stored procedure the same user who created the stored procedure?

我想我在这里看到了一个问题。执行存储过程的用户是否与创建存储过程的用户相同？

You said, "Create database link, as the user who will later be executingthe stored procedure".

你说，“创建数据库链接，作为稍后将执行存储过程的用户”。

If the user creating the database link is different from the user creating the stored procedure, that may be your problem.

如果创建数据库链接的用户与创建存储过程的用户不同，那可能是您的问题。

Try creating the stored procedure and database link as the same user, or creating a public database link.

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

Then, since Oracle default is definer rights, you can have anyone execute the stored procedure (assuming they have been granted execute privilege on the procedure).

然后，由于 Oracle 默认是定义者权限，您可以让任何人执行存储过程（假设他们已被授予对该过程的执行权限）。