Home » SQL & PL/SQL » SQL & PL/SQL » modification date for package SP (11.0.2.10)
modification date for package SP [message #669328] Mon, 16 April 2018 02:44 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi all,

How do I know the last modification date for a stored procedure under a certain package?

Using the following can only get information about stored procedures that are NOT under package, and modifying
OBJECT_TYPE = 'PACKAGE' 
returns information about the whole package.



SELECT  LAST_DDL_TIME, TIMESTAMP
FROM    DBA_OBJECTS
WHERE   OBJECT_TYPE = 'PROCEDURE'
        and 
        AND OBJECT_NAME = 'My_SP';

Thanks,
Ferro
Re: modification date for package SP [message #669330 is a reply to message #669328] Mon, 16 April 2018 03:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The question is meaningless - you don't compile/run in individual procedures in a package.
You compile/ run in the package body and / or package spec as a whole.
So you'll only find a time against the package body and spec in the data dictionary.
Oracle doesn't know nor care that when you last changed the package you only altered a single procedure in it.
If you want to know when a particular procedure in a package was changed you'll need to compare versions in your source control repository.
Re: modification date for package SP [message #669336 is a reply to message #669330] Mon, 16 April 2018 05:59 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear cookiemonster,

Thanks for the reply.

However I tried to test by changing one statement in the package body and recompiled successfully.

I then tried:


SELECT  LAST_DDL_TIME, TIMESTAMP
FROM    DBA_OBJECTS
WHERE   OBJECT_TYPE = 'PACKAGE'
        AND OBJECT_NAME = 'My_package';

and it returned an old date. Am I using the wrong fields?

Thanks,
Ferro
Re: modification date for package SP [message #669337 is a reply to message #669336] Mon, 16 April 2018 06:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Meditate over PACKAGE vs PACKAGE BODY and therefore:

SELECT  LAST_DDL_TIME, TIMESTAMP
FROM    DBA_OBJECTS
WHERE   OBJECT_TYPE = 'PACKAGE'
        AND OBJECT_NAME = 'My_package';

Versus:

SELECT  LAST_DDL_TIME, TIMESTAMP
FROM    DBA_OBJECTS
WHERE   OBJECT_TYPE = 'PACKAGE BODY'
        AND OBJECT_NAME = 'My_package';

SY.
Re: modification date for package SP [message #669369 is a reply to message #669337] Tue, 17 April 2018 23:55 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks Solomon
Previous Topic: Please help for resolving error for PIPELINED table function call
Next Topic: Dropping a Primary Key constraint
Goto Forum:
  


Current Time: Fri Mar 29 05:48:08 CDT 2024