将多级嵌套表对象数组格式化为表

Question

数据库: Oracle 11gR2

平台: windows 7客户端

你好,

I have an array of multilevel nested-table object.

CREATE OR REPLACE TYPE  OBJ_1 AS OBJECT (
  col_1            NUMBER,
  col_2           VARCHAR2(56),
  col_3         VARCHAR2(256)
);

CREATE OR REPLACE TYPE  ARR_1 AS TABLE OF  OBJ_1;

CREATE OR REPLACE TYPE  OBJ_2 AS OBJECT (
 col_4           NUMBER,
 col_5         NUMBER(15),
 col_6         NUMBER(1),
 col_7         NUMBER(1),
 col_8         VARCHAR2(56),
 col_arr       ARR_1
);

CREATE OR REPLACE TYPE  ARR_2 AS TABLE OF  OBJ_2;

我想要将其转换为表格格式-我需要将其提供给某个程序。预期输出：

------------------------------------------------------------------------
col_4    col_5    col_6    col_7    col_8    col_1    col_2    col_3
------------------------------------------------------------------------
1        2        3         4        5        1        2        3
1        2        3         4        5        4        5        6

在Toad上，以下查询将创建输出：

select *
from table(ARR_2(OBJ_2(1, 2, 3, 4, '5', ARR_1(OBJ_1(1,2,'3'))) ,
                 OBJ_2(1, 2, 3, 4, '5', ARR_1(OBJ_1(4,5,'6'))) ) )

------------------------------------------------------
col_4    col_5    col_6    col_7    col_8    col_arr
------------------------------------------------------
1        2         3        4        5       (DATASET)
1        2         3        4        5       (DATASET)

其中(DATASET)是内部嵌套表。所以我认为在“表函数”中增加一个级别就可以了，但是没有，但是出错了。

select * from table(
select *
from table(ARR_2(OBJ_2(1, 2, 3, 4, '5', ARR_1(OBJ_1(1,2,'3'))) ,
                 OBJ_2(1, 2, 3, 4, '5', ARR_1(OBJ_1(4,5,'6'))) ) )
               );

Error: ORA-02324: more than one column in the SELECT list of THE subquery

有什么建议吗？

谢谢。

Answer 1

select col_4, col_5, col_6, col_7, col_8, col_1, col_2, col_3
from
(
    select col_4, col_5, col_6, col_7, col_8, col_arr
    from table(ARR_2(OBJ_2(1, 2, 3, 4, '5', ARR_1(OBJ_1(1,2,'3'))) ,
                     OBJ_2(1, 2, 3, 4, '5', ARR_1(OBJ_1(4,5,'6'))) ) )
)
cross join
table(col_arr)