Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
Хотелось бы изменять тип (CREATE OR REPLACE TYPE XTLG_TYPE AS OBJECT ...) в схеме_1 при изменении структуры определенной таблицы в схеме_2.
Есть такая возможность?

1. Создаем тип в схеме_1
create type XTLG_TYPE as object
(
ID NUMBER,
NUMB NUMBER(17,2)
);

2. Создаем таблицу RRR в схеме2
create table RRR
(
ID NUMBER,
NUMB NUMBER(17,2)
);

3. Создаем DDL-триггер в схеме_1 на изменение таблицы RRR в схеме_2
create or replace trigger ddl_trigger
after alter on FACT.SCHEMA
begin
if ora_dict_obj_owner <> 'FACT' or ora_dict_obj_name <> 'RRR' then
return;
end if;
EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE XTLG_TYPE AS OBJECT
(
ID NUMBER,
NUMB NUMBER(17,7)
);';
end;

4. В схеме_2 пытаемся сохранить изменение структуры таблицы RRR. Получаем сообщение об ошибке.
ORA-00604: error occurred at recursive SQL level 1
ORA-00902: invalid DDL operation in system triggers
ORA-06512: at line 7
1 фев 17, 17:54    [20170706]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17089
Если не сработает триггер в автономной транзакции (а скорее всего, не сработает), можно из триггера запустить DBMS_JOB-задание на изменение типа
1 фев 17, 17:58    [20170730]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
Вячеслав Любомудров, спасибо, попробовал:
job_text :=
'begin
execute immediate ''' ||
'CREATE OR REPLACE TYPE XTLG_TYPE AS OBJECT
(
ID NUMBER,
NUMB NUMBER(17,7)
)'';'
|| chr(10) || 'end;';
dbms_job.submit(job => job_no_out,
what => job_text,
next_date => sysdate,
no_parse => TRUE);

К сожалению, не помогло(. Задание висит. Смотрю
select * from USER_JOBS

поле FAILURES = 3
1 фев 17, 19:02    [20170917]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26713
Бород
FAILURES = 3
STFF job errors
+ ТОП №4
1 фев 17, 19:11    [20170946]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
Elic, пока не понял(
1 пядь = 1/12 сажени = 1/4 аршина = 4 вершка = 7 дюймов = 17,78 см.
1 фев 17, 19:28    [20171005]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17089
Бород
Вячеслав Любомудров, спасибо, попробовал:
job_text :=
'begin
execute immediate ''' ||
'CREATE OR REPLACE TYPE XTLG_TYPE AS OBJECT
(
ID NUMBER,
NUMB NUMBER(17,7)
)'';'
|| chr(10) || 'end;';
dbms_job.submit(job => job_no_out,
what => job_text,
next_date => sysdate,
no_parse => TRUE);

К сожалению, не помогло(. Задание висит. Смотрю
select * from USER_JOBS

поле FAILURES = 3
Ошибки ты можешь посмотреть в alert.log (и соответствующем trace-файле) или просто залогировав их в обработчике ошибок в какую-нибудь табличку.
Но скорее всего, нет соответствующих прав. Например, CREATE TYPE предоставлена через роль, а не напрямую.
Вот тебя и послали в ТОП4
2 фев 17, 01:59    [20171723]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
Почитал тут: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8001.htm

Из схемы_1 выполнил:
GRANT all on FORS.XTLG_TYPE to FACT with grant option;
GRANT EXECUTE ANY TYPE TO FACT with ADMIN OPTION;

В trace-файле вижу:
ORA-12012: error on auto execute of job 65
ORA-01031: insufficient privileges
ORA-06512: at line 2
13 фев 17, 19:06    [20208879]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26713
Бород
Почитал тут: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8001.htm
Читай ещё раз Prerequisites. До просветления.


Бород
1. Создаем тип в схеме_1
create type XTLG_TYPE as object
Попробуй после
set role none;
14 фев 17, 07:45    [20209663]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
Спасибо знатокам.
Хотя полное просветление не наступило, но код как-то заработал.
А именно: после изменения структуры таблицы схемы_2 (FACT.RRR) срабатывает триггер, изменяющий тип XTLG_TYPE в схеме_1.
Только почему-то это изменение идет с запаздыванием на один шаг.
Например, увеличиваю размер поля таблицы RRR в схеме FACT (схеме_2) с 9 до 10 - в типе XTLG_TYPE в схеме_1 он 9. Следущим шагом увеличиваю размер поля с 10 до 11 - в типе он 10...
Заголовок тригерра такой:
create or replace trigger ddl_trigger
after alter on FACT.SCHEMA
...

Пока не понял почему так несправедливо устроен мир Oracle:)
17 фев 17, 13:55    [20221195]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Бород
Пока не понял почему так несправедливо устроен мир Oracle:)


