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
    sql database oracle-database stored-procedures dblink
    Edit
    Report