Why am I getting ORA-06592: CASE not found while executing CASE statement in PL/SQL?

I have the following CASE in PL/SQL

        CASE
            WHEN v_line_item.custom_segment = 'CND1' THEN
                v_current_col := v_col_lcy_tps;
            WHEN v_line_item.custom_segment = 'CND2' THEN
                v_current_col := v_col_lcy_ib;
            WHEN v_line_item.custom_segment = 'CND3' THEN
                v_current_col := v_col_lcy_gm;
            WHEN v_line_item.custom_segment = 'CND4' THEN
                v_current_col := v_col_lcy_pb;
            WHEN v_line_item.custom_segment = 'CND5' THEN
                v_current_col := v_col_lcy_bb;
        END CASE;

The code compil开发者_StackOverflow社区es fine, but when I execute to stored proc I get the following error:

ORA-06592: CASE not found while executing CASE statement

So when I remove the CASE; the stored proc won't compile. The only Examples I can get my hands on, uses the CASE in a select statement, I don't want to use it in select statement, I want to set my variable without having a bunch of IF THEN ELSE statements.

If you use a CASE statement - the listings under the CASE - must match all conditions that you might encounter - either explicitly as you have done by using

WHEN v_line_item.custom_segment = 'CND1' THEN
            v_current_col := v_col_lcy_tps;
WHEN v_line_item.custom_segment = 'CND2' THEN

or by using the ELSE clause.

Your code is hitting a situation where v_line_item.custom_segment doesn't match any of the given CASE scenarios, hence Oracle raises this exception.

You could add a catch-all condition

ELSE
   -- do some work here, raise an exception or log it.

so that it matches all conditions.

Further reading:

• What you must know about CASE in PL/SQL
• Oracle Documentation on CASE

Old thread I know, but wouldn't you just write it like this?

v_current_col :=
    case v_line_item.custom_segment
        when 'CND1' then v_col_lcy_tps
        when 'CND2' then v_col_lcy_ib
        when 'CND3' then v_col_lcy_gm
        when 'CND4' then v_col_lcy_pb
        when 'CND5' then v_col_lcy_bb
    end;

It's more concise and readable and it won't give an ORA-06592 error.