Пытаясь улучшить ремонтопригодность SP в одной из наших систем, я решил, что использование цикла будет лучше, чем жестко закодированный массив значений (в данном случае имен таблиц), и попытался соответствующим образом реорганизовать код, чтобы добавление или удаление таблицы в систему не требовало редактирования массива. Оставив пока в стороне вопросы почему и зачем циклы (я очень хорошо знаю аргументы против них), может ли кто-нибудь объяснить, что происходит?
Представьте себе двух пользователей, SourceUser и DestUser, обоих в одной базе данных, каждый со своими таблицами в одном и том же табличном пространстве. Куча хранимых процедур в SourceUser заполняет данные из SourceUser в DestUser для целей отчетности. В рамках этого первая запускаемая процедура удаляет все таблицы в DestUser и создает их заново. Опять же, не обсуждая относительные достоинства этого здесь.
SourceUser имеет права Drop Any Table и Create Any Table на DestUser. В DestUser есть одна таблица, которую мы хотим сохранить. Итак, SQL, который я построил в процедуре, выглядит так:
Begin
For T In (SELECT TABLE_NAME FROM all_tables WHERE TABLE_NAME != 'MIDBLOG' AND OWNER = sTarget_DB) Loop
Begin
Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
Exception
When Others Then
--Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
NULL;
End;
End Loop;
End;
В этом случае для sTarget_DB установлено значение DestUser, и этот код запускается для SourceUser.
Когда процедура запускается, я обнаруживаю, что никакие таблицы не были удалены (перед запуском я убедился, что было несколько десятков таблиц, включая одну с именем MIDBLOG). Я запустил его в режиме отладки SQL Developer, и выполнение даже не попало внутрь цикла, поскольку кажется, что у него нет строк для обработки, но я точно знаю, что оператор select вернет пару десятков имен таблиц.
Далее я изменил его на это:
Begin
For T In (SELECT TABLE_NAME FROM all_tables WHERE OWNER = sTarget_DB) Loop
Begin
If T.TABLE_NAME != 'MIDBLOG' THEN
Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
End If;
Exception
When Others Then
--Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
NULL;
End;
End Loop;
End;
После этого единственная таблица, которую БЫЛО удалено, была той самой, которую я не хотел удалять! Еще более странным было то, что цикл выполнялся только один раз, как будто запрос на выборку возвращал только одну строку. Я мог бы увидеть, как это происходит, если бы я запускал процедуру в режиме отладки в SQL Developer 3.2. Мы сделали то же самое на ПК коллеги на SQL Developer (возможно, 3.1), и снова цикл выполнился только один раз, но на этот раз он правильно решил не удалять таблицу MIDBLOG и снова оставил все остальное в покое.
Если я запускаю любой из приведенных выше примеров как анонимный блок в SQL Developer, он делает именно то, что я ожидаю. Я попробовал более подробное явное объявление курсора и получил те же результаты, что и раньше. Я ни разу не получил никаких исключений.
Все это было на Oracle 10g Enterprise Edition Release 10.2.0.4.0 (64bit). Как только я попробовал это на Oracle 11g Enterprise Edition Release 11.2.0.1.0 (64-разрядная версия), все заработало нормально. С какой стати такое базовое требование должно демонстрировать такое сильно различное поведение в двух версиях? Может ли это работать так, как я хочу, в обеих версиях с одним и тем же кодом?
DBA
- плохая идея с точки зрения безопасности. Поскольку все роли невидимы в хранимой процедуре прав определяющего, хранимая процедура прав определяющего не может использовать привилегии, предоставленные ролиDBA
или любой другой роли. Он может использовать только те привилегии, которые предоставлены непосредственно пользователю. 30.11.2012authid current_user
к объявлению SP сортирует проблемы и гарантирует, что хранимая процедура наследует правильное разрешение для достижения того, что я хотел. Цель и теория этого были хорошо объяснены здесь: docs .oracle.com/cd/B19306_01/appdev.102/b14261/ 30.11.2012