Total Pageviews

2020/09/03

[Oracle] ORA-06592 - CASE not found while executing CASE statement

Problem
When I try to execute the following function, I got ORA-06592 - CASE not found while executing CASE statement error:
    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;


How-To
The error result from missing else clause, the function should be modified as bellows:

    FUNCTION FN_GET_BIZ_COD (i_biz_cod IN VARCHAR2)
    RETURN VARCHAR2 IS o_result VARCHAR2(2) := '';
    BEGIN
        CASE i_biz_cod
            WHEN '01' THEN o_result := ' P';
            WHEN '02' THEN o_result := ' S';
            WHEN '03' THEN o_result := ' 8';
            WHEN '04' THEN o_result := ' 3';
            WHEN '05' THEN o_result := ' 8';
            WHEN '06' THEN o_result := '11';
            WHEN '07' THEN o_result := ' 6';
            ELSE o_result := '';
        END CASE;
        RETURN o_result;
    END FN_GET_BIZ_COD;

No comments: