12

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?

Hash
  • 4,647
  • 5
  • 21
  • 39
VVP
  • 121
  • 1
  • 1
  • 3

2 Answers2

18

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.

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
  • 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. – VVP Jun 04 '10 at 23:13
  • The DBA says that "all_source is not usable with flashback". – VVP Jun 07 '10 at 18:39
  • point your DBA to this link from Tom Kyte: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6135698985750#13932884772332 – Ian Carpenter Jun 07 '10 at 18:51
  • 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. – Ian Carpenter Jun 08 '10 at 07:03
  • Also got "insufficient privileges" but logging on as SYS eliminated the error. Thanks!!! – Arturo Hernandez Jan 09 '13 at 21:10
4

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

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • 3
    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. – Brian T Hannan Jun 04 '10 at 21:10
  • 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. – VVP Jun 04 '10 at 23:14
  • 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 – Mark Baker Jun 05 '10 at 07:54
  • 1
    Source control comments aside, the answer is to use flashback as described by @carpenteri – Nick Pierpoint Jun 05 '10 at 23:22