Anonymous TABLE or VARRAY type in Oracle

ghz 1years ago ⋅ 3283 views

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

SQL table and varray types

An interesting solution was given by user [APC here](https://stackoverflow.com/questions/8785459/anonymous-table-or-varray- type-in-oracle#8786450). For future readers of this question, it may be interesting to see that this query provides what I'm really interested in:

select coll_type, elem_type_name, type_name, length, upper_bound
from all_coll_types
where owner = 'SYS'
and elem_type_name IN ('VARCHAR2', 'NUMBER')
order by coll_type, elem_type_name, type_name;

Resulting in (in Oracle 11g):

+-------------+--------------+----------------------+------+-----------+
|COLL_TYPE    |ELEM_TYPE_NAME|TYPE_NAME             |LENGTH|UPPER_BOUND|
+-------------+--------------+----------------------+------+-----------+
|TABLE        |NUMBER        |KU$_OBJNUMSET         |{null}|     {null}|
|TABLE        |NUMBER        |KU$_XMLCOLSET_T       |{null}|     {null}|
|TABLE        |NUMBER        |ORA_MINING_NUMBER_NT  |{null}|     {null}|
|TABLE        |VARCHAR2      |DBMS_AW$_COLUMNLIST_T |   100|     {null}|
|TABLE        |VARCHAR2      |DBMS_DEBUG_VC2COLL    |  1000|     {null}|
|TABLE        |VARCHAR2      |HSBLKNAMLST           |    30|     {null}|
|TABLE        |VARCHAR2      |KU$_VCNT              |  4000|     {null}|
|TABLE        |VARCHAR2      |ORA_MINING_VARCHAR2_NT|  4000|     {null}|
|VARYING ARRAY|NUMBER        |AWRRPT_NUM_ARY        |{null}|         30|
|VARYING ARRAY|NUMBER        |JDM_NUM_VALS          |{null}|        999|
|VARYING ARRAY|NUMBER        |ODCIGRANULELIST       |{null}|      65535|
|VARYING ARRAY|NUMBER        |ODCINUMBERLIST        |{null}|      32767|
|VARYING ARRAY|NUMBER        |SQL_OBJECTS           |{null}|       2000|
|VARYING ARRAY|NUMBER        |TABLESPACE_LIST       |{null}|      64000|
|VARYING ARRAY|VARCHAR2      |AQ$_JMS_NAMEARRAY     |   200|       1024|
|VARYING ARRAY|VARCHAR2      |AQ$_MIDARRAY          |    32|       1024|
|VARYING ARRAY|VARCHAR2      |AWRRPT_VCH_ARY        |    80|         30|
|VARYING ARRAY|VARCHAR2      |DBMSOUTPUT_LINESARRAY | 32767| 2147483647|
|VARYING ARRAY|VARCHAR2      |DBMS_XS_ROLELIST      |  1024|       4096|
|VARYING ARRAY|VARCHAR2      |FLASHBACKTBLIST       |    30|        100|
|VARYING ARRAY|VARCHAR2      |HSBLKVALARY           |  4000|        250|
|VARYING ARRAY|VARCHAR2      |JDM_ATTR_NAMES        |    60|        999|
|VARYING ARRAY|VARCHAR2      |JDM_STR_VALS          |  4000|        999|
|VARYING ARRAY|VARCHAR2      |KU$_DROPCOLLIST       |  4000|       1000|
|VARYING ARRAY|VARCHAR2      |KUPC$_LOBPIECES       |  4000|       4000|
|VARYING ARRAY|VARCHAR2      |ODCIRIDLIST           |  5072|      32767|
|VARYING ARRAY|VARCHAR2      |ODCIVARCHAR2LIST      |  4000|      32767|
|VARYING ARRAY|VARCHAR2      |RE$NAME_ARRAY         |    30|       1024|
|VARYING ARRAY|VARCHAR2      |RE$RULE_LIST          |    65|       1024|
|VARYING ARRAY|VARCHAR2      |SQLPROF_ATTR          |   500|       2000|
|VARYING ARRAY|VARCHAR2      |TXNAME_ARRAY          |   256|        100|
+-------------+--------------+----------------------+------+-----------+

It looks as though ORA_MINING_NUMBER_NT and ORA_MINING_VARCHAR2_NT will be the best match for my needs.

PL/SQL indexed array types

If using Oracle 12c and PL/SQL, there's also the possibility to use any of the DBMS_SQL types, which can be unnested using the TABLE(..) constructor. There are:

  • DBMS_SQL.CLOB_TABLE
  • DBMS_SQL.BINARY_FLOAT_TABLE
  • DBMS_SQL.BINARY_DOUBLE_TABLE
  • DBMS_SQL.BLOB_TABLE
  • DBMS_SQL.BFILE_TABLE
  • DBMS_SQL.DATE_TABLE
  • DBMS_SQL.NUMBER_TABLE
  • DBMS_SQL.UROWID_TABLE
  • DBMS_SQL.VARCHAR2_TABLE
  • DBMS_SQL.TIME_TABLE
  • DBMS_SQL.TIME_WITH_TIME_ZONE_TABLE
  • DBMS_SQL.TIMESTAMP_TABLE
  • DBMS_SQL.TIMESTAMP_WITH_LTZ_TABLE
  • DBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLE
  • DBMS_SQL.INTERVAL_DAY_TO_SECOND_TABLE
  • DBMS_SQL.INTERVAL_YEAR_TO_MONTH_TABLE
#oracle