Без триггера:

1. Пользователь выдал ALTER TABLE
2. Oracle выполнил неявный COMMIT
3. Oracle изменил data dictionary
4. Oracle выполнил неявный COMMIT тем самым зафиксировав изменения.

AFTER триггер выполняется как шаг 3.1, посему не видит изменения в data dictionary. Триггер запускает job который пока не выполняется. Шаг 4 выполняет COMMIT в прoцессе которого запускается job и не видит изменения в data dictionary. В результате имеем "с запаздыванием на один шаг". Измени время выполнения job на скажем SYSDATE + 10 / 3600 / 24 (+ 10 секунд).

А вообще - ты изобретаешь велосипед с треугольными колесами, посему "Пока не понял почему так несправедливо устроен мир Oracle:)". В реляционных базах таблицы и их структура статичны. Поле NUMB траслируется (напрямую или косвенно) в какое-то бизнес определение предусматривающeе некоторый тип и размерность данных. Бизнес определение не скачет как биржевые сводки, и если меняется то редко. Так-что зачем эта динамика. Кроме того в серьезных проектах используется ERD и все структурные изменения делают там а затем генерируют DDL.

SY.
18 фев 17, 15:25    [20224240]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17089
SY
Шаг 4 выполняет COMMIT в прoцессе которого запускается job и не видит изменения в data dictionary.
Ты сегодня в ударе

По тому триггеру, который он показал ему вообще не важен data dictionary
18 фев 17, 15:30    [20224253]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Вячеслав Любомудров
Ты сегодня в ударе

По тому триггеру, который он показал ему вообще не важен data dictionary


Ну я дал товарищу "benefit of a doubt" и исходя из "Например, увеличиваю размер поля таблицы RRR в схеме FACT (схеме_2) с 9 до 10 - в типе XTLG_TYPE в схеме_1 он 9. Следущим шагом увеличиваю размер поля с 10 до 11 - в типе он 10" решил реальный триггер мняет тип согласно изменениям таблицы.

SY.
18 фев 17, 15:55    [20224306]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
SY
Без триггера:

1. Пользователь выдал ALTER TABLE
2. Oracle выполнил неявный COMMIT
3. Oracle изменил data dictionary
4. Oracle выполнил неявный COMMIT тем самым зафиксировав изменения.

AFTER триггер выполняется как шаг 3.1, посему не видит изменения в data dictionary. Триггер запускает job который пока не выполняется. Шаг 4 выполняет COMMIT в прoцессе которого запускается job и не видит изменения в data dictionary. В результате имеем "с запаздыванием на один шаг". Измени время выполнения job на скажем SYSDATE + 10 / 3600 / 24 (+ 10 секунд).

А вообще - ты изобретаешь велосипед с треугольными колесами, посему "Пока не понял почему так несправедливо устроен мир Oracle:)". В реляционных базах таблицы и их структура статичны. Поле NUMB траслируется (напрямую или косвенно) в какое-то бизнес определение предусматривающeе некоторый тип и размерность данных. Бизнес определение не скачет как биржевые сводки, и если меняется то редко. Так-что зачем эта динамика. Кроме того в серьезных проектах используется ERD и все структурные изменения делают там а затем генерируют DDL.

SY.

Спасибо. Время выполнения job увеличил. Не помогло.
Дело в том, считывание структуры таблицы происходит ранее в этом же триггере. И проблема шага 3.1 возникает там:

