Re: question re: dba

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 21 Feb 2007 10:17:30 -0800
Message-ID: <1172081850.585806.236910@p10g2000cwp.googlegroups.com>

On Feb 21, 9:31 am, "EdStevens" <quetico_..._at_yahoo.com> wrote:
> Oracle 10.2.0.2 on HP-UX
>
> Doing some queries on dba_procedures, I get some rows where
> PROCEDURE_NAME is null. That seems pretty bizarre to me. Those same
> rows did have values on OBJECT_NAME, so I cross-referenced back to
> dba_objects:
>
> select p.owner,
> o.object_type,
> o.object_name,
> p.procedure_name
> from dba_objects o,
> dba_procedures p
> where p.owner in (<snip owner list>)
> and o.object_name = p.object_name
> order by 1, 2,3
>
> That showed some of the null procedure names on object_type of
> package, package body, procedure, synonym, trigger, but no
> consistency that I can see. Also, a bunch of the packages show an
> object name of 'VALID'.
>
> So, what am I seeing here? Am I looking at things from the wrong end?

Hi Ed,

Out of "idle curiosity" :) I checked, and in my simple test case it looks like procedures are only for packages, so I am not sure where the disconnect in your database and mine is. I will say the docs do not specifically state that the procedure name is null for a procedure, but it kind of makes sense with the additional object_name column in the same view. Can you post a desc on packages where the procedure_name column is populated are in %_procedures, and where it is not?

SQL> r

  1  select o.object_type,
  2         o.object_name,
  3         p.procedure_name
  4    from  user_objects o,
  5          user_procedures p

6 where o.object_name = p.object_name 7* order by 1, 2,3

OBJECT_TYPE OBJECT_NAME PROCEDURE_NAME

------------------- --------------------
------------------------------
FUNCTION            OSCALL
JAVA SOURCE         OSCALL
PROCEDURE           CHECK_SPACE
PROCEDURE           SEND_MAIL
PROCEDURE           SHOW_SPACE

SQL> desc send_mail
PROCEDURE send_mail

 Argument Name                  Type                    In/Out

Default?

----------------------- ------
P_SENDER VARCHAR2 IN P_RECIPIENT VARCHAR2 IN P_CCRECIPIENT VARCHAR2 IN P_SUBJECT VARCHAR2 IN P_MESSAGE VARCHAR2 IN

SQL> select procedure_Name from user_procedures;

PROCEDURE_NAME

SQL> select object_Name from user_procedures;

OBJECT_NAME

SHOW_SPACE
SEND_MAIL
CHECK_SPACE
OSCALL SQL> create or replace package test_proc_list is   2 procedure t;
  3 end test_proc_list;
  4 /

Package created.

SQL> create or replace package body test_proc_list is   2 procedure t is
  3 begin
  4 null;
  5 end;
  6 end test_proc_list;
  7 /

Package body created.

SQL> select o.object_type,
  2         o.object_name,
  3         p.procedure_name
  4    from     user_objects o,
  5          user_procedures p

6 where o.object_name = p.object_name 7 order by 1, 2,3
8 /

OBJECT_TYPE OBJECT_NAME PROCEDURE_NAME

------------------- --------------------
------------------------------
FUNCTION            OSCALL
JAVA SOURCE         OSCALL
PACKAGE             TEST_PROC_LIST       T
PACKAGE BODY        TEST_PROC_LIST       T
PROCEDURE           CHECK_SPACE
PROCEDURE           SEND_MAIL
PROCEDURE           SHOW_SPACE

7 rows selected.

SQL> HTH, Steve Received on Wed Feb 21 2007 - 12:17:30 CST

Re: question re: dba_procedures