query inside the exception and begin block in exception

Question

Hello I want to ask what is the best practise to do.

First example try to retrieve my data through exceptions I use this code in my main application and is working fine but I dont know if its good practise to code inside the exceptions blocks

BEGIN   
    DECLARE
        v_status varchar2(100);
        v_flag varchcar2(100);
    BEGIN 
        SELECT STATUS INTO v_status FROM TABLE1 WHERE condition1;
    EXCEPTION 
        when no_data_found then 
            select FLAG INTO v_flag FROM TABLE2   WHERE condition1; -- THERE WILL BE 100% RECORD 
            
            IF v_flag='N' THEN 
                V_STATUS:='N'
            ELSIF v_flag:='O'
                V_STATUS:='O'
            ELSE
                BEGIN 
                    SELECT STATUS INTO V_STATUS FROM TABLE3 WHERE condition1,condition2;
                EXCEPTION 
                    V_STATUS:='F';
                END;
            END IF;         
    END;

    IF V_STATUS='O' THEN 
        --DO SOMETHING HERE 
    ELSIF V_STATUS='N' THEN 
        --DO SOMETHING HERE 
    ELSE 
        --DO SOMETHING HERE
    END IF;
END;

SECOND EXAMPLE TRY TO RETRIEVE DATA WITH CASES AND SELECT WITH COUNT.

BEGIN   
    DECLARE 
        V_CNTR NUMBER;
        V_STATUS VARCHAR2(100);
    BEGIN 
        SELECT COUNT(1) INTO V_CNTR FROM TABLE1 WHERE condition1;
        CASE 
            WHEN COUNT=1 THEN 
                SELECT STATUS INTO V_STATUS FROM TABLE1 WHERE condition1; 
            ELSE 
                select FLAG INTO v_flag FROM TABLE2   WHERE condition1; -- THERE WILL BE 100% RECORD 
                
                IF v_flag='N' THEN 
                    V_STATUS:='N'
                ELSIF v_flag:='O'
                    V_STATUS:='O'
                ELSE 
                        SELECT COUNT(1) INTO V_CNTR FROM TABLE3 WHERE condition1,condition2;
                    CASE 
                        WHEN count=1 THEN 
                            SELECT STATUS INTO V_STATUS FROM TABLE3 WHERE condition1,condition2;
                        ELSE 
                            V_STATUS:='F';
                    END CASE; 
                END IF;         
        END CASE;
    END;

    IF V_STATUS='O' THEN 
        --DO SOMETHING HERE 
    ELSIF V_STATUS='N' THEN 
        --DO SOMETHING HERE 
    ELSE 
        --DO SOMETHING HERE
    END IF;
END;

Answer 1

From personal experience...

Those nested blocks work fine and it can be useful to use code in exception blocks, but it becomes very unreadable quickly as you show in your example. If your initial code looks like this, then imagine what it will look like a couple of development cycles later.

It is cleaner to move those BEGIN SELECT INTO EXCEPTION WHEN NO_DATA_FOUND THEN... END: blocks into functions. Makes the code a lot more structured, more readable and easier to debug and maintain:

DECLARE
  v_status varchar2(100);
  v_flag varchcar2(100);
  FUNCTION status (argument_i VARCHAR2) RETURN VARCHAR2
  IS
    l_status VARCHAR2(100);
  BEGIN
    SELECT STATUS INTO v_status FROM TABLE1 WHERE condition = argument_i;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL; -- or -1 or NOTFOUND - whatever you prefer
  END;
BEGIN
  v_status := status(argument_i => condition);
  IF v_status IS NULL THEN
    ...
  ELSE
    ...
  END IF;
END; 

Here it's an inline function - within packages you can use standalone functions, private if never called outside the package. Note, in your 2 examples you declare the variables in the inner block but call them in the outer block - that is something to avoid.