Select all rows as one table type object in pure Oracle SQL

Question

I have following table type:

CREATE OR REPLACE TYPE tab_id AS TABLE OF NUMBER(12);

I need to select IDs from table and return them as single tab_id. In PL/SQL I would use BULK COLLECT INTO, but I need it in pure SQL. I need the query to return the tab_id object directly.

I tried:

WITH src AS ( 
SELECT 1 AS token FROM DUAL
UNION ALL
SELECT 2 AS token FROM DUAL
)
SELECT tab_id( token ) FROM src

But this returns two rows of type tab_id, first contains value 1 and second contains value 2. I need the query to return only one row, containing both values in one tab_id.

I also tried following version without success:

WITH src AS ( 
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
)
SELECT tab_id( SELECT * FROM src ) FROM DUAL -- I got ORA-00936
SELECT tab_id( ( SELECT * FROM src ) ) FROM DUAL -- I got ORA-01427

Is there a way how to select all values into one table type?

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-04-20T15:18:37

You may use COLLECT function with CAST.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE TYPE tab_id AS TABLE OF NUMBER(12);

Query 1:

WITH src
AS (
    SELECT 1 AS token FROM DUAL
      UNION ALL
    SELECT 2 AS token  FROM DUAL
    )
SELECT CAST(COLLECT(token) AS tab_id)
FROM src

Results:

| CAST(COLLECT(TOKEN)ASTAB_ID) |
|------------------------------|
|                          1,2 |

Select all rows as one table type object in pure Oracle SQL

1 Answers