CODEWITHSUNDEEP
oracle-databaseplsql
pradeeptp
pradeeptp

Reputation: 2151

Where is object_id of Stored Procedure stored in Oracle?

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.

Upvotes: 2

Views: 2604

Answers (2)

whatsupbros
whatsupbros

Reputation: 802

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

Upvotes: 3

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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.

Upvotes: 0

Related Questions