Re: FW: Last DDL Time for packages

If LAST_DDL_TIME for PACKAGE BODY does not changed, the source code for that package hasn't been changed since.

Thanks!

On Tue, Jul 28, 2009 at 3:38 PM, Powell, Mark D <mark.powell_at_eds.com> wrote:

>
> Be aware that the last_ddl_time is updated for grants so the date value
> may not reflect an actual code change but may be the result of just a
> grant. My test shows only the specification date gets changed.
>
> UT1 > select object_type, to_char(last_ddl_time,'YYYYMMDD HH24:MI:SS')
> LTIME
> 2 from dba_objects
> 3 where object_name = 'DBA_MSG'
> 4 and object_type like 'PACKAGE%'
> 5 /
>
> OBJECT_TYPE LTIME
> ------------------ -----------------
> PACKAGE 20090728 16:32:17
> PACKAGE BODY 20090120 15:37:15
>
> UT1 > grant execute on mark.dba_msg to public
> 2 /
>
> Grant succeeded.
>
> UT1 > select object_type, to_char(last_ddl_time,'YYYYMMDD HH24:MI:SS')
> LTIME
> 2 from dba_objects
> 3 where object_name = 'DBA_MSG'
> 4 and object_type like 'PACKAGE%'
> 5 /
>
> OBJECT_TYPE LTIME
> ------------------ -----------------
> PACKAGE 20090728 16:36:01
> PACKAGE BODY 20090120 15:37:15
>
>
> -- Mark D Powell --
> Phone (313) 592-5148
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephane Faroult
> Sent: Tuesday, July 28, 2009 4:14 PM
> To: wellmetus_at_gmail.com
> Cc: oracle-l_at_freelists.org
> Subject: Re: Last DDL Time for packages
>
> Roger,
>
> You have TWO objects in DBA_OBJECTS for a package, the package and
> the package body. If the interface doesn't change, LAST_DDL_TIME isn't
> updated for the package - but is for the package body.
>
> ORACLE-SQL> _at_foo
>
> Package created.
>
>
> Package body created.
>
> ORACLE-SQL> select object_name, object_type
> 2
> ORACLE-SQL>
> ORACLE-SQL> ed
> Wrote file afiedt.buf
>
> 1 select object_name, object_type, last_ddl_time
> 2 from user_objects
> 3* where object_name = 'FOO'
> ORACLE-SQL> /
>
> OBJECT_NAME
> ------------------------------------------------------------------------
> --------
> OBJECT_TYPE LAST_DDL
> ------------------- --------
> FOO
> PACKAGE 22:10:05
>
> FOO
> PACKAGE BODY 22:10:06
>
>
> ORACLE-SQL> save pack
> Created file pack.sql
> ORACLE-SQL> ed foo
>
> [ some change to the package body]
>
> ORACLE-SQL> _at_foo
>
> Package created.
>
>
> Package body created.
>
> ORACLE-SQL> _at_pack
>
> OBJECT_NAME
> ------------------------------------------------------------------------
> --------
> OBJECT_TYPE LAST_DDL
> ------------------- --------
> FOO
> PACKAGE 22:10:05
>
> FOO
> PACKAGE BODY 22:11:38
>
>
> ORACLE-SQL>
>
>
>
> Roger Xu wrote:
> > Hi List,
> >
> > We are trying to use below SQL to obtain the last time at which a
> > package/procedure/function being modified. It seems to be working for
> > procedure and functions, but not for the package.
> >
> > select to_char(LAST_DDL_TIME, 'YYYY MM DD HH:MI:SS') from dba_OBJECTs
> > where OBJECT_NAME = 'MYPACKAGE';
> >
> > Is there any other way to find out a package's source code was
> > changed? Maybe I should look into the link between package and
> procedures?
> >
> > Thanks,
> >
> > Roger Xu
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l