Behavior of Boolean Datatype in SQL and PL/SQL

Oracle provides boolean datatype for PL/SQL. Boolean datatype can house three literals i.e, TRUE, FALSE and NULL. The main rationale for coming with such datatype was to evaluate comparison expressions.

The value of boolean datatype variable cannot be printed using DBMS_OUTPUT.PUT_LINE


SQL> declare
2 b_variable boolean;
3 begin
4 b_variable:=true;
5 dbms_output.put_line(b_variable);
6 end;
7 /
dbms_output.put_line(b_variable);
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

A simple if statement can act as a remedy for the above code.

SQL> declare
2 b_variable boolean;
3 begin
4 b_variable:=true;
5 if (b_variable) then
6 dbms_output.put_line('true');
7 else
8 dbms_output.put_line('false');
9 end if;
10 end;
11 /
true

PL/SQL procedure successfully completed.

SQL>


Functions returning boolean data cannot be used in select statements.

SQL> create or replace function is_it_equal_2_oracle(instring varchar2)
2 return boolean is
3 begin
4 if (instring='oracle') then
5 return true;
6 else
7 return false;
8 end if;
9 end;
10 /

Function created.

SQL> select is_it_equal_2_oracle('sqlserver') from dual;
select is_it_equal_2_oracle('sqlserver') from dual
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type


The same function can be used in PL/SQL as follows.


1 declare
2 database char(10) :='sqlserver';
3 begin
4 if is_it_equal_2_oracle(database) then
5 dbms_output.put_line('Yes it is oracle');
6 else
7 dbms_output.put_line('No it is not equal to oracle');
8 end if;
9* end;
SQL> /
No it is not equal to oracle

PL/SQL procedure successfully completed.

SQL>

People might confuse with the following SQL statement.

SQL> select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from dual;

As in SQL there is no boolean datatype, true will not be identified by SQL. Output is as follows.

SQL> select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from d
ual;
select decode(is_it_equal_2_oracle('sqlserver'),true,'true','false') from dual
*
ERROR at line 1:
ORA-00904: "TRUE": invalid identifier


Conclusion: Boolean datatype is exclusive for PL/SQL. A care should be taken while doing implementation.