Retrieve old versions of procedures with Flashback Query

Each stored procedure (or trigger, package etc.) is reflected in DBA_SOURCE, of course. Now imagine, you modified or even dropped a procedure without having the code available to recreate it properly. In this case, you may save the day by using Flashback Query (possible since 9i) against DBA_SOURCE, like in this example:

SQL> select text from dba_source where name='UPD_SALES' order by line;

TEXT
--------------------------------------------------------------------------------
procedure upd_sales as
begin
for i in 1..10000 loop
update sales set amount_sold=amount_sold*1 where rownum<2;
commit;
end loop;
end;

7 rows selected.

SQL> drop procedure upd_sales;

Procedure dropped.

SQL> connect / as sysdba
Connected.

SQL> select text from dba_source 
     as of timestamp systimestamp - interval '5' minute  
     where name='UPD_SALES' order by line;

TEXT
--------------------------------------------------------------------------------
procedure upd_sales as
begin
for i in 1..10000 loop
update sales set amount_sold=amount_sold*1 where rownum<2;
commit;
end loop;
end;

7 rows selected.

I could spool the output from above into a text file and use it afterwards to recreate my procedure as it was before 5 minutes! By the way, I know that the commit inside the loop is not a good idea here 🙂