Oracle: использование ссылки базы данных в хранимой процедуре: таблица или представление не существует

В настоящее время проблема связана с тем, что я не могу ссылаться на таблицу в связанной базе данных в хранимой процедуре. Я получаю сообщение об ошибке:

ORA-00942: таблица или представление не существует

Ниже приведены шаги, которые я предпринял на хост-машине (работает оракул 10g), чтобы настроить ссылку базы данных на удаленную базу данных (работает оракул 11g). Шаги являются точными, но некоторые некоторые имена были изменены, хотя они были согласованы.

Обновите 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 [email protected]_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 [email protected]_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
```

Я пробовал много вещей, чтобы попытаться разобраться с этой проблемой, в том числе:

При создании ссылки на базу данных, не используя кавычки вокруг имени пользователя и пароля. Ссылка создает штраф, но выбор из нее дает мне эту ошибку:
```
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TWS_LINK
```
Пробовал различные комбинации имени пользователя и пароля в верхнем/нижнем регистре. Получена такая же ошибка, как и 1.
Пробовал одинарные кавычки вместо двойных кавычек вокруг имени пользователя и пароля. Получена эта ошибка:
```
ERROR at line 1:
ORA-00987: missing or invalid username(s)
```

Доказано, что у меня есть полный доступ к удаленному db, подключившись к нему с помощью sqlplus:

[oracle]$ sqlplus remote_user/[email protected]_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.

Ответ 1

ОК, так что мне удалось получить эту работу.

Оказывается, при создании ссылки на базу данных двойные кавычки вокруг полей имени пользователя и пароля вызывали проблему. Подводя итог:

Если они присутствовали и ссылка была создана так:

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

Удаленная база данных может запрашиваться через sql
Хранимая процедура не может скомпилироваться, получив ошибку ORA-942
Поскольку процедура не может быть скомпилирована, она не может быть выполнена

Если двойных кавычек нет:

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

Удаленная база данных не может запрашиваться через sql, получив неверную ошибку пароля (подробно в вопросе)
Хранимая процедура может скомпилироваться без ошибок.
Хранимая процедура выполняется как ожидалось, извлекает данные из ссылки на базу данных и отображает ее.

Таким образом, даже если удаленная база данных не может быть запрошена через sql, получив неверную ошибку пароля, процедура, использующая эту же информацию о соединении, компилируется и выполняется нормально.

Я уверен, вы согласитесь, это любопытное состояние событий, и я искренне наткнулся на то, чтобы заставить его работать в моем сценарии. Я не совсем уверен, что буду называть это решением, так как есть много нерешенных вопросов.

Надеюсь, если кто-то придет сюда через Google, они найдут этот ответ полезным и, по крайней мере, запустит свой код.

ГХ.

Ответ 2

Я столкнулся с той же проблемой на 11gR2, и я благодарен этому форуму за то, что помог мне найти проблему. Способ заставить db-ссылку работать как в SQL, так и в процедуре заключается в следующем синтаксисе (заключить только пароль в двойные кавычки).

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

Ответ 3

Я думаю, что вижу здесь проблему. Является ли пользователь, выполняющий хранимую процедуру тем же пользователем, который создал хранимую процедуру?

Вы сказали: "Создайте ссылку на базу данных, как пользователь, который позже выполнит хранимую процедуру".

Если пользователь, создающий ссылку на базу данных, отличается от пользователя, создавшего хранимую процедуру, это может быть вашей проблемой.

Попробуйте создать ссылку на хранимую процедуру и базу данных как один и тот же пользователь или создать ссылку на общедоступную базу данных.

Затем, поскольку Oracle по умолчанию является определенными правами, вы можете заставить кого-либо выполнить хранимую процедуру (при условии, что им предоставлена привилегия выполнения процедуры).