Проблема: Понадобилось сделать генератор SQL скриптов для переноса данных по таблицам. Простой путь: Нашел в сети топорный шаблон под названием fn_gen_inserts(updates) и вознамерился было попользоваться им, но понял, что код не удовлетворяет моим и без того скромным требованиям к генерации скриптов. У меня SQL скриптами осуществляется перенос данных с сервера разработки на сервер тестирования и промышленный сервер. Поэтому задавать имена целевых таблиц и их владельцев, как того требуют авторы исходных кодов вышеназванных функций, как-то несуразно. Как известно, пакет DBMS_SQL имеет несколько простых и удобных методов описания столбцов заданного динамически SQL запроса, но описания таблиц - нет. Как мне кажется, в случае анализа простых SQL запросов, вполне можно попытаться определить название таблицы и ее владельца в автоматическом режиме.
Задача: По заданному строкой SQL запросу определять название опорной таблицы и ее владельца. Итак, кусочек PL/SQL кода, где переменная p_sql - передаваемый SQL запрос
...
Задача: По заданному строкой SQL запросу определять название опорной таблицы и ее владельца. Итак, кусочек PL/SQL кода, где переменная p_sql - передаваемый SQL запрос
...
-- 1) Evaluate execution plan for SQL query
begin
execute immediate 'explain plan for '||p_sql;
exception when others then
raise_application_error(-20001, 'ERROR: SQL query cannot be parsed ('||SQLERRM||')');
end;
-- 2) Getting the name of the first table in access order list (99,99%)
begin
select table_name into l_base_table_name from (
select regexp_substr(acc, '^[|]\s*(\w+)\s*[|]',1,1,'i',1) idx, regexp_substr(acc, '[|]\s*((\w|[$#_])+)\s*[|]$',1,1,'i',1) table_name
from (
select plan_table_output acc from table(dbms_xplan.display(null, null, 'BASIC -ROWS -COST -PREDICATE -PARALLEL -PARTITION -PROJECTION -ALIAS -NOTE -REMOTE'))
) tabaccinfo
where tabaccinfo.acc like '%TABLE ACCESS%' order by idx
) maintabacc where rownum = 1;
exception when others then
raise_application_error(-20002, 'ERROR: Cannot get the name of the source table ('||SQLERRM||')');
end;
-- 3) Get primary table owner from SQL query as a first name from the list of possible owners
begin
select owner into l_base_table_owner from (
select owner from all_tables t where t.table_name = l_base_table_name order by decode(t.owner, user, 1, 2)
) t where rownum = 1;
exception when others then
raise_application_error(-20003, 'ERROR: Cannot get the owner of the source table ('||SQLERRM||')');
end;
...
после этого можно обработать переданный запрос в DBMS_SQL, чтобы обработать описания столбцов запроса.
Разумеется, есть несколько допущений, которые значительно ограничивают применимость данного подхода. Вот они:
1) Совершенно необязательно, что основная таблица запроса будет идти первой в порядке доступа в плане выполнения SQL запроса или в случае выгрузки из пайплайновой функции такой таблицы вообще может не быть. В таких случаях я явно задаю название целевой таблицы
2) Совершенно не факт, что владельца таблицы можно получить просто получив отсортированный список владельцев одноименного объекта. В случае, если владелец объекта не может быть определен таким образом, я задаю его вручную, явно.
В моем случае, для выгрузки данных из таблиц в скрипт, этими допущениями вполне можно пренебречь.
