Execute Immediate With Renames Returning ORA-04068

From: absinth <absinth_at_gmail.com>
Date: 4 Aug 2005 17:41:48 -0700
Message-ID: <1123202508.885055.75310@g14g2000cwa.googlegroups.com>

I have a procedure with the following code in the body of the function:

EXECUTE IMMEDIATE 'TRUNCATE TABLE PREVIOUS';
EXECUTE IMMEDIATE 'RENAME CX_DVA_PREVIOUS TO TEMP';
EXECUTE IMMEDIATE 'RENAME CURRENT TO PREVIOUS';
EXECUTE IMMEDIATE 'RENAME TEMP TO CURRENT';

COMMIT; Basically what my procedure does is shuffle a current table to --> previous and clear out the current table.

But executing this procedure in rapid succession gives me: SQL> exec interface.shuffle_current_table;

PL/SQL procedure successfully completed.

SQL> exec if_dva_inbound.shuffle_current_table; BEGIN interface.shuffle_current_table; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "INTERFACE.INBOUND" has been invalidated
ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1 Received on Thu Aug 04 2005 - 19:41:48 CDT