Thanks for the question, SDS.
Asked: July 25, 2019 - 2:32 pm UTC
Last updated: July 26, 2019 - 4:33 am UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
I queries the DBA_OBJECTS table to find that the names of all the procedures under packages, which exist in the DBA_PROCEDURES table do not exist as objects in the DBA_OBJECTS table. There is only one entry for the package in DBA_OBJECTS and no entry for the procedures in the package.
There is a column called SUBOBJECT_NAME in DBA_OBJECTS. I was assuming that when it comes to procedures within packages, the OBJECT_NAME would be the package name and the SUBOBJECT_NAME would be the procedure name.
Isn't a procedure an individual object in Oracle? Why are procedures under packages treated differently to independent procedures?
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE PROCEDURE_NAME SUBPROGRAM_ID
-------------------------- ------------------------------- ---------- ------------- --------------------------- -------------
ABCD_OWNER P_DAIETRICS 146838 PROCEDURE 1
ABCD_OWNER EXISTS_PARTIN 146837 PROCEDURE 1
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_DEALERS2 1
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PROPOSALS2 2
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PROP_PARTY_ANEX 3
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_MODEL_DERIV 4
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_STATUS_CHANGES 5
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_STATUS_BREAKDOWN 6
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PROPOSAL_DELICH 7
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_RAMDONEES_INFO 8
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_VEHICLE_BLOW 9
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_TRADING_CHOPS 10
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PRODUCTS2 11
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PROP_RAMIF 12
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PRODUSIL_UW 13
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_HIST_DWELL 14
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_AGGREMLIN_DAY 15
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_AGGREMLIN_HOUR 16
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_PIGSTAAL_BREAK 17
ABCD_OWNER PK_PHASE 146835 PACKAGE GOPHS_NEWDAB_LVOMMENSTS 18
ABCD_OWNER PK_PHASE 146835 PACKAGE CHECKRAINTREE 19
ABCD_OWNER PK_PHASE 146835 PACKAGE P_DEL_USEFELNEW 20
ABCD_OWNER PK_PHASE 146835 PACKAGE 0
ABCD_OWNER PK_BIES 146836 PACKAGE 0
DBA_OBJECTS
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE SUBOBJECT_NAME
-------------------------- ------------------------------- ---------- ------------- ------------------------------
ABCD_OWNER PK_PHASE 146835 PACKAGE
ABCD_OWNER PK_BIES 146836 PACKAGE
ABCD_OWNER EXISTS_PARTIN 146837 PROCEDURE
ABCD_OWNER P_DAIETRICS 146838 PROCEDURE
ABCD_OWNER PK_BIES 147312 PACKAGE BODY
ABCD_OWNER PK_PHASE 147313 PACKAGE BODY
and Connor said...
In Oracle, the *package* is the object, not the subcomponent routines (procedures, functions, types, cursors, constants etc etc) that are within it.
Package level makes sense because we do things like
- create synonymns
- assign grants
- compile
all at the package / package body, not at the subcomponent level.
Similarly, a standalone procedure or function is an object for the same reasons as above. They can be synonym'd, granted etc.
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment