Overview
You are seeking the following kind of information for each of the column definition terms of the investigated view ( employee_vw
):
- Dependencies between the column definition term and any function in the system
- Dependencies between the column definition term and the column of any base view/table
AFAIK no dependencies on the column level are maintained by the data dictionary. However, the column catalog and dependencies among tables/views and functions can be queried. Using this information, the desired result can be approximated.
Three kinds of information are exploited:
- Dependencies between the investigated view (
employee_vw
) and any function.
- Dependencies between the investigated view (
employee_vw
) and its base tables and views
- Occurrence of a base table/view column in the column definition term of the investigated view (
employee_vw
).
The first two kind of data are available through the dba_dependencies
system view.
For the last item, all column names from the base tables/views are matched against the view definition text, constraining any column name occurrence such that it must be preceded by the name of any of the referenced functions.
Query
The following query implements the idea from above:
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||'%')
;
The base tables/views may in fact be synonyms. The following query caters for this condition:
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||'%')
;
Note that functions from plsql packages may be applied in the column definition. If you need to take this into account, use fndep.referenced_type IN ( 'FUNCTION', 'PACKAGE' )
in the where conditions.
Caveats
The solution is an approximation only with (at least) these deficiencies:
- The names of a referenced function and a base table/view column may occur in different column definition terms. ( false positives )
- The names of referenced function and a base table/view column may occur in string literals. ( false positives )
- The view definition text available through
dba_views.text_vc
is limited to 4000 chars. The actual definition, however, may be up to 32767 chars in length. The latter case can be detected by checking the view definition length. The complete text is available too in column dba_views.text
, unfortunately of datatype long
which cannot be easily operated on. (false negatives; false positives in edge cases)
If your view definitions exceed the length of 4000, you are probably best off in duplicating the dba_views
record for the investigated view in a temporary table replacing text
with an equivalent clob column. The conversion can be done using to_lob
. Beware of the intricacies sketched in this article.
I have no idea how to circumvent the other caveats without resorting to full-fledged sql parsing.