Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Reference by
Procedure Reference by [message #192569] Tue, 12 September 2006 21:22 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
is there any view where i can find what other procedures/functions/triggers that uses a particular procedure?

for example i have a proc A, it calls proc B, where can i query that i will know what other procedures A uses, thanks sir/mam
Re: Procedure Reference by [message #192582 is a reply to message #192569] Tue, 12 September 2006 22:36 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
you can use, USER_DEPENDENCIES dictionary view


Re: Procedure Reference by [message #192587 is a reply to message #192582] Tue, 12 September 2006 22:49 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the reply, although it doesnt have an indication if it is being referenced by, or it is its reference. thanks again!
Re: Procedure Reference by [message #192588 is a reply to message #192569] Tue, 12 September 2006 22:55 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
no it does.

SQL> desc user_dependencies

 Name                                   
 ----------------------------------
 NAME                                   
 TYPE                                   
 REFERENCED_OWNER                       
 REFERENCED_NAME                        
 REFERENCED_TYPE                        
 REFERENCED_LINK_NAME                   
 SCHEMAID                               
 DEPENDENCY_TYPE                        


do you see the column NAME and REFERENCED_NAME. The NAME column containes the object refering and the REFERENCED_NAME column contains the object referenced.

You can very easily put a query with a where clause like this
SELECT NAME
from user_dependencies
where referenced_name = 'A'

this will give you all objects which are using Procedure A.
SELECT referenced_name
from user_dependencies
where name = 'A'


this will give you all objects A is using.

Regards,
Liza
Re: Procedure Reference by [message #192623 is a reply to message #192588] Wed, 13 September 2006 00:48 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
my query


SQL> select name, type, referenced_name, referenced_type, dependency_type
  2  from dba_dependencies
  3  where name = 'CTS_SNAPSHOT_TBL_AIS'
  4  /

NAME                           TYPE              REFERENCED_NAME                                                  REFERENCED_TYPE   DEPENDENCY_TYPE
------------------------------ ----------------- ---------------------------------------------------------------- ----------------- ---------------
CTS_SNAPSHOT_TBL_AIS           TRIGGER           CTS_SNAPSHOT_TBL                                                 TABLE             HARD
CTS_SNAPSHOT_TBL_AIS           TRIGGER           CHANGEMAINLOGBOOKING   


SQL> select name, type, referenced_name, referenced_type, dependency_type
  2  from dba_dependencies
  3  where referenced_name = 'CTS_SNAPSHOT_TBL_AIS'
  4  /

NAME                           TYPE              REFERENCED_NAME                                                  REFERENCED_TYPE   DEPENDENCY_TYPE
------------------------------ ----------------- ---------------------------------------------------------------- ----------------- ---------------

SQL> 



i was just thinking that on the second query CTS_SNAPSHOT_TBL should appear on the NAME column and the CTS_SNAPSHOT_TBL on referenced_name.
Re: Procedure Reference by [message #192634 is a reply to message #192569] Wed, 13 September 2006 02:17 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Why it should, how can a table depend on itself.

The name column shows the objects which are reffering some other objects in the database. And the referenced_name shows the objects which are refered by the objects in the name column.

So what you are trying to do in the second query is finding out the objects which are actually refering the trigger CTS_SNAPSHOT_TBL_AIS. Remember a trigger refers to the table , not the table refers to the trigger.

Regards,
Liza
Re: Procedure Reference by [message #192663 is a reply to message #192634] Wed, 13 September 2006 03:34 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thanks for the explanation =) its much clearer now!
Re: Procedure Reference by [message #192670 is a reply to message #192634] Wed, 13 September 2006 04:02 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Why it should, how can a table depend on itself

Self referencing Join? Laughing

@OP, there is a utility called utldtree which builds tables views and procedures to allow you to clearly see a heirarchical view of dependencies for an object.

Read up on dependencies here

http://thinkunix.net/unix/db/oracle/docs-7.3/DOC/server/doc/ADG73/ch11.htm#index1021

[Updated on: Wed, 13 September 2006 04:07]

Report message to a moderator

Re: Procedure Reference by [message #192691 is a reply to message #192569] Wed, 13 September 2006 05:04 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Quote:

Self referencing Join? Laughing


Self Reference join is not a dependency, so does not show up in USER_DEPENDENCIES, neither in UTLDTREE.
Re: Procedure Reference by [message #192695 is a reply to message #192670] Wed, 13 September 2006 05:06 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
thank you so much for the link, i cant find that part on the oracle 9i documentation, only this link

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch53.htm#1005731

im just curious on the plsql developer tool where it has a References and Referenced By view.

[Updated on: Wed, 13 September 2006 05:08]

Report message to a moderator

Re: Procedure Reference by [message #192710 is a reply to message #192691] Wed, 13 September 2006 05:56 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Self Reference join is not a dependency, so does not show up in USER_DEPENDENCIES, neither in UTLDTREE.
which is why there was a lol after. Sorry if the joke did not come across (sarcasm rarely does in text) Smile
Re: Procedure Reference by [message #192717 is a reply to message #192695] Wed, 13 September 2006 06:05 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
I could be wrong, but I think that the link that I posted will be valid for 9i too.
icon7.gif  Re: Procedure Reference by [message #192745 is a reply to message #192569] Wed, 13 September 2006 07:42 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
i think i will have to focus on my sense of humor, prior to oracle.
Smile
Re: Procedure Reference by [message #192753 is a reply to message #192745] Wed, 13 September 2006 08:03 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Smile
Previous Topic: error message
Next Topic: cursor & decode
Goto Forum:
  


Current Time: Fri Dec 02 23:17:02 CST 2016

Total time taken to generate the page: 0.05768 seconds