SQL & PL/SQL

Hi ,

Oracle version 11.1.0.7.

I want to make use of rotating table option(i.e Insert into TEMP table and rename the ORIGINAL table as OLD table and again rename TEMP to ORIGINAL and rename the OLD table to TEMP table finally truncate the TEMP table).

For achieving this ... i have created a procedure like below

Create or replace procedure refresh_tab1 AS
BEGIN 

EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB1_TEMP';

INSERT INTO SELECT T2.COL1,T3.COL2,T4.COL3 from TAB2 T2,TAB3 T3,TAB4 T4;

EXECUTE IMMEDIATE 'ALTER TABLE TAB1 RENAME TO TAB1_OLD';
              
EXECUTE IMMEDIATE 'ALTER TABLE TAB1_TEMP RENAME TO TAB1';
               
EXECUTE IMMEDIATE 'ALTER TABLE TAB1_OLD RENAME TO TAB1_TEMP';
                
EXECUTE IMMEDIATE 'TRUNCATE TABLE TAB1_TEMP';

END;

after executing this procedure ... all the Dependent objects( Procedures) all become stale( UN compiled state). Can some one give me any idea to accomplish this?



You guys a have a great day.
Thanks.
Mike