One of the most annoying bugs I hit almost daily is over 14 years of age and was filed against 7.3.3
It is an sqlplus issue:
serverout on stops working when you receive
ORA-04068: existing state of packages has been discarded
See (using SQL*Plus: Release 11.2.0.1.0 Production ):
Session 1
sokrates1 > set serverout on
sokrates1 > create package abc is x number; end abc;
2 /
Package created.
sokrates1 > exec abc.x := 17 ; dbms_output.put_line(abc.x)
17
PL/SQL procedure successfully completed.
Now, in Session 2 (same db, same user), we do
sokrates2 > create or replace package abc is x number; y number; end abc;
2 /
Package created.
and then, back in Session 1:
sokrates1 > exec abc.x := 16; dbms_output.put_line(abc.x)
BEGIN abc.x := 16; dbms_output.put_line(abc.x); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SOKRATES.ABC" has been invalidated
ORA-04065: not executed, altered or dropped package "SOKRATES.ABC"
ORA-06508: PL/SQL: could not find program unit being called: "SOKRATES.ABC"
ORA-06512: at line 1
we receive ORA-04068, that’s ok and expected.
But now:
sokrates1 > show serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
sokrates1 > exec abc.x := 16; dbms_output.put_line(abc.x)
PL/SQL procedure successfully completed.
sokrates1 > REM oops - serverout is ON, but dbms_output.put_line does not display something on my screen
sokrates1 > exec dbms_output.put_line('serverout is on, why do you don''t want to talk to me ?')
PL/SQL procedure successfully completed.
sokrates1 > show serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
sokrates1 > exec dbms_output.put_line('are we friends again ?')
PL/SQL procedure successfully completed.
sokrates1 > set serverout on
sokrates1 > exec dbms_output.put_line('now we are friends again')
now we are friends again
PL/SQL procedure successfully completed.
After receiving ORA-04068, serverout ON is ignored. You have to manually set it again in order to work.
This bug was filed more than 14 years ago ( ID 560143 ) against sqlplus 7.3.3 ( and still reproduces under 11.2 )
Bug 11720497 was filed by me.