How to assign variable as column value

oracleplsql

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,'(^|,)','\1x'),
    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,'(^|,)','\1x'),
        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.

Best Answer

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;
Related Question