Re: function/procedure dependencies

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 29 Apr 2006 19:41:54 +0200
Message-ID: <e308gv$hg4$00$1@news.t-online.com>

Frank Dietrich schrieb:
> Hi Frank,
>
> Frank van Bortel wrote:

>> Malcolm Dew-Jones wrote:
>>> Frank van Bortel (frank.van.bortel_at_gmail.com) wrote:
>>> : Frank Dietrich wrote:
>>> : > For standalone functions and procedure I can use the information from the
>>> : > *_DEPENDENCIES tables. But I need this information for functions and
>>> : > precedures inside of packages. So the package dependencies alone provide
>>> : > not enough information.
>>> : Does 'connect by' ring a bell? It should...

>
> In this context? No.
>

>>> and your point is?
>>>
>> select object_id
>> from public_dependency
>> CONNECT BY PRIOR object_id = referenced_object_id
>> START WITH referenced_object_id =
>>    (select object_id
>>     from  dba_objects
>>     where owner = '&which_owner'
>>       and object_name = '&object_name'
>>       and object_type = '&object_type')

>
> May be I not clear enough explain what I would like to get:
>
> I need not only the information which package depends on which other. I
> need the information packageA.procedureB depends on packageD.functionE.
>
> But it seems that this information is not stored in the database. I
> think I try to get this information in a way like this:
> - get dependencies from dba_objects
> - and then parse the package source
>
> But I think it's not worth to invest to much time in this.
>
> Frank

It is still not quite clear, what you try to achieve. Packaged procedures/functions as opposite to packages are not database objects, so dependencies are not stored. You can have call to package B within package A which don't belong to any function/procedure - for example in the initialisation block, or you can reference global types too from the package body itself.
If you goal is to estimate which lines in your packages should be modified after you change the referenced package - you still need to parse the source code. Every decent editor with can do it for you. (or you code it in plsql, should be not that complex).

Best regards

Maxim Received on Sat Apr 29 2006 - 12:41:54 CDT