replacement for cast and collect in oracle

Question

I want to convert the below statement into normal query.

SELECT CAST(COLLECT(warehouse_name ORDER BY warehouse_name) AS warehouse_name_t) "Warehouses" FROM warehouses;

How to do this?

I tried some of the things but could not succeed. Please help.

The current query will give you a collection of names, as whatever data type `warehouse_name_t` is - e.g. a nested table. What do you mean by a 'normal query' - what output do you want instead? If you still want a collection then explain what the problem is with this code. If you don't want a collection then just query the column from the table directly - `select warehouse_name from warehouses`. — Alex Poole, Sep 05 '22 at 08:33
`CAST` and `COLLECT` are standard built-in Oracle function so your query is a "normal query". Please [edit] the question to explain what your expected outcome would be if you do not use `CAST` and `COLLECT`? — MT0, Sep 05 '22 at 08:34
When you write *I've tried but **it doesn't work/with no success*** please clarify the meaning of "doesn't work" or "no success": any error messages, unexpected result or something that makes you think it is not what you expect — astentx, Sep 05 '22 at 09:07

MT0 · Answer 1 · 2022-09-05T08:45:30.217

If you want ANSI SQL and do not want a collection but want the values as rows:

SELECT warehouse_name 
FROM   Warehouses
ORDER BY warehouse_name

If you want to aggregate the rows into a single row and want a delimited single string then use LISTAGG:

SELECT LISTAGG(warehouse_name, ',') WITHIN GROUP (ORDER BY warehouse_name)
         AS warehouses
FROM   Warehouses

If you want a collection data-type then CAST and COLLECT are standard built-in functions and are exactly what you should be using:

SELECT CAST(
         COLLECT(warehouse_name ORDER BY warehouse_name)
         AS warehouse_name_t
       ) AS Warehouses
FROM   warehouses;

db<>fiddle here

1 Answers1