CODEWITHSUNDEEP
plsqloracle11gshort-circuiting
asinkxcoswt
asinkxcoswt

Reputation: 2544

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

Upvotes: 1

Views: 141

Answers (2)

user272735
user272735

Reputation: 10648

I usually validate preconditions with assertions. I don't know if this is suitable in the OP's case, but I think it's a worth of mentioning as a viable solution alternative.

Example:

declare
  procedure assert(p_cond in boolean, p_details in varchar2 default null) is
  begin
    if not p_cond then
      raise_application_error(-20100, p_details);
    end if;
  end;
begin
  assert(1 = 1, 'first');
  assert(1 = 2, 'second');
  assert(1 = 1, 'third');

  -- all preconditions are valid, processing is "safe"
end;
/

Obviously in real code/logic one must considered how the exceptions should be handled. Also state variables might be used/required.

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

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;
/

Upvotes: 1

Related Questions