Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Global temporary tables v pl/sql tables

Re: Global temporary tables v pl/sql tables

From: Martin Doherty <martin.doherty_at_oracle.com>
Date: Wed, 10 Jul 2002 15:57:42 -0700
Message-ID: <3D2CBBE6.95DDC58F@oracle.com>


In Oracle 7.3, global variable declarations were instantiated the first time the package is invoked or referred to during the session. They will then persist for the duration of the session. The difference being, if the global variable is declared in the spec, it is public and may be referred to directly by other procedures from outside the package. If it is declared in the body, then it is private to the package and can only be accessed by procedures/functions within the same package.

(I'm guessing this behavior has not changed in subsequent releases ???)

Martin Doherty

Keld Nielsen wrote:

> "Charlie Edwards" <Charlie3101_at_hotmail.com> wrote
> in message
> news:db479d88.0207090250.2ce2e835_at_posting.google.c
> om...
> > "Keld Nielsen" <keldnielsen_at_image.dk> wrote in
> message
> news:<M8iW8.157$s76.32298_at_news010.worldonline.dk>.
> ..
> > > Hi Richard,
> > > Just a small comment (embedded)
> > >
> > > "Richard Foote" <richard.foote_at_bigpond.com>
> wrote
> > > in message
> > >
> news:TsgW8.30694$Hj3.92875_at_newsfeeds.bigpond.com..
> > > .
> > > > Hi
> > > >
> > > > Just to add to what Robert has said.
> > > >
> > > > A GTT is actually quite permanent, it's the
> data
> > > within it that's temporary
> > > > (either at a transaction or session level).
> > > Therefore you can potentially
> > > > access data in a GTT through many PL/SQL
> block
> > > executions.
> > > >
> > > > A PL/SQL table is temporary, period, as it's
> a
> > > memory structure that exists
> > > > only for the life of the PL/SQL block.
> > >
> > > if it's created in a package body (or spec) it
> > > will exist during
> > > the life of a session - or am I wrong here ?
> > >
> >
> > It needs to be DECLARED in the package spec to
> last the session.
> >
> > CE

>

> CREATE OR REPLACE PACKAGE x AS
>

> PROCEDURE y;
> end;
> /
> CREATE OR REPLACE PACKAGE BODY X AS
> TYPE emp_typ IS TABLE OF emp%ROWTYPE
> INDEX BY BINARY_INTEGER;
> emp_tab emp_typ;
>

> PROCEDURE y IS
> CURSOR emp_cur IS
> SELECT * FROM emp
> ORDER BY empno;
> ...
>

> so the table has been declared, but perhaps not
> DECLARED !
>

> why has the table been declared in the body, do
> you think ?

Received on Wed Jul 10 2002 - 17:57:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US