Oracle PL/SQL query nested table collection and value key name

Question

I am looking to cast a nested table so i can use a query to reorder the values. As there isn't a key name I am wondering what the column name would be?

I know this isn't the correct syntax but it's an illustration of what I'm looking to achieve.

CREATE OR REPLACE TYPE a_nested_table AS TABLE OF VARCHAR2(20);

CREATE OR REPLACE FUNCTION my_func RETURN VARCHAR2 IS
    output VARCHAR2;
    list a_nested_table := a_nested_table('foo', 'bar');
BEGIN
    FOR current_record IN( 
        SELECT column_name INTO bar
        FROM TABLE(CAST(list AS a_nested_table))
        ORDER BY UPPER(column_name) ASC
    ) LOOP
        output := output || current_record.column_name
    END LOOP;

    return output;
END my_func;

Answer 1

If you are version 11.2 or up then you could use LISTAGG function:

create or replace type a_nested_table as table of varchar2(20);

create or replace function my_func return varchar2 is
  result varchar2(4000);
  list   a_nested_table := a_nested_table('aa', 'bb', 'cc', 'dd', 'ee');
begin
  select listagg(column_value, ' ') within group(order by column_value desc)
    into result
    from table(list);

  return(result);
end my_func;

...

SQL> select my_func from dual;
MY_FUNC
--------------------------------------------------------------------------------
ee dd cc bb aa

For more information on using LISTAGG function refer to documentation.

UPDATE:

For 10g version:

create or replace type a_nested_table is table of varchar(20);

create or replace function my_func return varchar2 is
  list    a_nested_table := a_nested_table('aa', 'bb', 'cc', 'dd', 'ee');
  result varchar2(4000);
begin
  for c1 in (select column_value col_val from table(list) order by 1 desc) loop
    result := result || ' ' || c1.col_val;
  end loop;
  return result;
end my_func;

SQL> select my_func from dual;
MY_FUNC
--------------------------------------------------------------------------------
ee dd cc bb aa