How to assign variable as column value

Question

I'm new with Oracle. This is something simple that I'm just not getting.

I'm in a situation where I need to split a string with values separated by commas (i.e. 25,27) and "decode" it (example: if 25 = 'Bird. ' and 27 = 'Elephant. ', then 25, 27 should return Bird. Elephant.).

I've followed this and manged to get values I want, but how do I place it into a select statement as a subquery?

The code so far:

declare
 l_input varchar2(50) := '25,27';
 l_count binary_integer;
 l_array dbms_utility.lname_array;
 finalText varchar2(50);
 placeholderText varchar2(50);
begin
 dbms_utility.comma_to_table
 ( list => regexp_replace(l_input,'(^|,)','1円x'),
 tablen => l_count,
 tab => l_array
 );
 finalText:= '';
 for i in 1 .. l_count
 loop
 placeholderText := '';
 case substr(l_array(i), 2)
 when '25' then placeholderText:= 'Bird.';
 when '27' then placeholderText:= 'Elephant.';
 else placeholderText:= substr(l_array(i), 2);
 end case;
 finalText:= finalText || ' ' || placeholderText; 
 end loop;
 dbms_output.put_line(finalText); --I want this as the column 'value'
end;

What I would like (or something similar):

select
 Id,
 Name,
 (declare
 l_input varchar2(50) := ItemColumValue;
 l_count binary_integer;
 l_array dbms_utility.lname_array;
 finalText varchar2(50);
 placeholderText varchar2(50);
 begin
 dbms_utility.comma_to_table
 ( list => regexp_replace(l_input,'(^|,)','1円x'),
 tablen => l_count,
 tab => l_array
 );
 finalText:= '';
 for i in 1 .. l_count
 loop
 placeholderText := '';
 case substr(l_array(i), 2)
 when '25' then placeholderText:= 'Bird.';
 when '27' then placeholderText:= 'Elephant.';
 else placeholderText:= substr(l_array(i), 2);
 end case;
 finalText:= finalText || ' ' || placeholderText; 
 end loop;
 dbms_output.put_line(finalText); --I want this as the column 'value'
 end;) as "Some Column Name"
from
 some_table

Is this possible?

Thank you.

Answer 1

Actually DBMS_UTILITY.COMMA_TO_TABLE and DBMS_UTILITY.TABLE_TO_COMMA are a very old procedures. Nowadays people usually prefer this one:

WITH t AS 
 (SELECT TRIM(REGEXP_SUBSTR('25,27', '[^,]+', 1, LEVEL)) AS n, LEVEL as pos
 FROM dual
 CONNECT BY REGEXP_SUBSTR('25,27', '[^,]+', 1, LEVEL) IS NOT NULL),
a AS 
 (SELECT pos,
 CASE n
 WHEN '25' THEN 'Bird.'
 WHEN '27' THEN 'Elephant.'
 END AS animal
 FROM t)
SELECT LISTAGG(animal, ' ') WITHIN GROUP (ORDER BY pos) AS value
FROM a;