匿名TABLE或VARRAY类型甲骨文 [英] Anonymous TABLE or VARRAY type in Oracle

问题描述

在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))

很显然，我可以宣布我自己的类型上面。我能 TABLE 和 VARRAY 之间进行选择。例如：

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

不过，我可能有更复杂的例子，我真的需要一个 TABLE / VARRAY 键入。所以，如果我的SQL是一个未知的系统，因为我可能没有必要的补助，我不能创建类型上运行的是什么？

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?

所以我的问题是： Oracle是否知道匿名 TABLE / VARRAY 类型上可用的Oracle实例？类似的Postgres / H2 / HSQLDB的简单 ARRAY 类型？

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?

更新：我主要是运行在Java这个SQL，如果这是相关的。没有必要解释PL / SQL给我，我真的只是寻找匿名SQL数组类型（即匿名的独立的存储类型的）。如果他们根本不存在，答案是的 NO 的

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

推荐答案

这是有趣的解决方案是由用户给出的<一个href=\"http://stackoverflow.com/questions/8785459/anonymous-table-or-varray-type-in-oracle#8786450\">APC这里。

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;

致使（中的Oracle 11g）：

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|
+-------------+--------------+----------------------+------+-----------+

看起来好像 ORA_MINING_NUMBER_NT 和 ORA_MINING_VARCHAR2_NT 将是我需要的最佳匹配。

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

这篇关于匿名TABLE或VARRAY类型甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！