Re: FW: Last DDL Time for packages
- From: Roger Xu <wellmetus@xxxxxxxxx>
- To: mark.powell@xxxxxxx
- Date: Tue, 28 Jul 2009 15:55:35 -0500
Thank you all. Here is my conclusion:
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@xxxxxxx> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephane Faroult
> Sent: Tuesday, July 28, 2009 4:14 PM
> To: wellmetus@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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> @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> @foo
>
> Package created.
>
>
> Package body created.
>
> ORACLE-SQL> @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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
