Articles for the Month of October 2014

[ORACLE] Use Flashback Query to Retrieve the Original Source Code of a Dropped/Altered Object

You might end up altering or dropping a PROCEDURE, PACKAGE, FUNCTION, without taking a backup first and you need to roll back! I was recently in that position, and I had to do a little search to figure out how to do it. I found a post  on uhesse.com but I had to update it a bit to fit my needs. Basically you can use Flashback Query to get the earlier version.

So all the source codes are in DBA_SOURCE view under SYS schema, so you have to make sure that you connect with a user that has SYSDBA privilege. If you do a SELECT TEXT on this view, you can get the current source code:

SELECT text FROM dba_source WHERE name='PKG_NAME' ORDER BY type, line;

But you need the source code from a couple of minutes ago, so you need to run the following statement.

SELECT text FROM dba_source
     AS OF TIMESTAMP systimestamp - interval '5' minute     --UPDATE THIS TIME TO GET THE FLASHBACK QUERY
     WHERE name='PKG_NAME'
     ORDER BY type, line;
TEXT
--------------------------------------------------------------------------------
PACKAGE PKG_NAME
     AUTHID CURRENT_USER
AS

     PROCEDURE setInsGasDl_Ind;
     PROCEDURE setPremise_Ser_Addr;
     PROCEDURE DLM_Rmv_Dup_DeviceNumber;
     PROCEDURE Publish_To_SRM;
END PKG_NAME;
PACKAGE BODY PKG_NAME
AS

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

     PROCEDURE setInsGasDl_Obsolete_Ind
     IS
     BEGIN
          COMMUDF.print_msg (
               'Set INSTALLED_LOCATION OBSOLETE_INDICATOR');

          UPDATE installed_location
               SET OBSOLETE_INDICATOR = 'Y', RECORD_UPDATE_DATE = SYSTIMESTAMP
          WHERE OBSOLETE_INDICATOR IS NULL
               AND (INSTALLATION_NUMBER, LOCATION_NUMBER) IN
     ....
     ....

The problem is you can’t really run it directly as there is no CREATE OR REPLACE before the name of the package. Also, you have the column header in the middle of the code, which makes it totally useless! So, let’s use Oracle’s DECODE to take care of the first issue and then set some environment variables to take care of the second issue.

set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off

select decode( type||'-'||to_char(line,'fm99999'),
               'PACKAGE BODY-1',
               '/'||chr(10),
               null)
          || decode(line,1,'CREATE OR REPLACE ', '' )
          || text text
     from dba_source
     as of timestamp systimestamp - interval '5' minute --UPDATE THIS TIME TO GET THE FLASHBACK QUERY
     where name = upper('PKG_NAME')
     order by type, line;

 

CREATE OR REPLACE PACKAGE PKG_NAME
     AUTHID CURRENT_USER
AS
     PROCEDURE setInsGasDl_Ind;
     PROCEDURE setPremise_SerAddr;
     PROCEDURE DLM_Rmv_Dup_DeviceNumber;
     PROCEDURE Publish_To_SRM;
END PKG_NAME;
/
CREATE OR REPLACE PACKAGE BODY PKG_NAME

AS
     PROCEDURE setInsGasDl_Obsolete_Ind
     IS
     BEGIN
          COMMUDF.print_msg (
               'Set INSTALLED_LOCATION OBSOLETE_INDICATOR');

          UPDATE installed_location
               SET OBSOLETE_INDICATOR = 'Y', RECORD_UPDATE_DATE = SYSTIMESTAMP
          WHERE OBSOLETE_INDICATOR IS NULL
               AND (INSTALLATION_NUMBER, DEVICE_NUMBER) IN
     ....
     ....

So here you have it. Try it and let me know if it works for you too.

Cheers,