Using Subtypes for Booleans

I try to use subtypes as much as I can and after reading the “All Things Oracle” article of Patrick Barel on PL/SQL subtypes, I want to add something to his article about the use of subtypes. Booleans are a PL/SQL datatype and doesn’t exist in SQL. This means that when your PL/SQL function returns a boolean, you can’t use

I try to use subtypes as much as I can and after reading the “All Things Oracle” article of Patrick Barel on PL/SQL subtypes, I want to add something to his article about the use of subtypes.

Booleans are a PL/SQL datatype and doesn’t exist in SQL. This means that when your PL/SQL function returns a boolean, you can’t use it in SQL.

Here’s an example…

I create a package with a function that returns a BOOLEAN.

Easy to use in PL/SQL:

But when you use this in SQL, for example in a query:

You’ll receive some errors:

When you want to use the function also in SQL, you usualy create a wrapper function.

This one still works:

But now this one is also working:

Instead of using two different functions, just combine these two in one. The “is_equal_num” function will work for both PL/SQL and SQL, but you can’t use “IF test_boolean.is_equal_num(1,1) THEN” anymore, so you’ll have to compare with a value. You’ll have to code “IF test_boolean.is_equal_num(1,1) = 1 THEN”.

Of course, you don’t need to use a NUMBER datatype, you can use any SQL datatype, e.g. VARCHAR2(1) with values ‘Y’ and ‘N’. It doesn’t really matter which you are using, but it’s a good idea to have a standard. And this standard can be defined using subtypes.

Here’s what I do…

I create a package in which I define my subtypes, but I also define constants to use with this subtype. For booleans there are always two possible values: true or false, 1 or 0, ‘Y’ or ‘N’, …

This is what my package looks like:

I changed my function in my package to use my own booleans from the subtypes package:

Both the test cases will work without a wrapper function(all though now we’ll have to compare in PL/SQL):

When you want to change the subtypes, you don’t have to change any code besides the subtypes package:

But this time in SQL the outcome is a VARCHAR2(1) and contains ‘Y’:

You can also use a DATE datatype for your boolean. Give it a try!

(But you shouldn’t use a date in a “real” application environment, it’s not the most suitable datatype)