3

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

Cœur
  • 37,241
  • 25
  • 195
  • 267
user3119949
  • 31
  • 1
  • 2
  • 2
    This will happen if an app has an active session open, and a previously referenced package was called, and in the meantime, someone re-compiled the package. Are you doing a lot of re-compiles? – OldProgrammer Dec 19 '13 at 17:14
  • 1
    The answer below sums it up well, but the takeaway is that package deployment should be tied to application deployment. – Pedantic Dec 19 '13 at 19:34

1 Answers1

5

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Alex, oftentimes we are asked to "scrum" updates to production environments and the process of deploying updates cause package state errors. At this point I just restart the server, resetting the connection pool. . You get what you ask for. :) – Pedantic Dec 20 '13 at 03:57
  • yes, this is all well unless you can never take the system down, and are running 7by24 data services which rely on your Oracle packages.. which is my case. We have to re-direct all data service traffic to the failover Oracle host, do the re-compiles, and then move them all back to the primary which is a big pain.. i am looking for more elegant ways if they exist to do this without flushing the pool.. java people do not appreciate that much either.. Thanks for the good ideas - much appreciated! – user3119949 Dec 20 '13 at 04:47
  • Now, back to stateless package definition: if i take out both spec and body types declarations outside of the procedures and functions, would that make it stateless? By 'global' I meant global to the body of the package, and not across Oracle sessions, not the global context in the SGA, not that one..sorry, i bit slow on this.. much appreciate the suggestions! – user3119949 Dec 20 '13 at 04:49
  • A recompile poisons the package state. – Pedantic Dec 20 '13 at 06:02
  • 2
    @user3119949 - type declarations in the spec or body don't make it stateful. And yeah, I realise there can be operational reasons for live updates, they should just be avoided is possible (IMO, anyway). Sometimes you just have to do what you can within the constraints that are imposed on you. Anyway... [this might be useful](http://stackoverflow.com/a/11043498) to check for issues before deploying to live. – Alex Poole Dec 20 '13 at 08:13
  • yes, thanks Alex, I ran that sql and of course my packages have hundreds of variables, and getting rid of them all is not a realistic option.. in fact, i would be curious to ask people how one would retrofit a package not to use any variables, and still make it backward compatible? I will look at editions for this problem, perhaps it will help me with zero downtime deployments – user3119949 Dec 20 '13 at 15:35
  • Hate to chime in so late but it sounds like an issue with Tomcat/JBoss/etc., right? I shouldn't care, as a PLSQL guy / DBA guy whether or not package states are useful, right? – Pedantic Jan 17 '14 at 05:34
  • @Pedantic - not really, you can see the error from any session, in SQL*Plus, Toad, your own OCI program, or whatever. If a package is recompiled, anything that uses a connection pool - and therefore has long persistent sessions - is more likely to be affected, just because of its session, but that's not the container's fault. As a DBA you don't care (though the state consumes memory); as a PL/SQL developer you need to care about whether you really need state and what impact it might have, in your design. (This error can be really annoying in dev, even if your deployment avoids it in prod). – Alex Poole Jan 17 '14 at 07:40