Thanks for the question, Enrique.

Asked: January 04, 2010 - 10:31 pm UTC

Last updated: August 08, 2017 - 10:32 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

Tom,

Happy New Year. Thanks for all your many contributions to the Oracle community.

I want to find out column dependencies on views. Basically if I have a view MYVIEW with columns A,B,C,D I'd like to write a query to show the source table/column name for all columns.

The following link show an interesting extension of DBA_DEPDENDENCIES by adding a REFERENCED_COLUMN column.

http://rwijk.blogspot.com/2008/10/dbadependencycolumns.html

It's based on fine grained dependency tracking which seems to be an 11g feature.

Is there a way to get column dependency on a 10g database (other than looking at the view definition)? I'm afraid the answer is a simple "no" but please confirm.

Thanks

and Tom said...

prior to 11g, that was not captured anywhere - column level dependencies were new in 11g Release 1.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

so, why

Sokrates, December 02, 2015 - 12:07 pm UTC

... are they still (even on V12) not exposed to us ?
For example, in a View DBA_DEPENDENCY_COLUMNS ?

December 03, 2015 - 1:58 am UTC

I suggest you raise an enhancement request.

(I agree with you)

thanks

Sokrates, December 03, 2015 - 8:16 am UTC

done

dependency columns

PHANI, August 06, 2017 - 4:35 am UTC

Hi Tom,

THis is great solution and helped me. Is there any way to find the kind of Type of Operations that was done in the SQL Procedure.
Ex: SELECT , UPDATE, MERGE

August 06, 2017 - 8:11 pm UTC

Not without actually scanning the source code once you have located the plsql units.

Also check out PL/scope

http://stevenfeuersteinonplsql.blogspot.com.au/2017/03/a-roundup-of-new-plsql-features-in.html

PHANI, August 06, 2017 - 4:41 am UTC

Hi Tom,

This is great solution and helped me. Is there any way to find the kind of Type of Operations on Columns present in SQL Procedure.
Ex: SELECT , UPDATE, MERGE

PLSCOPE in 12.2

Rajeshwaran Jeyabal, August 07, 2017 - 11:17 pm UTC

Team:

Reading through the PLSCOPE from documentation.
http://docs.oracle.com/database/122/ADFNS/plscope.htm#ADFNS403
<quote>
An identifier that is passed to a subprogram in IN OUT mode has both a REFERENCE usage (corresponding to IN) and an ASSIGNMENT usage (corresponding to OUT).
</quote>

Able to find an entry for REFERENCE, but not ASSIGNMENT.
Could you help us to understand if we got missed out something here?

demo@ORA12C> create or replace procedure p(p_out in out number)
  2  as
  3  begin
  4     null;
  5  end;
  6  /

Procedure created.

demo@ORA12C>
demo@ORA12C> alter procedure p compile
  2  plscope_settings='identifiers:all';

Procedure altered.

demo@ORA12C> column object_name format a10
demo@ORA12C> column name format a10
demo@ORA12C> select name,type,object_name,usage,usage_id
  2  from user_identifiers
  3  where object_name ='P'
  4  order by usage_id;

NAME       TYPE               OBJECT_NAM USAGE         USAGE_ID
---------- ------------------ ---------- ----------- ----------
P          PROCEDURE          P          DECLARATION          1
P          PROCEDURE          P          DEFINITION           2
P_OUT      FORMAL IN OUT      P          DECLARATION          3
NUMBER     NUMBER DATATYPE    P          REFERENCE            4

demo@ORA12C>

August 08, 2017 - 10:32 am UTC

That's because you haven't assigned anything to it! And the reference you're seeing is the NUMBER data type, not your parameter.

Somewhere in p give it a value and refer to it:

create or replace procedure p(p_out in out number) as
begin
  p_out := 2;
  dbms_output.put_line(p_out);
end;
/

select name,type,object_name,usage,usage_id
from   user_identifiers
where  object_name = 'P'
order  by usage_id;

NAME    TYPE             OBJECT_NAME  USAGE        USAGE_ID  
P       PROCEDURE        P            DECLARATION  1         
P       PROCEDURE        P            DEFINITION   2         
P_OUT   FORMAL IN OUT    P            DECLARATION  3         
NUMBER  NUMBER DATATYPE  P            REFERENCE    4         
P_OUT   FORMAL IN OUT    P            ASSIGNMENT   5         
P_OUT   FORMAL IN OUT    P            REFERENCE    7

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.

Breadcrumb

Question and Answer