sql - Anonymous TABLE or VARRAY type in Oracle

Question

In Oracle, I would sometimes like to create constructs such as these ones

SELECT * FROM TABLE(STRINGS('a', 'b', 'c'))
SELECT * FROM TABLE(NUMBERS(1, 2, 3))

Obviously, I can declare my own types for the above. I can choose between TABLE and VARRAY. For example:

CREATE TYPE STRINGS AS TABLE OF VARCHAR2(100);
CREATE TYPE NUMBERS AS VARRAY(100) OF NUMBER(10);

In this particular case, another solution is to write things like

SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL

But I may have more complex examples where I will really need a TABLE / VARRAY type. So what if my SQL is running on an unknown system where I cannot create types because I may not have the necessary grants?

So my question is: Does Oracle know "anonymous" TABLE / VARRAY types that are available on any Oracle instance? Similar to Postgres / H2 / HSQLDB's simple ARRAY types?

UPDATE: I am mostly running this SQL from Java, if this is relevant. No need to explain PL/SQL to me, I'm really just looking for anonymous SQL array types (i.e. "anonymous" standalone stored types). If they don't exist at all, the answer is NO

Answer 1

Providing you're not scared of explicitly referencing the SYS schema there are a few. Here are some I use quite often (well odcivarchar2list not so much, as it chews up a lot of memory: for strings I prefer dbms_debug_vc2coll).

SQL> desc sys.odcinumberlist
 sys.odcinumberlist VARRAY(32767) OF NUMBER

SQL> desc sys.odcivarchar2list
 sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)

SQL> desc sys.ODCIDATELIST
 sys.ODCIDATELIST VARRAY(32767) OF DATE

SQL> desc sys.dbms_debug_vc2coll
 sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)

SQL> 

However, if those aren't sufficient for your needs run this query to find some more:

select type_name
       , owner
from all_types
where typecode = 'COLLECTION'
and owner != user
/

Of course, this result will vary from database to database. For instance a lot of the colllections on my database are owned by XDB and not every system will have that installed. The four I listed at the the top of this answer should be available on every database since 9iR2 (and perhaps early) although they are not always documented in earlier versions.

---

"Note that ALL_COLL_TYPES seems to be an even better dictionary view to find appropriate types"

That's a good point. We can also filter on COLL_TYPE to winnow out the VARRAYs. That view was introduced into 10g whereas ALL_TYPES was available on 9i. As with most things Oracle, the later the version the more functionality it has.