Editor | "DBMS Output" tab | "Turn Output On" (or Off) icon stops working after re-compiling a Package. The green turn output On icon needs to be clicked again even though it was clicked On already. The Output icon stays at Green, but it will have to be clicked Red and then Green again for it to work.
Error:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OE.TEST1" has been invalidated
ORA-04065: not executed, altered or dropped package body "OE.TEST1"
ORA-06508: PL/SQL: could not find program unit being called: "OE.TEST1"
ORA-06512: at line 2
Steps to Replicate:
A)
Session 1 in TOAD, create this package spec and body:
CREATE OR REPLACE PACKAGE test1
IS
gv_output NUMBER := 0;
FUNCTION test1
RETURN VARCHAR2;
END test1;
CREATE OR REPLACE PACKAGE BODY test1
IS
FUNCTION test1
RETURN VARCHAR2
IS
BEGIN
RETURN gv_output;
END test1;
END test1;
B)
Session 2 in Toad. Open a new Toad instance. Connect to the same database. Execute the package.function like this, with dbms_output enabled (DBMS Output Tab in Editor – Turn Output to On (Green round icon):
BEGIN
dbms_output.put_line (test1.test1);
END;
You will see the dbms_output.
C)
Now, go back to Toad Session 1, change this line:
RETURN gv_output;
To this:
RETURN gv_output + 1;
Compile the package body.
Go back to Toad Session 2 (the other Toad instance), execute the code again. You will receive the errors below, and you will not receive a dbms_output until you click and un-click the button to disable and re-enable dbms_output (click the icon so that it will turn Red and then Green again).
Error:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OE.TEST1" has been invalidated
ORA-04065: not executed, altered or dropped package body "OE.TEST1"
ORA-06508: PL/SQL: could not find program unit being called: "OE.TEST1"
ORA-06512: at line 2
This is an Oracle bug. It has been logged a few times in Oracle's system for as far back as Oracle 7.3.3 and most recently 11g R2. The bugs are not yet addressed by Oracle. In My Oracle Support (formerly Metalink), search for the following two issues for more information.
Bug 560143 : DBMS_OUTPUT DISABLED AFTER A ORA-4068 OF EXECUTE PACKAGE
and
Bug 11720497 : SERVEROUT VARIABLE RESET TO DEFAULT IF TWO SESSIONS EXECUTE PL/SQL SEQUENCE
WORKAROUND:
Click the Turn Output icon twice so that it will turn Red (off) and then Green (On) again. This will allow it to work as normal again.
