Search This Blog

Sunday, December 11, 2011

Recover older Oracle PL/SQL source code from a package body

Q. 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?

A. 1. Login as sysdba on the server ie. \ as sysdba
     2. Spool on
     3. Put the query
         select text
         from all_source
        as of timestamp
         to_timestamp('11-Sep-2011 09:30:00', 'DD-MON-YYYY HH24:MI:SS')
         where name = 'XX_HR_CONVERSIONS' and type = 'PACKAGE BODY'

Cheersss...

1 comment: