Oracle PL/SQL/Data Type/Boolean

Assign a value to a variable of type boolean. Don"t use quotes around the value like this

   
SQL>
SQL> DECLARE
  2     v_boolean BOOLEAN;
  3  BEGIN
  4     v_boolean := "TRUE";
  5  END;
  6  /
   v_boolean := "TRUE";
                *
ERROR at line 4:
ORA-04045: errors during recompilation/revalidation of sqle.LOG_ERRORS
ORA-01031: insufficient privileges
ORA-06550: line 4, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored

SQL>
SQL>
SQL> -- Instead, do this
SQL>
SQL> DECLARE
  2     v_boolean BOOLEAN;
  3  BEGIN
  4     v_boolean := TRUE;
  5  END;
  6  /
PL/SQL procedure successfully completed.

Boolean data type with If statement

   
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> <<default_test>>
  2  DECLARE
  3     lv_first_num      NUMBER(10) DEFAULT 0;    -- Defaulted to 0
  4     lv_second_num     NUMBER(10) := 10;        -- Defaulted to 10
  5     lv_third_num      NUMBER(10);              -- Defaulted to NULL
  6     lv_processed_bln  BOOLEAN DEFAULT FALSE;   -- Defaulted to FALSE
  7     lv_complete_bln1  BOOLEAN;                 -- Defaulted to NULL
  8     lv_complete_bln2  BOOLEAN;                 -- Defaulted to NULL
  9  BEGIN
 10     DBMS_OUTPUT.PUT_LINE("lv_first_num:      " ||
 11               TO_CHAR(lv_first_num)  ||
 12               CHR(10) || "lv_second_num:     " ||
 13               TO_CHAR(lv_second_num) ||
 14               CHR(10) || "lv_third_num:      " ||
 15               TO_CHAR(lv_third_num)  ||
 16               CHR(10) || "lv_processed_bln:  " || "FALSE" ||
 17               CHR(10) || "lv_complete_bln1:  " || ""      ||
 18               CHR(10) || "lv_complete_bln2:  " || ""      ||
 19               CHR(10) ||
 20               "default_test.lv_second_num: " ||
 21               TO_CHAR(default_test.lv_second_num) || CHR(10));
 22     DBMS_OUTPUT.PUT_LINE("Is lv_second_num > lv_third_num?");
 23     IF lv_second_num > lv_third_num THEN
 24        DBMS_OUTPUT.PUT_LINE("lv_second_num > lv_third_num" || CHR(10));
 25     ELSE
 26        DBMS_OUTPUT.PUT_LINE("lv_second_num < lv_third_num" || CHR(10));
 27     END IF;
 28     DBMS_OUTPUT.PUT_LINE("Is lv_first_num = lv_third_num?");
 29     IF lv_first_num = lv_third_num THEN
 30        DBMS_OUTPUT.PUT_LINE("lv_first_num = lv_third_num");
 31     ELSE
 32        DBMS_OUTPUT.PUT_LINE("lv_first_num <> lv_third_num");
 33     END IF;
 34     DBMS_OUTPUT.PUT_LINE("Is lv_complete_bln1 = TRUE?");
 35     IF lv_complete_bln1 THEN
 36        DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 = TRUE");
 37     ELSE
 38        DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 <> TRUE");
 39     END IF;
 40     DBMS_OUTPUT.PUT_LINE("Is NOT lv_complete_bln1 = TRUE?");
 41     IF NOT lv_complete_bln1 THEN
 42        DBMS_OUTPUT.PUT_LINE("NOT lv_complete_bln1 = TRUE");
 43     ELSE
 44        DBMS_OUTPUT.PUT_LINE("NOT lv_complete_bln1 <> TRUE");
 45     END IF;
 46     DBMS_OUTPUT.PUT_LINE("Is lv_complete_bln1 = lv_complet_bln2?");
 47     IF lv_complete_bln1 = lv_complete_bln2 THEN
 48        DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 = lv_complete_bln2");
 49     ELSE
 50        DBMS_OUTPUT.PUT_LINE("lv_complete_bln1 <> lv_complete_bln2");
 51     END IF;
 52  END default_test;
 53  /
