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 any way I can recover my older replaced source code from the package?

Harpp

Answer 1

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.

Communicant

Thanks - I tried the first one (w/o the timestamp) and it gave me the latest code. Then I added the timestamp and got "insufficient privileges" error. Will forward it to the dba who can maybe do it for me. – Harpp

The DBA says that "all_source is not usable with flashback". – Harpp

point your DBA to this link from Tom Kyte: asktom.oracle.com/pls/asktom/… – Communicant

Glad I could help. I've amended my answer to make it clear that you need to connect to as sys and included the link to Tom Kyte's example. – Communicant

Also got "insufficient privileges" but logging on as SYS eliminated the error. Thanks!!! – Tracheotomy

this didn't work, I had to query sys.source$. – Shimmy

Answer 2

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

Tier

Source control is a must on any and all code, including DB stuff. Small incremental changes and frequent check-ins is key to smooth software development for sure. – Godiva

Thanks for your comments - unfortunately I learnt this the hard way. Actually I almost got around to saving a hard copy of the code using the "Export DDL" option in SQL Developer when I had finished - but something came up and I lost track of it. Anyway - this episode ensures I will not forget it again. – Harpp

It's happened to us all. There are only 2 types of people: those who do backups/use source control, and those who have never had a data loss – Tier

Source control comments aside, the answer is to use flashback as described by @carpenteri – Carrion

Answer 3

Flashback query doesn't work on all_source view, you need to execute a flashback query on sys.source$ like this:

select source
    from sys.source$
    as of timestamp
    to_timestamp('8-NOV-2023 12:15:00', 'DD-MON-YYYY HH24:MI:SS')
where obj# = 109026;

Shimmy