Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL: How to access tables globally.
A copy of this was sent to bwit_at_pobox.com (Bob Withers)
(if that email address didn't require changing)
On Sun, 6 Sep 1998 08:20:10 -0500, you wrote:
>In article <6stlvq$pcu$1_at_pascal.a2000.nl>, avbentem_at_DONT-YOU-DAREdds.nl
>says...
>> >If you define the table in the package spec it will be accessible to
>> >everyone who has access to the package. If you define it globally in the
>> >package body it will only be accessible to the package members. In
>> >either case it will be persistent.
>>
>> Just to be sure, and like Thomas wrote in his reply: they are *session*
>> persistent. So when declared in the package specification, other packages
>> indeed can see the data when being called from the very same session.
>> However, even if declared in the package specification, two different
>> sessions will have their own data in the PL/SQL table.
>
>You're absolutely correct. Sorry, I should have been more explicit. In
>addition, they are only persistent within a session as long as the
>package state is not reset. This could occur by having code within the
>session execute DBMS_SESSION.RESET_PACKAGE or having the package owner
>recompile the package spec while there are sessions using it.
>
Or (in addition to dbms_session and recompiling -- which will raise an ERROR in the other session so they do know their state is gone), in oracle 8.0 and up, by putting a pragma serially_reusable in the package spec:
<quote>
Serially Reusable Packages
To help you manage the use of memory, PL/SQL provides the pragma SERIALLY_REUSABLE, which lets you mark some packages as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server, a PL/SQL client-to-server RPC, or a server-to-server RPC).
The global memory for such packages is kept in a small pool, not in the user global area (UGA). As a result, the memory is used only for one unit of work. Then, the memory is returned to the pool for reuse by another user.
For packages without a body, you code the pragma in the package specification using the following syntax:
PRAGMA SERIALLY_REUSABLE; An example follows:
CREATE PACKAGE SR_Pkg AS
PRAGMA SERIALLY_REUSABLE;
...
END SR_Pkg;
For packages with a body, you must code the pragma in the specification and body. You cannot code the pragma only in the body.
For more information, see Oracle8 Server Application Developer’s Guide.
</quote>
>Thanks for the clarification.
>
>Regards,
>Bob
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Sep 06 1998 - 08:58:47 CDT