Home » SQL & PL/SQL » SQL & PL/SQL » Dependency check ( Database 10g Enterprise Edition, 10.2.0.3.0 - 64bit, Solaris)
Dependency check [message #279630] Fri, 09 November 2007 07:51 Go to next message
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 #279632 is a reply to message #279630] Fri, 09 November 2007 07:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
look into utldtree
Re: Dependency check [message #279640 is a reply to message #279632] Fri, 09 November 2007 08:25 Go to previous messageGo to next message
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 #279643 is a reply to message #279640] Fri, 09 November 2007 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

Regards
Michel
Re: Dependency check [message #279657 is a reply to message #279640] Fri, 09 November 2007 09:14 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 #279662 is a reply to message #279661] Fri, 09 November 2007 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok I post an example:
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; 'invalid'; end; end;
  2  /

Warning: Package Body created with compilation errors.

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
P3 PACKAGE BODY      P3 PACKAGE

3 rows selected.

P1 calls P2 which is invalid and that itself calls (or rather want to call) P3.
You see P1 dependencies on P2 but you can't see P2 dependency on P3.
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).

Regards
Michel
Re: Dependency check [message #279670 is a reply to message #279662] Fri, 09 November 2007 09:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Thanks for the clarifications, I see what you are saying now. Appreciate the demo.
Re: Dependency check [message #279671 is a reply to message #279670] Fri, 09 November 2007 09:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never be hesitant to ask me explainations, I don't master English and I'm often not very clear.

Regards
Michel
Re: Dependency check [message #279672 is a reply to message #279671] Fri, 09 November 2007 09:47 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well, your English is a heck of a lot better than my French Smile
Re: Dependency check [message #279854 is a reply to message #279657] Sun, 11 November 2007 02:04 Go to previous messageGo to next message
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 Wink
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: formal parameter constraining
Next Topic: text file generation
Goto Forum:
  


Current Time: Thu Dec 08 04:11:09 CST 2016

Total time taken to generate the page: 0.06328 seconds