Dependency check [message #279630] |
Fri, 09 November 2007 07:51  |
Kingfisher
Messages: 40 Registered: August 2007 Location: Oslo, Norway
|
Member |
|
|
I have a list of packages which are invalid.
How do I find out which packages are dependant on the invalid packages?
|
|
|
|
Re: Dependency check [message #279640 is a reply to message #279632] |
Fri, 09 November 2007 08:25   |
Kingfisher
Messages: 40 Registered: August 2007 Location: Oslo, Norway
|
Member |
|
|
pablolee wrote on Fri, 09 November 2007 14:55 | look into utldtree
|
It is not possible to find the dependencies of an invalid package without installing this package?
With SQL and the *_dependencies and *_objects views I can find out which packages a invalid package is dependant on, but not which packages are depending on it. Or at least, this is as far as I have found out.
|
|
|
|
Re: Dependency check [message #279657 is a reply to message #279640] |
Fri, 09 November 2007 09:14   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Quote: | It is not possible to find the dependencies of an invalid package without installing this package?
|
Are you asking a question or making a statement? I'll assume that you are asking a question and you actually meant "Is it..." rather than "It is..."
The answer is Yes, it is possible.
By the way, utldtree is not a package, so I'll assume that you didn't bother looking into my suggestion as to what you should look at.
Quote: | *snip..views I can find out which packages a invalid package is dependent on, but not which packages are depending on it.
|
So you are saying, that for a given package (let's say package pa1), you can see which package(s) reference (i.e. call upon the services of) pa1, but you cannot figure out how to see which package(s) pa1 references (i.e. which packages pa1 call on.
You see that there are 2 'name' columns in the views that you mentioned don't you?
|
|
|
Re: Dependency check [message #279659 is a reply to message #279643] |
Fri, 09 November 2007 09:16   |
Kingfisher
Messages: 40 Registered: August 2007 Location: Oslo, Norway
|
Member |
|
|
Michel Cadot wrote on Fri, 09 November 2007 15:50 | If a package is invalid, it is not compiled and so has no dependent object.
YOU know that it has dependent objects because YOU know the logic.
Oracle can't compile the object, so it can't know anything. Just like you can't know about what are talking about some people that speak a language you don't know.
|
Then how do Toad manage to find this out? If you select an invalid package in Toad's Schema Browser, you can look at which packages it is dependant on and also which objects are dependant on it.
|
|
|
Re: Dependency check [message #279661 is a reply to message #279643] |
Fri, 09 November 2007 09:18   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Michel, I've read your post a few times and I'm still not getting what you are trying to say here.
for example, you say that when an object is invalid, Oracle is unable to compile it. If I take that statement at face value, I'd have to say that it was wrong as we both know that Oracle absolutely can compile invalid objects. Also, I would disagree that an object has no dependents simply because it is invalid. I can't help but think however, that there is more to what you are trying to say. I'd certainly appreciate it if you could expand on what you mean.
Cheers
Jim
|
|
|
|
|
|
|
Re: Dependency check [message #279854 is a reply to message #279657] |
Sun, 11 November 2007 02:04   |
Kingfisher
Messages: 40 Registered: August 2007 Location: Oslo, Norway
|
Member |
|
|
pablolee wrote on Fri, 09 November 2007 16:14 |
Quote: | It is not possible to find the dependencies of an invalid package without installing this package?
|
Are you asking a question or making a statement? I'll assume that you are asking a question and you actually meant "Is it..." rather than "It is..."
|
English is not my first language, so the sentence became a mix of Norwegian and English 
Quote: | By the way, utldtree is not a package, so I'll assume that you didn't bother looking into my suggestion as to what you should look at.
|
Yes I did look at it, and its an utility that needs to be installed. Now, I don't have access to installing anything on our servers so this is not an option.
Quote: | So you are saying, that for a given package (let's say package pa1), you can see which package(s) reference (i.e. call upon the services of) pa1, but you cannot figure out how to see which package(s) pa1 references (i.e. which packages pa1 call on.
You see that there are 2 'name' columns in the views that you mentioned don't you?
|
Yes I do see that. I think I didn't explain this correctly.
The views I mentioned have columns that tell you the object and which object it is dependant on. Documentation is wrong on this point, as it claims referenced_name is a column for "type of object", which it isn't, but thats another issue.
Problem is, I can find my invalid packages and what they depend on, but I want to find out which packages are dependant on the invalid packages. The results I get doesn't make sense. In Toad, I can find the answers, but that won't do as I need to automate this in a script.
For instance:
I have one package calc_premiums that is invalid. There are several packages dependant on this package, but I can only find these packages by reading their sourcecode. If a package references calc_premiums, it is dependant on it.
If I do a query for calc_premiums in the referenced_name column I find nothing, but I find it in the name column of the mentioned dependencies-view.
How do I find out about such dependencies without reading all packages' source?
|
|
|
Re: Dependency check [message #279857 is a reply to message #279662] |
Sun, 11 November 2007 02:08   |
Kingfisher
Messages: 40 Registered: August 2007 Location: Oslo, Norway
|
Member |
|
|
Michel Cadot wrote on Fri, 09 November 2007 16:27 | This is because P2 can't be compiled (as if it were in another language) and so Oracle can't know you programmed a dependency (call) on P3 (as he can't understand your language).
|
Thanks, Michel. I don't have an Oracle server available right now but I'll test this later.
I understand what you're getting at, but it still seems to me Toad manages anyway. Maybe because the package have compiled successfully at one time, but now fails? Just guessing.
|
|
|
Re: Dependency check [message #279862 is a reply to message #279857] |
Sun, 11 November 2007 02:31  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I don't know what TOAD does. TOAD has many bugs.
I only trust Oracle dictionary (not always) never a GUI.
If a package compiled then becomes invalid due to a compilation error then dependencies are cleaned:
SQL> create or replace package p1 as procedure p; end;
2 /
Package created.
SQL> create or replace package p2 as procedure p; end;
2 /
Package created.
SQL> create or replace package p3 as procedure p; end;
2 /
Package created.
SQL> create or replace package body p1 as procedure p is begin p2.p; end; end;
2 /
Package body created.
SQL> create or replace package body p2 as procedure p is begin p3.p; end; end;
2 /
Package body created.
SQL> create or replace package body p3 as procedure p is begin null; end; end;
2 /
Package body created.
SQL> col name format a2
SQL> col referenced_name format a2
SQL> select name, type, referenced_name, referenced_type from user_dependencies
2 where name like 'P_' order by 1, 2, 3, 4
3 /
NA TYPE RE REFERENCED_TYPE
-- ----------------- -- -----------------
P1 PACKAGE BODY P1 PACKAGE
P1 PACKAGE BODY P2 PACKAGE
P2 PACKAGE BODY P2 PACKAGE
P2 PACKAGE BODY P3 PACKAGE
P3 PACKAGE BODY P3 PACKAGE
5 rows selected.
SQL> create or replace package body p2 as procedure p is begin p3.p; 'invalid'; end; end;
2 /
Warning: Package Body created with compilation errors.
SQL> select name, type, referenced_name, referenced_type from user_dependencies
2 where name like 'P_' order by 1, 2, 3, 4
3 /
NA TYPE RE REFERENCED_TYPE
-- ----------------- -- -----------------
P1 PACKAGE BODY P1 PACKAGE
P1 PACKAGE BODY P2 PACKAGE
P3 PACKAGE BODY P3 PACKAGE
3 rows selected.
If you have a test when this is not the case then post it we can check it by our own.
Regards
Michel
|
|
|