job_text := ' select LISTAGG(CASE' ||
' WHEN t.DATA_TYPE = ''VARCHAR2'' THEN t.COLUMN_NAME||'' VARCHAR2(''||t.DATA_LENGTH||'')''||chr(10)' ||
' WHEN t.DATA_TYPE = ''INTEGER'' THEN t.COLUMN_NAME||'' INTEGER''' ||
' WHEN t.DATA_TYPE = ''NUMBER'' THEN t.COLUMN_NAME||'' NUMBER''||CASE WHEN t.DATA_PRECISION is not NULL THEN ''(''||t.DATA_PRECISION||CASE WHEN t.DATA_SCALE is not NULL THEN '',''||t.DATA_SCALE ELSE null END||'')'' ELSE null END||chr(10)' ||
' WHEN t.DATA_TYPE = ''DATE'' THEN t.COLUMN_NAME||'' DATE''||chr(10)' ||
' ELSE t.COLUMN_NAME' ||
' END,' ||
''',' ||
''') WITHIN GROUP (ORDER BY t.SEGMENT_COLUMN_ID) agg' ||
' from sys.all_tab_cols t' ||
' where t.owner=''FACT''' ||
' and t.table_name=''RRR''' ||
' and t.COLUMN_ID > 0';

EXECUTE IMMEDIATE job_text INTO sql_text;

Видимо, надо сделать задержку выолнения строки "EXECUTE IMMEDIATE job_text INTO sql_text".
Непонятно как сделать это красиво.

Зачем вся эта хрень?
Статичные структуры таблиц иногда меняются. И хотелось бы процесс воздействия изменения на код автоматизировать.
20 фев 17, 17:41    [20230034]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Бород
Спасибо. Время выполнения job увеличил. Не помогло.
Дело в том, считывание структуры таблицы происходит ранее в этом же триггере. И проблема шага 3.1 возникает там:
Видимо, надо сделать задержку выолнения строки "EXECUTE IMMEDIATE job_text INTO sql_text".
Непонятно как сделать это красиво.


Включить мозг: "AFTER триггер выполняется как шаг 3.1, посему не видит изменения в data dictionary". Посему считывание структуры таблицы должно быть не в триггере а в job. Передавай в job owner и table_name.

Бород
Зачем вся эта хрень?
Статичные структуры таблиц иногда меняются. И хотелось бы процесс воздействия изменения на код автоматизировать.


Дорога в ад вымощена благими намерениями (хотелками).

SY.
20 фев 17, 17:57    [20230102]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
SY
Включить мозг: "AFTER триггер выполняется как шаг 3.1, посему не видит изменения в data dictionary". Посему считывание структуры таблицы должно быть не в триггере а в job. Передавай в job owner и table_name.
Мозг начал подавать признаки жизни :)
Ок, передаю в job, выполняю select. Но дальше в коде второй job - пересоздание типа. Там нужен результат выполнения первого job. А на момент создания второго job результата первого еще нет, поэтому в строке для второго job видим хрень, верно?
Есть возможность перед EXECUTE IMMEDIATE включить тормоз на 10 сек?
Поможет ли это или напоремся на новые вилы?
20 фев 17, 18:05    [20230139]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 1973
SY,

при всём уважении, втопку ERD
20 фев 17, 18:11    [20230157]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 1973
точнее, не ERD, как таковое, а подход ERD >> DDL
20 фев 17, 18:14    [20230167]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
Тормоз на минуту не помог.
Мозг покрылся тиной и отключился.
Пойдем в читалку;)
20 фев 17, 18:25    [20230231]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Зачем втоpой job? Job читает DBA_TAB_COLUMNS для owner + table_name, формирует текст для CREATE OR REPLACE TYPE и выполняет его через EXECUTE IMMEDIATE.

SY.
20 фев 17, 18:27    [20230239]     Ответить | Цитировать Сообщить модератору
 Re: ALTER TYPE в DDL-тригере  [new]
Бород
Member

Откуда:
Сообщений: 9
SY, наверное, вы правы. Спасибо!
Точно, это тина. Пойду попробую; с кавычками, к сожалению, быстро не получается.
20 фев 17, 18:30    [20230257]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить