Can I recover older Oracle pl/sql source code from a package body after i have replaced with newer code

Question

I had created an Oracle PL/SQL package with a header and a body with lots of code. Later, I ended up accidentally erasing the code from that body after reran the CREATE OR REPLACE PACKAGE BODY... statement with different source code (which actually I intended to save under a different package name). Is there anyway I can recover my older replaced source code from the packege?

Answer 1

Unless you have logging/auditing of DDL commands enabled, or a backup of the database, then the answer is almost certainly not

Database definitions, including stored procedures, should always be treated like source code, and maintained in a code repository

Answer 2

You might be able to get it back by using a flashback query on all_source.

e.g. my package body is currently at version 2, executing this query as a standard user:

SQL> select text
  2  from all_source
  3  where name = 'CARPENTERI_TEST'
  4  and type = 'PACKAGE BODY';

TEXT


package body carpenteri_test
is

procedure do_stuff
is
begin
   dbms_output.put_line('version 2');
end do_stuff;

end carpenteri_test;

10 rows selected.

I know I changed this around 9:30 this evening so after connecting as a SYSDBA user I ran this query:

SQL> select text
  2  from all_source
  3  as of timestamp
  4  to_timestamp('04-JUN-2010 21:30:00', 'DD-MON-YYYY HH24:MI:SS')
  5  where name = 'CARPENTERI_TEST'
  6  and type = 'PACKAGE BODY';

TEXT
----------------------------------------------------------------------------

package body carpenteri_test
is

procedure do_stuff
is
begin
   dbms_output.put_line('version 1');
end do_stuff;

end carpenteri_test;

10 rows selected.

More information on flashback can be found here. Tom Kyte also demostrates how to use flashback with all_source here.