CODEWITHSUNDEEP
oracle-databaseplsqlpackage
user7765387
user7765387

Reputation: 27

Where do we store the global variables which are declared in the package specification in PLSQL

In PLSQL where do we store the global variables declared in the package specification

Upvotes: 1

Views: 1283

Answers (1)

Jon Heller
Jon Heller

Reputation: 36902

Oracle stores global variables in memory structures that are part of the Program Global Area. You can read about the PGA in the Memory Architecture chapter of the Oracle Concepts Guide.

Variables are only accessible through PL/SQL, we can't access them through data dictionary views.

Session variables can only fit in PGA, they cannot spill to disk, so we may have to be careful about loading too much data. We need to avoid storing large tables in variables, which we can often do by processing cursors with FOR loops or using a LIMIT clause.

For example, the following code loads a lot of data into a simple collection.

--Load 20,000 large strings.  Takes about 10 seconds.
declare
    type string_nt is table of varchar2(4000);
    v_strings string_nt := string_nt();
begin
    for i in 1 .. 20000 loop
        v_strings.extend;
        v_strings(v_strings.count) := lpad('A', 4000, 'A');
    end loop;
    null;
end;
/

We can't view the variable data in the data dictionary, but we can check the size of the data through the data dictionary. In this case, it takes about 105MB of memory to store 80MB of raw data:

--Maximum session PGA memory.
select value/1024/1024 mb
from v$sesstat
join v$statname
    on v$sesstat.statistic# = v$statname.statistic#
where v$statname.name = 'session pga memory max'
order by value desc;

(My answer is based on the assumption that you're asking because you're worried about storing lots of data. If my assumption is wrong, please update the question to explain precisely what you're looking for.)

Upvotes: 2

Related Questions