Problem
It can happen that SQL developer suggests to reload an old package body that you are currently working on. If you click the suggested “autoupdate” then you will loose all current changes that you made to the package body. This just happened to a collegue of mine.
It is possible to recover the old code using the flashback feature of the oracle database. But one has to be quicker than the undo retention period of the database.
If the schema was edition enabled this complicates things a tiny bit further. We need to make sure to fetch the plsql code from the correct edition that we are interested in.
When we are quering normal data dictionary views like DBA_SOURCE this will always use the current edition. DBA_SOURCE_AE will show all code editions.
Flashback does not work well with data dictionary views. However we can use the base tables and run a flashback query there to see the old package code.
Action
Three simple statements will do it.
Set your dba session to use the correct edition (this might not be needed if the object id is found using a slightly different query). The edition needs to match the edition where the source code was originally compiled
ALTER SESSION SET EDITION="DEV$ALPHA";
Find the object id of the package body
Use the correct name of the package and your edition name
select obj# from sys."_CURRENT_EDITION_OBJ" where name = 'MYPACKAGE' and type# = 11 -- package body and namespace = 2 and defining_edition = 'DEV$ALPHA';
Flashback query to show the source code for the identified object
select * from sys.source$ --versions between timestamp (systimestamp - interval '25' minute) and systimestamp as of timestamp (systimestamp - interval '25' minute) where obj# = 1906090;
Done.
Aftermath
The starting situation can be reproduced by changeing body and spec of the same package. Once the spec is recompiled, the body needs to be recompiled too. Then SQL developer suggests to load the new code (which is in fact the old code). If you click yes, it will overwrite the current changes.
svenweller 