DBMS Output not working after ORA-04068

Toad for Oracle - Beta
1

DBMS Output still looks enabled, but it does not work after
ORA-04068: existing state of packages has been discarded.

Is there a way to re-enable DBMS Output automatically or at least display it as disabled?

Steps to reproduce

Session 1

  • Enable DBMS Output

    CREATE PACKAGE package_with_state AS
  global_variable NUMBER;
END package_with_state;

  • EXEC package_with_state.global_variable := 1;

Session 2

  • CREATE OR REPLACE PACKAGE package_with_state AS
  global_variable NUMBER := 2;
END package_with_state;

Session 1

  • EXEC dbms_output.put_line( package_with_state.global_variable );
    • Exception as expected:

      ORA-04068: existing state of packages has been discarded

  • EXEC dbms_output.put_line( package_with_state.global_variable );
    • :exclamation: DBMS Output still displayed as enabled, but nothing printed

  • Disable and enable DBMS Output

  • EXEC dbms_output.put_line( package_with_state.global_variable );
    • Works as again
2

This is still an issue with the current beta I stumbled across it a number of times. :wink:
I was about file a topic on my own, but this one popped up when I typed in the description. :slight_smile:

3

Thanks Dirk and Peter. I've logged this so we won't forget about it again.
@PeterLang thanks for the nice formatting and clear steps of how to reproduce it. It really helps.

4

@PeterLang @dirk.mika1
I've spent some time looking at this. I don't think it's really a Toad bug. If you turn on spool SQL, you'll see that we're still executing the commands to fetch the dbms_output, but there isn't any unless you disable and enable output. I'll make a change to do that automatically after an ORA-04068, but I suspect there may be other errors that could cause this to happen. If y'all notice any, let me know.

5

Thanks, John!

I think the ORA-04068 is very special, so automatically re-enabling dbms_output if it was enabled should go a long way.

6

You're welcome. And sorry it took 2 years on this one. Sometimes we get busy and don't reply right away, then threads get buried and forgotten. Feel free to remind us after a week or so if you don't get a response.

7

The reason why the behavior with error ORA-04068 is so special is that the DBMS_OUTPUT package stores all data in global variables. These are reset with the ORA-04068, as in all other packages.