Search code examples
sqloracle-databaseplsqloracle19c

How to remove collection type name from results

When I run this query

create type type_4 as table of varchar2(20) ;

SELECT
    department_id,
    CAST(COLLECT(first_name) AS type_4) names
FROM employees 
GROUP BY department_id;

I am getting below result

enter image description here

My question is How do i remove "HR.TYPE_4" from the result because its irrelevant and also

why its (HR.TYPE_4)coming in the result set because i am just giving a simple select.

Solution

  • why its (HR.TYPE_4)coming in the result set because I am just giving a simple select.

    Because you collected the names into a nested table collection. The user interface you are using is helpfully displaying all the relevant information including the data type of the collection and the values in the collection.

    You may not find it helpful but the developers of whatever user interface you are using decided that that was the most informative way of displaying a collection to the user. You can investigate if there are any preferences that you can set on the user interface to change how it displays it or you can look at alternative methods of formatting the data.

    If you just want a list of names then you do not need a collection and can use LISTAGG to get a single formatted string:

    SELECT department_id,
       LISTAGG(first_name, ',') WITHIN GROUP (ORDER BY first_name) AS names
FROM   employees 
GROUP BY department_id;