Re: Querying varray's contents from another session

One has to distuingish two things - 1) collections (an oracle name for arrays) in pl/sql as pure memory structures and just more complex variables than scalars
2) collections permanently stored in database. There are 3 types of collectons:

1) Associative arrays, also known as index-by tables
2) Nested tables
3) Varrays (short for variable-size arrays)

2) and 3) can be used both in pure pl/sql and stored in db.

Collections in pure pl/sql are just like other variables - session specific and noone other can look at them. Collections stored in db however are just like other data in db as soon as one commits all other sessions can see and query them.

More info in docs are here:
collections in PL/SQL
Using PL/SQL Collections and Records
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm Using PL/SQL With Object Types
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/objects.htm collections in database
Support for Collection Datatypes
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjcol.htm

But don't bee fooled, so called objects in db are nothing more than hidden relational tables (for nested tables) or text columns (for varrays). So I personally don't like collections in DB, however they are very nice of course in pl/sql.
OK now short answers to your specific questions:

> Now my question is: is it possible to query the data constructed
> inside this varray - of which the PL/SQL procedure is still running -
> from another session?

As long as varray is just a variable in pl/sql - no. As soon as you store it in a permanent table and commit - yes.

>
> What is the internal implementation of varray like?

pl/sql varrays - some kind of memory structures I suspect. For varrays stored in db see here
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14260/adobjadv.htm#sthref879

> Temporary tables

(global) Temporary tables are absolutely independent concept and yes they can contain a column which can be varray for example.

> ? How to enumerate all the varrays constructed by different sessions?

If you mean pl/sql construct then you cannot them enumerate just as you cannot enumerate simple variables "a" of type number used by different sessions in the same package for example.

> And list their contents?

As soon as you store them in db and commit you can access them in all sessions. But I warn you - before using collections in db especially with big data amounts test that. Of course as everything other :)

Gints Plivna
http://www.gplivna.eu

--
http://www.freelists.org/webpage/oracle-l