Sokrates on Oracle

Archive for the ‘7.3.3’ Category

A Bug which survived from 7.3.3

Posted by Matthias Rogel on 9. June 2011

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.

Posted in 11.2, 7.3.3, sqlplus | 1 Comment »