CODEWITHSUNDEEP
javasqloracle-databaseplsql
user3119949
user3119949

Reputation: 31

How is stateful pl/sql package defined?

my java users are getting 'unit cannot be found' errors yet my package is VALID. Another post suggested the root cause: my package must not be stateless. This makes sense, but I am unclear on the real definition of stateless by Oracle:

will it qualify as stateless if I remove all declared types from the SPEC of the package, or do I also have to remove all GLOBAL variables from the body as well?

here is how Oracle defines stateless:

"The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless."

this is the same issue as was raised in this post it appears:

ORA-06508: PL/SQL: could not find program unit being called

Upvotes: 3

Views: 3854

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

will it qualify as stateless if I remove all declared types from the SPEC of the package, or do I also have to remove all GLOBAL variables from the body as well?

From the body as well. The only difference between variables (or constants, or types, etc.) declared in the specification and in the body is that those in the body are private and can only be referred to within the body, while those in the specification can be seen and used externally.

The documentation also mentions that compile-time constants do not make a package stageful from 11gR2, but since you're talking about variables that probably isn't too helpful.

(Hopefully by 'global' you just mean variables that are declared outside a procedure or function; those variables are still restricted to a single session, they aren't global across sessions, as that label might imply).

You may be able to reset any JDBC connections for running programs before they encounter the error, which would let them carry on, but it depends on what you're doing. In WebLogic for example, after recompiling a stateful package I can reset the connection pool, which closes all existing connections and opens new ones - which have fresh sessions and therefore fresh package instantiations.

If a session does encounter the error, and doesn't terminate as a result, then the next package call within that session should get a fresh version of the package and carry on too; but that could still be an issue an have side-effects. Though if you have a connection pool, the same program could see the error multiple times as it keeps getting a new connection from the pool and its session hits the state issue. Eventually all the connections in the pool should either get a fresh version, or be terminated and replaced.

As @pedantic commented, and I neglected to make clear, this is only ever going to be appropriate in development environments where the short and repeated update cycle makes it necessary; even with stateless packages you shouldn't really be deploying changes to a live system while it's being used.

Upvotes: 5

Related Questions