How to get the functions name used by a column in the whole database?

Question

I have a view called employee_vw which contains the query below:

select function_standard(ename) from employees;

I want to find out which functions are applied on a column. I tried using select dbms_metadata.get_ddl('COLUMN','ENAME','HR') FROM DUAL, but there is no column object type as seen.

My requirement is to be able to list all of the functions that are applied to a specified column in the whole database. Is there any way to find this out?

Thanks in advance.

score 1 · Accepted Answer · answered Feb 01 '18 at 13:56

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.

How to get the functions name used by a column in the whole database?

1 Answers1