| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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 "S Green" <twogreens_at_sprynet.com>
(if that email address didn't require changing)
On Sat, 5 Sep 1998 00:18:30 -0500, you wrote:
>I want to share pl/sql table data between pl/sql modules. All the modules
>are in a common procedure.
>If I define a table in the package, will it be
>accessible to all modules.
If you put the table definition in a package specification, it will be a global variable available to all other functions/procedures. For example:
create or replace package my_globals
as
type vcArray is table of varchar2(255) index by binary_integer;
my_big_array vcArray;
end;
/
Now, other functions/procedures can access:
...
begin
...
dbms_output.put_line( My_Globals.My_Big_Array(5) );
...
end;
for example...
>Do I need to pass it (it will be very big) as a
>parameter?
No, see above
>Will the data persist within the package. ie, if i set values
>in one procedure and it completes will the values persist in the table.
PL/SQL global variables (variables declared in either a package SPEC or BODY outside of any function/procedure) are session persistent. They are initialized when the package is first loaded in a given user session, they retain their values until you log off.
So yes, if procedure1 modifies my_globals.my_big_array, procedure2 will be able to see that change (unless you've logged off/in between calls)
>What about row locking. Could two modules change the same row concurrently.
Since pl/sql doesn't support tasking, this is not an issue. there is only one thread of execution in pl/sql -- its quite linear.
What you may be concerned with is "in a multi-user environment how do I coordinate access to this structure". the answer is simply -- you don't have to, each and every session shares the pl/sql CODE however each and every session (user logged in) has its own data segment. pl/sql is run in a environment much like a mini operating system. the database will share code segments but not data segments.
if you want two sessions to share data and you don't want to use database tables, you must use some inner process communication (IPC) mechanism such as dbms_pipes, dbms_alerts to send and recieve data from session to session. Think of each database session as if it was a separate unix process -- to share data you must use a file (table), sockets (pipes), or signals (alerts), same in pl/sql.
>
>
>TIA
>
>Scott
>
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 Sat Sep 05 1998 - 09:14:49 CDT
 |
 |