Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Anonymous TABLE or VARRAY type in Oracle

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

like image 377
Lukas Eder Avatar asked Jan 09 '12 08:01

Lukas Eder


People also ask

What is difference between Varray and nested table in Oracle?

Differences Between Varrays And Nested TablesA Varray which is stored in a database maintains its subscripts and sequence. It is always maintained as a single object. Whereas, the nested tables are used when the count of the number of elements is not restricted.

What is a Varray in Oracle?

VARRAY stands for the variable-sized array. A VARRAY is single-dimensional collections of elements with the same data type. Unlike an associative array and nested table, a VARRAY always has a fixed number of elements(bounded) and never has gaps between the elements (not sparse).

What are the types of tables in Oracle?

The most common type of table in an Oracle database is a relational table, which is structured with simple columns similar to the employees table. Two other table types are supported: object tables and XMLType tables. Any of the three table types can be defined as permanent or temporary.

What is the difference between record type and table type in Oracle?

RECORD and TABLE are user-defined composite types that can be defined by the programmer. The record type allows the defination of a type that consists of specified fields. Once the type has been defined, you can then define a specific name for the type as it is being used, a variable name that references the type.


2 Answers

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.

like image 193
APC Avatar answered Oct 14 '22 05:10

APC


SQL table and varray types

An interesting solution was given by user APC here. 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
like image 27
Lukas Eder Avatar answered Oct 14 '22 04:10

Lukas Eder