ansaurus

Question

Answer 1

+5 A:

existing state of packages has been discarded means, that your Package had some sort of state.

This is caused by a global variable (or constant) stored in your Package Body.

Since the package has already been used in your session, Oracle assumes that this state is relevant for you. Some of these variables might have different values now, and when you recompile the Body, the values are reset.

This exception is thrown, so that your clients know that they can't rely on those variables any more.

You could try to remove all global variables and constants from the Package Body, or close your session and reconnect before calling the package again.

Peter Lang 2010-03-23 19:05:58

Indeed, I do have some globals! I'll see if I can find a way around that, maybe make them non-globals... Is there a way to have static values for procedures in PL/SQL?

FrustratedWithFormsDesigner 2010-03-23 20:12:17

What exactly do you mean by "static values for procedures"? Those global variables are more or less static variables (per session). The easiest work-around for you would be to put the global variables in the Package Header instead of the Body - state will not be invalidated any more when compiling the Body. Why do you need those globals?

Peter Lang 2010-03-23 20:20:13

They hold configuration values that only need to be set once and drive the rest of the system. I didn't want to put them into the header because I didn't want any other code to have access to them (they get passed into the one relevant procedure from a script). I suppose they could be stored in a table, but there could be a LOT of reads on that table if I did it.

FrustratedWithFormsDesigner 2010-03-23 20:28:21

Guess I would have put configuration into the Body too. Reading it from a table might slow down your process. Are you still going to recompile your Body in future or is this only now for testing? One other way would be to define a `RECORD` containing all necessary variables and pass it to the procedures that you call.

Peter Lang 2010-03-23 20:49:57

@Peter Lang: I think for now I will just read from the table. I've rewritten part of the affected code so I don't think it will have too many reads, so probably not major performance problem now. I'm only going through all this recompiling for dev/test, so this shouldn't be an issue in production systems.

FrustratedWithFormsDesigner 2010-03-23 20:55:46

Answer 2

+4 A:

If you're running stuff in a script try these commands in there prior to running the re-compiled code..

exec DBMS_SESSION.RESET_PACKAGE
exec DBMS_SESSION.MODIFY_PACKAGE_STATE( DBMS_SESSION.REINITIALIZE )

They do what the name might suggest.

Paul James 2010-03-24 08:53:56

Interesting... I'll have to try this.

FrustratedWithFormsDesigner 2010-03-24 13:33:31

Man, I wish I could mark two answers as the accepted answer. One of the other devs here said this works fine, and it means I don't have to worry about re-writing and re-testing code.

FrustratedWithFormsDesigner 2010-03-24 15:03:40

ansaurus

tags:

views:

answers:

PL/SQL Package invalidated

related questions