Как получить имя функции, используемой столбцом во всей базе данных?

Question

У меня есть представление под названием employee_vw, который содержит запрос ниже:

select function_standard(ename) from employees; 

Я хочу выяснить, какие функции применяются к столбцу. Я пытался с помощью select dbms_metadata.get_ddl('COLUMN','ENAME','HR') FROM DUAL, но нет видимого типа объекта столбца.

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

Заранее спасибо.

Answer 1

обзор

Вы ищете информацию следующего типа для каждого из терминов определения столбца исследуемого представления (employee_vw):

1. Зависимости между термином определения столбца и любой функцией в системе
2. Зависимости между термином определения столбца и столбцом любого базового представления / таблицы

AFAIK. Никакие зависимости на уровне столбцов не поддерживаются словарем данных. Однако каталог столбцов и зависимости между таблицами / представлениями и функциями могут быть запрошены. Используя эту информацию, желаемый результат может быть аппроксимирован.

Используются три вида информации:

1. Зависимости между исследуемым видом (employee_vw) и любая функция.
2. Зависимости между исследуемым видом (employee_vw) и его базовые таблицы и представления
3. Вхождение столбца базовой таблицы / представления в термин определения столбца исследуемого представления (employee_vw).

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

запрос

Следующий запрос реализует идею сверху:

  SELECT fndep.referenced_name  function_name
       , tcol.column_name       column_name
       , tcol.table_name        container_name
    FROM dba_dependencies fndep
    JOIN dba_dependencies tabdep   ON (tabdep.name = fndep.name AND tabdep.type = fndep.type)
    JOIN all_views        v        ON v.view_name = fndep.name 
    JOIN all_tab_cols     tcol     ON (tcol.table_name = tabdep.referenced_name)
   WHERE fndep.referenced_type  = 'FUNCTION'
     AND fndep.type             = 'VIEW'
     AND fndep.name             = UPPER('employee_vw')
     AND tabdep.referenced_type IN ( 'TABLE', 'VIEW' )
     AND lower(v.text_vc) LIKE lower('%'||fndep.referenced_name||'%'||tcol.column_name||'%')
       ;

Базовые таблицы / представления могут фактически быть синонимами. Следующий запрос обслуживает это условие:

  SELECT fndep.referenced_name  function_name
       , tcol.column_name       column_name
       , tcol.table_name        container_name
    FROM dba_dependencies fndep
    JOIN dba_dependencies tabsyndep   ON (tabsyndep.name = fndep.name AND tabsyndep.type = fndep.type)
    JOIN dba_synonyms     syn         ON (syn.synonym_name = tabsyndep.referenced_name)
    JOIN dba_tab_cols     tcol        ON (tcol.table_name = syn.table_name)
    JOIN dba_views        v           ON v.view_name = fndep.name 
   WHERE fndep.referenced_type  = 'FUNCTION'
     AND fndep.type             = 'VIEW'
     AND fndep.name             = UPPER('employee_vw')
     AND tabsyndep.referenced_type IN ( 'SYNONYM' )
     AND lower(v.text_vc) LIKE lower('%'||fndep.referenced_name||'%'||tcol.column_name||'%')
       ;

Обратите внимание, что функции из пакетов plsql могут применяться в определении столбца. Если вам нужно принять это во внимание, используйте fndep.referenced_type IN ( 'FUNCTION', 'PACKAGE' ) в условиях где.

Предостережения

Решение является приближенным только с (по крайней мере) этими недостатками:

• Имена ссылочной функции и столбца базовой таблицы / представления могут встречаться в разных терминах определения столбца. (ложные срабатывания)
• Имена ссылочной функции и столбца базовой таблицы / представления могут встречаться в строковых литералах. (ложные срабатывания)
• Текст определения представления доступен через dba_views.text_vc ограничено 4000 символов. Фактическое определение, однако, может иметь длину до 32767 символов. Последний случай может быть обнаружен путем проверки длины определения представления. Полный текст доступен также в столбце dba_views.text К сожалению, типа данных long который не может быть легко оперирован. (ложные отрицания; ложные срабатывания в крайних случаях)

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

Я понятия не имею, как обойти другие предостережения, не прибегая к полноценному разбору sql.