DB: Oracle 11gR2
Platform: windows 7 client
Hello,
I have an array of multilevel nested-table object.
{code}
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;
{code}
I want to convert to it to a table format - which I need to feed to some program. intended output:
--------------------------------------------------------------------------------
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
On Toad the following query creates the output:
{code}
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)
{code}
Where (DATASET) is the internal nested-table. So I thought adding one level to 'table function' will do the job, but NO, getting error.
{code}
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
{code}
Any suggestion?
Thanks.