“It was working yesterday…”

Working in any form of shared development environment you will sooner or later hit a problem when something that used to work has either stopped working or is doing something it wasn’t doing before.

So how do you find out what have changed? With regards PL/SQL objects such as Packages, Triggers etc. the answer can be found by querying the Oracle Data Dictionary.

In the November/December 2012 issue of Oracle Magazine, Steven Feuerstein’s article on PL/SQL  101 describes the information that is available to developers via the various Data Dictionary views and included in that article is a SQL query that can be used to display objects belonging to the user that have changed today:

SELECT object_type,
       object_name,
       last_ddl_time
FROM   user_objects
WHERE  last_ddl_time >= TRUNC(SYSDATE)
ORDER BY object_type,
         object_name

Running this query in SQL Developer shows that the ADD_EMPLOYEE procedure has changed or has it?

One limitation with this approach is that the query relies on the last_ddl_time column. According to the Oracle documenation this column…

“is the last modification of the object resulting from a DDL statement (including grants and revokes)”.

So this column is not set just by code changes. For the ADD_EMPLOYEE example I didn’t make any changes to the procedure code but I did recompile it, which changed the value for the last_ddl_time column and so it appeared when I ran the query against user_objects.

This limitation aside,  I have found this to be a useful query. Thanks Steven!