This is an
excerpt from the book
Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
The Boolean data type in PL/SQL
allows us to store True, False and Null values which help us
in processing the logical states of a program unit.
This data type is only available in
PL/SQL and not in SQL, thus using Boolean values in an SQL
statement has always been impossible until Oracle version
12cR1.
Boolean Enhancements in
12cR1
In the below example, a function has
been created with an input variable of type Boolean
returning a Varchar2 result describing the input parameter
value in a string format.
1.
CREATE OR REPLACE
2.
FUNCTION func_boo(
3.
ip_b_var1 BOOLEAN)
4.
RETURN VARCHAR2
5.
IS
6.
BEGIN
7.
RETURN
8.
CASE
9.
WHEN ip_b_var1 THEN
10.
'True'
11.
WHEN NOT ip_b_var1 THEN
12.
'False'
13.
ELSE
14.
'Null'
15.
END;
16.
END;
17.
/
|
Line No.
|
Description
|
|
1,2,3
|
The function func_boo is created with one input
parameter ip_b_var1 of the Boolean data type.
|
|
4
|
The return type of the function is declared as
Varchar2.
|
|
5
|
Start of the declaration section of the function.
|
|
6
|
Start of the execution section of the function.
|
|
7-15
|
The Return statement is placed for returning the
text 'True' when the Boolean value True is passed as
the input parameter, 'False' when the Boolean value
False is passed as the input parameter or 'Null'
when the Boolean value Null is passed as the input
parameter to the function using a CASE statement.
|
|
16,17
|
End of the execution section of the function.
|
This function cannot be invoked
through an SQL statement as the input parameter's data type
is not supported by SQL. Thus the below method has been used
until R12.1 version for these situations.
%
Note: This
function cannot be used in an SQL statement
when its return type is a Boolean.
|
1.
SET SERVEROUTPUT ON SIZE
200000;
2.
DECLARE
3.
L_vc_var1 VARCHAR2(10);
4.
L_b_var2 BOOLEAN:=true;
5.
BEGIN
6.
L_vc_var1:=func_boo(l_b_var2);
7.
dbms_output.put_line(l_vc_var1);
8.
END;
9.
/
Result:
True
|
Line No.
|
Description
|
|
1
|
This environment variable opens up an output buffer
of size limit of 200000.
|
|
2
|
Start of the declare section of the block.
|
|
3
|
A local variable l_vc_var1 of the Varchar2 data type
is declared with a precision value 10.
|
|
4
|
A local variable l_b_var2 of the Boolean data type
is declared and defaulted to True.
|
|
5
|
Start of the execution section of the block.
|
|
6
|
The function func_boo's return value for the input
parameter as the local variable l_b_var2 is assigned
to the local variable l_vc_var1.
|
|
7
|
The variable l_vc_var1 is printed using the
procedure dbms_output.put_line.
|
|
8,9
|
End of the execution section of the block.
|
%
Note: This
function cannot be used in the SQL statement
if the Boolean value is directly used in the
SQL statement rather than a variable
assignment.
|
From R12.1 version, the above
anonymous block can be rewritten as like below. This
methodology is not possible prior to this release.
1.
SET SERVEROUTPUT ON SIZE
200000;
2.
DECLARE
3.
L_vc_var1 VARCHAR2(10);
4.
L_b_var2 BOOLEAN:=true;
5.
BEGIN
6.
SELECT func_boo(L_b_var2)
INTO L_vc_var1 FROM dual;
7.
dbms_output.put_line(L_vc_var1);
8.
END;
9.
/
Result:
True
|
Line No.
|
Description
|
|
1
|
This environment variable opens up an output buffer
of size limit of 200000.
|
|
2
|
Start of the declare section of the block.
|
|
3
|
A local variable l_vc_var1 of the Varchar2 data type
is declared with a precision value 10.
|
|
4
|
A local variable l_b_var2 of the Boolean data type
is declared and defaulted to True.
|
|
5
|
Start of the execution section of the block.
|
|
6
|
A select statement invoking the function func_boo's
for the input parameter as the local variable
l_b_var2 assigns its return value to the local
variable l_vc_var1.
|
|
7
|
The variable l_vc_var1 is printed using the
procedure dbms_output.put_line.
|
|
8,9
|
End of the execution section of the block.
|
%
Note: This
function cannot be invoked from a plain SQL
statement.
|
In the below example, the above
created function's result is checked in a decode statement
to print out the description of its result. This scenario is
not possible for the releases prior to 12cR1 version.
1.
SET SERVEROUTPUT ON SIZE
200000;
2.
DECLARE
3.
l_vc_var1 VARCHAR2(30);
4.
l_b_var2 BOOLEAN:=true;
5.
BEGIN
6.
SELECT DECODE(func_boo(l_b_var2),
'True', 'The value is True', 'False', 'The value is
False', 'The value is Null')
7.
INTO l_vc_var1
8.
FROM dual;
9.
dbms_output.put_line(l_vc_var1);
10.
END;
11.
/
Result:
The value is True
|
Line No.
|
Description
|
|
1
|
This environment variable opens up an output buffer
of size limit of 200000.
|
|
2
|
Start of the declare section of the block.
|
|
3
|
A local variable l_vc_var1 of the Varchar2 data type
is declared with a precision value 30.
|
|
4
|
A local variable l_b_var2 of the Boolean data type
is declared and defaulted to True.
|
|
5
|
Start of the execution section of the block.
|
|
6
|
A select statement invoking the function func_boo's
for the input parameter as the local variable
l_b_var2 in a decode statement assigns the
appropriate descriptive text for the return value
and assigns it to the variable l_vc_var1.
|
|
7
|
The variable l_vc_var1 is printed using the
procedure dbms_output.put_line.
|
|
8,9
|
End of the execution section of the block.
|
In the below example, the function
created above is used in the WHERE condition to print the
result of the function. This scenario of function usage is
also not possible prior to 12cR1.
1.
SET SERVEROUTPUT ON SIZE
200000;
2.
DECLARE
3.
l_vc_var1 VARCHAR2(10);
4.
l_b_var2
BOOLEAN:=false;
5.
BEGIN
6.
SELECT CASE COUNT(*)
7.
WHEN 1
8.
THEN 'True'
9.
ELSE 'Not True'
10.
END
11.
INTO l_vc_var1
12.
FROM dual
13.
WHERE func_boo(l_b_var2)='True';
14.
dbms_output.put_line(l_vc_var1);
15.
END;
16.
/
Result:
Not True
|
Line No.
|
Description
|
|
1
|
This environment variable opens up an output buffer
of size limit of 200000.
|
|
2
|
Start of the declare section of the block.
|
|
3
|
A local variable l_vc_var1 of the Varchar2 data type
is declared with a precision value 10.
|
|
4
|
A local variable l_b_var2 of the Boolean data type
is declared and defaulted to False.
|
|
5
|
Start of the execution section of the block.
|
|
6-13
|
A select statement invoking the function func_boo's
for the input parameter as the local variable
l_b_var2 in its WHERE condition comparing it to the
Boolean value True. If this condition matches, the
select query assigns the variable l_vc_var1 with the
text 'True' and if it does not match, the select
query assigns the variable l_vc_var1 with the text
'Not True' based on the count function.
|
|
14
|
The variable l_vc_var1 is printed using the
procedure dbms_output.put_line.
|
|
15,16
|
End of the execution section of the block.
|
 |
Need to learn to program with PL/SQL?
For complete notes on programming in PL/SQL, we
recommend the book
Advanced PL/SQL: The Definitive Reference by Boobal Ganesan.
This is a complete book on PL/SQL with
everything you need to know to write efficient and
complex PL/SQL code. |
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
