Oracle. Триггер с вызовом процедуры. PLS-00201.

[RXL]

Туплю и не понимаю, что делаю не так.
Оракл ругается, что "identifier scheme2.UPDATE_DISC_DATE must be declared".

У меня в одной схеме таблица и триггер, а в другой - процедура, которую нужно выполнить в триггере.

Процедура в схеме scheme2.

Код: (SQL)

CREATE OR REPLACE PROCEDURE scheme2.UPDATE_DISC_DATE(p_custnr IN NUMERIC) IS
        new_disc_date NUMERIC(10);
BEGIN
        new_disc_date := scheme2.GET_DISC_DATE(p_custnr);
       
        DELETE FROM scheme2.PLANNED_DISCONNECT
        WHERE CUSTNR = p_custnr;

        IF NOT new_disc_date IS NULL THEN
                INSERT INTO scheme2.PLANNED_DISCONNECT (CUSTNR, DISC_DATE)
                VALUES (p_custnr, new_disc_date);
        END IF;
END UPDATE_DISC_DATE;
/

Триггер в схеме scheme1.

Код: (SQL)

CREATE OR REPLACE TRIGGER scheme1.A_IU_mytable
AFTER UPDATE OR INSERT
ON scheme1.mytable
FOR EACH ROW
BEGIN
         scheme2.UPDATE_DISC_DATE(:NEW.custnr);
END;
/

[RXL]

Ё!!!

Если триггер создать в scheme2, то скушивает и не давится.

[McZim]

Для того пользователя под которым ты выполняешь тригер, нужно дать GRAND EXECUTE для процедуры которую хочешь выполнить в этом тригере.

[RXL]

У него права DBA. Ошибка происходит на этапе компиляции.

[McZim]

1. Нужно под этим пользователем под которым выполняется тригер, просто попробовать выполнить процедуру, т.е. понять вообще имеет доступ на ее исполнение или нет.
2. custnr в таблице mytable имеет тип NUMERIC?

[RXL]

1. Работает.
2. Да. NUMBER(8)

Как триггер перенес в туже схему, где процедура расположена, то сразу заработало. Меня такой вариант даже больше устраивает, т.к. в первой схеме чужое ПО и править его не безопасно. Хотя, понять, в чем дело - не мешает.

[McZim]

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

[McZim]

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

Входные данные.

схемы: test1 и test2

тригеры: test_exec (расположен в схеме test1)

процедуры: crt_table (расположена в схеме test2)

таблицы: out_name (расположена в схеме test1)

Задача.

Создавать таблицы в схеме test2 путем срабатывания тригера на таблице test1.out_name и взятием входного параметра за имя таблицы.

Решение.

1. Создать таблицу out_name в схеме test1 с параметрами name varchar.
2. Создать тригер test_execute и повешать на эту таблицу, ждать insert'а в эту таблицу.
3. Создать процедуру crt_table в ней выполнять создание таблиц в схеме test2 с именами переданными в качестве параметра.

ВАЖНО.

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

sqlplus test2/test2@XE

grant execute on crt_table to test1;

Выполение.

sqlplus test1/test1@XE

SQL> insert into out_name values('first');

ИТОГО.

в схеме test2 создается таблица first.

Код:

CREATE OR REPLACE PROCEDURE test2.crt_table(name_in varchar2)
IS
pragma autonomous_transaction;
BEGIN
execute immediate 'create table '||name_in||' (a varchar2(20))';
commit;
END;
/

Код:

CREATE OR REPLACE TRIGGER test1.test_exec
AFTER UPDATE OR INSERT
ON test1.out_name
FOR EACH ROW
BEGIN
test2.crt_table(:NEW.name);
END;
/

[RXL]

Обязательно потестирую.

Только... у меня "test1" имел GRANT DBA - рахве это не покрывает исполнение процедур?

[McZim]

RXL, а вот не знаю Поэтому пришлось прибегнуть к pragma autonomous_transaction; Она заставляет выполнять процедуру с правами ее создавшего, неважно кто и от куда ее запускает.