Evaluation of PL/SQL boolean variables in Oracle Forms

Question

Suppose I have a BOOLEAN variable within a PL/SQL block in an Oracle Form:

DECLARE
  is_viewable BOOLEAN;
BEGIN
  is_viewable := ...;

  IF NOT is_viewable THEN
    raise_my_error(); // pseudo-code
  END IF;
END;

After stepping through this code several times with a debugger, I have determined that raise_my_error() never gets called. To clarify:

• raise_my_error() does not get called if is_viewable = TRUE
• raise_my_error() does not get called if is_viewable = FALSE

Initial tests suggest that this behavior is limited to PL/SQL code run within Oracle Forms and not PL/SQL code run directly within the database (although I could be wrong).

I can get around this by explicitly comparing is_viewable to FALSE:

IF is_viewable = FALSE THEN
  raise_my_error();
END IF;

I am still curious why NOT is_viewable never evaluates to TRUE.

Update: It appears that my debugger wasn't showing correct values and that this question is no longer valid. Sorry about that confusion.

Huldahuldah

[friendly reminder] as you are experienced user, I guess you just forgot to accept an answer (dcp's or Jeffrey's). – Sir

@AlexanderMalakhov: I have wanted to accept an answer on several occasions, but the question appears to be un-answerable now that the code appears to be running correctly. Unless I am mistaken, neither answer could explain why NOT is_viewable would always evaluate to FALSE (regardless of the value of is_viewable) whereas is_viewable = FALSE would evaluate to either TRUE or FALSE, depending on the value of is_viewable. Because the problem stopped occurring, I can't verify any answers. :( – Huldahuldah

Understood. I had similar (w.r.t. "no correct answer, can't verify") question. As there were very little probability someone else will ever answer my Q, I've decided to accept most helpful (as opposed to correct) answer. So to reward the man for his time and effort. And for my Q not dangling in the list of unanswered. Though to be clear, I've completely no problems with someone not following my strategy (more so since you're obviously doing a great job for SO community) – Sir

Answer 1

We can test this in SQLPlus to see what happens in each of the 3 situations (true, false, null):

set serveroutput on

declare
  true_value boolean := true;
  false_value boolean := false;
  null_value boolean;
begin

    if not true_value then  --Should not pass
      dbms_output.put_line('True Value');
    end if;

    if not false_value then --Should pass
      dbms_output.put_line('False Value');
    end if;

    if null_value is null then --Just to make sure it is null
      dbms_output.put_line('Null Value is Null');
    end if;

    if not null_value then --Should not pass
      dbms_output.put_line('Null Value');
    end if;
end;
/

Which produces:

SQL> set serveroutput on
SQL>
SQL> declare
  2    true_value boolean := true;
  3    false_value boolean := false;
  4    null_value boolean;
  5  begin
  6
  7      if not true_value then  --Should not pass
  8        dbms_output.put_line('True Value');
  9      end if;
 10
 11      if not false_value then --Should pass
 12        dbms_output.put_line('False Value');
 13      end if;
 14
 15      if null_value is null then --Just to make sure it is null
 16        dbms_output.put_line('Null Value is Null');
 17      end if;
 18
 19      if not null_value then --Should not pass
 20        dbms_output.put_line('Null Value');
 21      end if;
 22  end;
 23  /
False Value
Null Value is Null

PL/SQL procedure successfully completed.

SQL>

So the only possible code path that can produce your expected output is if the value going into the conditional is false. If that is not what you are seeing or expecting then something else must be happening in your procedure or as a side effect.

Noodle

Answer 2

NOT is_viewable evaluates to TRUE if and only if is_viewable is FALSE.

In your case, is_viewable is probably being set to NULL; perhaps the Forms debugger shows you "FALSE" in this scenario causing confusion.

Try this code instead:

IF NOT is_viewable THEN 
   raise_my_error();
ELSIF is_viewable IS NULL THEN
   raise_another_error();
END IF;

Eggert

Answer 3

What value is the variable being set to? Do understand that if the value is null, the the block will never execute. I'm not sure if that's your problem, but here's an example:

DECLARE
is_viewable BOOLEAN;
BEGIN
  IF NOT is_viewable
  THEN
      /* this won't execute */
      dbms_output.put_line('nope');
  END IF;
  IF is_viewable
  THEN
      /* neither will this */
      dbms_output.put_line('nope');
  END IF;
END;

Of course, I don't know how Oracle Forms would be doing it differently, but maybe it's setting the variable to null somehow?

Pedraza

Thanks for the tip! I already understood the issue with NULL values. However, I have witnessed this behavior (via the debugger) with is_viewable set to TRUE as well as is_viewable set to FALSE. To be honest, I hadn't tried seeing what would happen if it were set to NULL... I doubt it would help the situation much. – Huldahuldah

Answer 4

You must set an initial value for is_viewable when it is declared. Oracle does not set a default value for BOOLEANS when they are declared. Set the value of the BOOLEAN when it is declared setting the value inside the block may not always be the best idea. If you are creating a function and the block fails then you may get a function being returned without a value but if declared outside the block and you have an exception handler then it would catch and handle the error. This is always a good practice to set up the block in this manner.

DECLARE 
    bTest BOOLEAN := FALSE;

BEGIN

--in your test check for the most likely thing that would happen 
--if bTest would in most instances evaluate to be FALSE then that should be your check

  IF NOT bTest THEN


   MESSAGE('True Passed');

  ELSE 

   MESSAGE('False Passed');


  END IF;

--in the event that an exception occurs or the block fails
--the function would still return a value

EXCEPTION WHEN NO_DATA_FOUND THEN
     bTest := FALSE;

WHEN OTHERS THEN
      bTest := FALSE;


END 

Phipps

Answer 5

Try this to see if it changes anything:

IF is_viewable THEN
    NULL;
ELSE
    raise_my_error();
END IF;

Labrie

It's worth mentioning, this code potentially hides bug. E.g. myBool := (sum1 = sum2), where sum1=0 and sum2 is null. One would probably want this to be True instead of Null. – Sir

Answer 6

What is the version of Forms ?
I've just tried following code in Forms Builder 6i and it works as expected

DECLARE
    bTest BOOLEAN;
BEGIN
   bTest := FALSE;
    IF NOT bTest THEN
        MESSAGE('NOT FALSE passed'); 
        PAUSE;
    END IF;

    bTest := TRUE;
    IF bTest THEN
        MESSAGE('TRUE passed'); 
        PAUSE;
    END IF;

    bTest := NULL;
    IF bTest OR (NOT bTest) THEN
        MESSAGE('You will never see this message'); 
        PAUSE;
    END IF;
END;

Does this work in your environment ?

Edit added null to example.

Sir

Yes, the version of forms would be useful. Also the version of the Oracle client. And of the database. Older versions of Forms had their own PL/SQL interpreter which could be affecting you. – Spinet