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

Question

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

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.

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
   

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> 
   

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.

Answer 1

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 could be queried via sql
2. The stored procedure could not be compiled, recieving the ORA-942 error
3. As the procedure could not be compiled, it could not be executed

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 not be queried via sql, recieving an invalid password error (detailed in the question)
2. The stored procedure could be compiled with no errors.
3. The stored procedure executes as expected, retrieving data from across the database link, and displaying it.

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.

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.

Answer 2

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).

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

Answer 3

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 executing the 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).