2

I am an beginner in PL/SQL, so I do not know whether this question makes sense. I am trying to find out the object_id of a stored procedure under a package (not under a schema) in Oracle 11g. I tried to query the following system tables/views which I thought should contain the object_Id of the SP.

  1. All_procedures
  2. User_obects

The All_procedures view does contain the name of the SP I am looking for but it contains only the object ID of the package. The reasons I want to find object_id of the Stored Procedure under a Package is are:

  1. Some of the PL/SQL programmers I talked to tells me that the SPs/Functions under a package are not considered an object. I want to understand why.

  2. I want to find out, in how many other SPs under the same package does this SP get referred. I hope public_dependency view contains this mapping, unless someone tells me this is the wrong view.

  3. If SPs/Functions are not identified as Objects under a package, then how do I get the list of SPs that refers to a specific SP.?

I had posted another question in Stackoverflow, which led me to the link How do you programatically identify a stored procedure's dependencies?. But the script mentioned in this post did not help me because the user_objects doesn't have any entry for the stored procedure under the package. It only has SPs under the Schema.

Community
  • 1
  • 1
pradeeptp
  • 2,131
  • 6
  • 29
  • 39

2 Answers2

3

Some of the PL/SQL programmers I talked to tells me that the SPs/Functions under a package are not considered an object. I want to understand why.

Packages in oracle database are schema units which groups logically related objects (such as types, variables, procedures and functions). They are considered as atomic units and if some procedure in a package refers to another procedure in another package, we have situation when the first package itself refers to the second package. So, the minimum object here is package itself, not a procedure or type or something else in the package.

I want to find out, in how many other SPs under the same package does this SP get referred. I hope public_dependency view contains this mapping, unless someone tells me this is the wrong view.

I've never come across the information how to get it. If referrence is inside the package, the package is independent object I think.

If SPs/Functions are not identified as Objects under a package, then how do I get the list of SPs that refers to a specific SP.?

You need to use views which contain source code, such as user_source/all_source/dba_source

whatsupbros
  • 802
  • 3
  • 13
  • 23
  • Thanks ZZa. I have always found that, in Oracle, simple things are very hard achieved (or not achieved). – pradeeptp May 18 '13 at 07:04
0

I won't comment on the first point, since I'm not privy to Oracle's design documents & thought process. I believe since the procedures & functions defined in a package are contained by the packge & dependent on them, they aren't assigned object ids.

I want to find out, in how many other SPs under the same package does this SP get referred

Easiest way would be to query user_source/all_source/dba_source depending on your access rights. Since these views contain all the source, code, you'll have to give appropriate filters.

If SPs/Functions are not identified as Objects under a package, then how do I get the list of SPs that refers to a specific SP.?

See above.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
  • I know that I can query user_source, but that does not give me any specific ID of the stored procedure. Moreover, it is not a very efficient way to build a reference tree. – pradeeptp May 17 '13 at 06:20
  • @pradeeptp You mention you want to find how many other SPs the SP gets referred. Querying user_source gives you just that. You can try using PL/Scope if you're 11g & above. Look at [ORACLE PL/Scope](http://stackoverflow.com/q/2781292) & [How can you tell if a PL/SQL Package, Procedure, or Function is being used?](http://stackoverflow.com/q/4670164) – Sathyajith Bhat May 17 '13 at 06:48
  • Thanks for your inputs. I know I can query user_source table, but I find it very inefficient to query a source code for references. So, can you confirm that there is no table which stores the object_id of the Stored procedure under a package? – pradeeptp May 18 '13 at 06:58