lv_first_num:      0
lv_second_num:     10
lv_third_num:
lv_processed_bln:  FALSE
lv_complete_bln1:
lv_complete_bln2:
default_test.lv_second_num: 10
Is lv_second_num > lv_third_num?
lv_second_num < lv_third_num
Is lv_first_num = lv_third_num?
lv_first_num <> lv_third_num
Is lv_complete_bln1 = TRUE?
lv_complete_bln1 <> TRUE
Is NOT lv_complete_bln1 = TRUE?
NOT lv_complete_bln1 <> TRUE
Is lv_complete_bln1 = lv_complet_bln2?
lv_complete_bln1 <> lv_complete_bln2
PL/SQL procedure successfully completed.
SQL>

Boolean expressions in where clause

  
SQL>
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL>
SQL> select gift_id, total_price, payment
  2    from gift
  3   where (payment = "CA" OR payment = "VS" OR payment = "VG")
  4     AND total_price > 15;
   GIFT_ID TOTAL_PRICE PA
---------- ----------- --
         1      123.12 CA
         2       50.98 CA
         3       35.99 VS
         4       19.95 CA
4 rows selected.
SQL>
SQL> drop table gift;
Table dropped.

Boolean literals

  
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL>
SQL> DECLARE
  2     v_true BOOLEAN := TRUE;
  3     v_false BOOLEAN := FALSE;
  4     v_null BOOLEAN := NULL;
  5  BEGIN
  6     IF v_true
  7     THEN
  8        DBMS_OUTPUT.PUT_LINE("true");
  9     END IF;
 10     IF v_false
 11     THEN
 12        DBMS_OUTPUT.PUT_LINE("false");
 13     END IF;
 14     IF v_null
 15     THEN
 16        DBMS_OUTPUT.PUT_LINE("null");
 17     END IF;
 18  END;
 19  /
true
PL/SQL procedure successfully completed.
SQL>

Define and use boolean value

  
SQL>
SQL> --  Effects of nulls on boolean expressions.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     a     INTEGER;
  3     n     INTEGER;     -- be our null value.
  4     b     BOOLEAN;
  5  BEGIN
  6     -- Assign a value to the variable A, but leave N null.
  7     a := 2;
  8     b := (a <> n);
  9
 10     IF b THEN
 11       DBMS_OUTPUT.PUT_LINE("a <> n is TRUE");
 12     ELSIF NOT b THEN
 13       DBMS_OUTPUT.PUT_LINE("a <> n is FALSE");
 14     ELSE
 15       DBMS_OUTPUT.PUT_LINE("a <> n is NULL");
 16     END IF;
 17  END;
 18  /
a <> n is NULL
PL/SQL procedure successfully completed.
SQL>

PL/SQL BOOLEAN datatypes accept only TRUE, FALSE, NULL

  
SQL> -- Boolean datatypes
SQL>
SQL> -- PL/SQL BOOLEAN datatypes accept only TRUE, FALSE, NULL.
SQL>
SQL> DECLARE
  2     x NUMBER := 10;
  3     y NUMBER := 20;
  4     v_flag BOOLEAN;
  5  BEGIN
  6     v_flag := (x > y);
  7
  8     DBMS_OUTPUT.PUT_LINE(v_flag);
  9  END;
 10  /
   DBMS_OUTPUT.PUT_LINE(v_flag);
   *
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00306: wrong number or types of arguments in call to "PUT_LINE"
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored

Use the NVL() against a non-initialized BOOLEAN variable:

  
SQL> DECLARE
  2    
  3    my_var BOOLEAN;
  4  BEGIN
  5    
  6    IF NOT NVL(my_var,FALSE) THEN
  7      dbms_output.put_line("This should happen!");
  8    ELSE
  9      dbms_output.put_line("This can""t happen!");
 10    END IF;
 11  END;
 12  /
This should happen!
PL/SQL procedure successfully completed.
SQL>