??: How to find dependencies
- From: "jametong" <jametong@xxxxxxxxx>
- To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Sep 2008 17:29:20 +0800
Just check the dba/user/all_dependencies views
???: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] ??
Nigel Thomas
????: 2008?9?4? 17:03
???: Amir.Hameed@xxxxxxxxx; ORACLE-L
??: Re: How to find dependencies
Amir
If you know what package.procedure(s) you are interested in, you can just
search for references to 'package.procedure' in xxx_SOURCE view.
select name, type, line, text
from user_source
where lower(text) like '%package.procedure%'
and type = 'TRIGGER'
You can further limit by using a subquery finding all dependencies on the
package from triggers.
It is valid to leave spaces between package, period, and procedure name; if
that is likely, you might want to use a regex match rather than plain old
LIKE.
It is very rare (not completely unknown) for the package.procedure to be
split across a line break (I'd regard that as a coding standards failure) eg
IF X > Y THEN
Package ,
Procedure (a,b,c);
END IF:
Finally; this mechanism picks up comments (and commented out calls). You can
fairly easily exclude single line comments (--). It's more of a pain to
exclude multiline comments (/* ... */). You can decide whether this is a big
issue or not...
Regards Nigel
2008/9/3 Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Hi Folks,
Here is the scenario; If a PL/SQL package contains multiple procedures
and functions and there are triggers referencing a certain procedure in
that package then is there a way to list all those triggers? The
DBA_DEPENDENCIES view does not drill down to the sub-programs within a
package.
Thanks
Amir
--
//www.freelists.org/webpage/oracle-l
Other related posts:
