CODEWITHSUNDEEP
oracle-database
Beta033
Beta033

Reputation: 2013

Oracle get stored procedure last modified date

First, thank you all for your help.

I'm trying to locate something similar to MSSQL's sys.procedures.Modify_date in oracle(10g).

A little context:

We design many stored procedures for use in both oracle and mssql. it's simple enough in mssql to see which stored procedures have been updated (as modify_date will be newer). I've heard there was something similar for oracle but have found precious little on the intertubes. I also heard a rumor that there was something close, but it contains the last compilation date for the sproc. As i'm looking for "the last time someone altered the text of the sproc", this won't quite work.

Is this possible? is there some trick? Right now we maintain a text comment within the stored procedure and one of the devs wrote a routine to go pull out the date from that comment. It's klunky and easy to forget to do and i'd like to find a better way.

Again, thanks for your help.

Upvotes: 13

Views: 55445

Answers (3)

tuinstoel
tuinstoel

Reputation: 7316

Maybe it is better to use something like sub version or team system to handle source control.

Upvotes: 2

Gary Myers
Gary Myers

Reputation: 35401

If you are interested in actual changes to the code, look into the AUDIT statement or a DDL trigger

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425693

SELECT  LAST_DDL_TIME, TIMESTAMP
FROM    DBA_OBJECTS
WHERE   OBJECT_TYPE = 'PROCEDURE'
        AND OBJECT_NAME = 'PRC_MINE'

Upvotes: 21

Related Questions