Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Evaluation of PL/SQL boolean variables in Oracle Forms

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.

like image 938
Adam Paynter Avatar asked Dec 09 '09 20:12

Adam Paynter


People also ask

Does Oracle SQL support Boolean?

The Oracle RDBMS does not support a Boolean datatype. You can create a table with a column of datatype CHAR(1) and store either “Y” or “N” in that column to indicate TRUE or FALSE. That is a poor substitute, however, for a datatype that stores actual Boolean values (or NULL).

What value S can be assigned to Boolean variable in PLSQL?

Boolean values and variables are very useful in PL/SQL. Because a Boolean variable can only be TRUE, FALSE, or NULL, you can use that variable to explain what is happening in your code.

Can we print Boolean value in Oracle?

No. Directly you cannot use it as parameter in dbms_output.


2 Answers

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.

like image 147
Doug Porter Avatar answered Oct 10 '22 02:10

Doug Porter


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?

like image 34
dcp Avatar answered Oct 10 '22 01:10

dcp