Search code examples
plsqloracle11gshort-circuiting

How to prevent Oracle to use short circuit in PL/SQL


The following PL/SQL code shows the function ensure(...) and how I will use it in Oracle 11g.

declare
valid boolean;
function ensure(b boolean, failure_message varchar) return boolean is begin
  if not b then
    dbms_output.put_line(failure_message);
    return false;
  else
    return true;
  end if;
end ensure;
begin
  valid := true;
  valid := valid 
        and ensure(1=1, 'condition 1 failed') 
        and ensure(1=2, 'condition 2 failed') 
        and ensure(2=3, 'condition 3 failed');

  if not valid then
    dbms_output.put_line('some conditions failed, terminate the program');
    return;
  end if;

  dbms_output.put_line('do the work');
end;
/

I want to use ensure(...) to pre-validate a set of conditions, the program is allowed to do the work only if all conditions pass.

I want the program to evaluate every ensure(...) even if the preceding ensure(...) return false, so that the failure_message will be printed for every conditions that fails.

The problem is Oracle uses short-circuit evaluation and ignore the rest conditions that come after the one that return false. For example, the above program prints the following message.

condition 2 failed
some conditions failed, terminate the program

How to tell Oracle to not use short-circuit evaluation so that the above program print the following message.

condition 2 failed
condition 3 failed
some conditions failed, terminate the program

Solution

  • Try:

    declare
    valid boolean;
    con1 boolean;
    con2 boolean;
    con3 boolean;
    function ensure(b boolean, failure_message varchar) return boolean is begin
      if not b then
        dbms_output.put_line(failure_message);
        return false;
      else
        return true;
      end if;
    end ensure;
    begin
      valid := true;
      con1 := ensure(1=1, 'condition 1 failed') ;
      con2 := ensure(1=2, 'condition 2 failed') ;
      con3 := ensure(2=3, 'condition 3 failed');
      valid := con1 AND con2 AND con3;
      if not valid then
        dbms_output.put_line('some conditions failed, terminate the program');
        return;
      end if;
    
      dbms_output.put_line('do the work');
    end;
    /