0

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.

0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.