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.

CREATE OR REPLACE PACKAGE test_boolean
IS
   FUNCTION is_equal( p_value_1 IN NUMBER
                    , p_value_2 IN NUMBER
                    )
   RETURN BOOLEAN;
END test_boolean;
/

CREATE OR REPLACE PACKAGE BODY test_boolean
IS
   FUNCTION is_equal( p_value_1 IN NUMBER
                    , p_value_2 IN NUMBER
                    )
   RETURN BOOLEAN
   IS
      l_return BOOLEAN;
   BEGIN
      IF p_value_1 = p_value_2
      THEN
         l_return := TRUE;
      ELSE
         l_return := FALSE;
      END IF;

      RETURN l_return;
   END is_equal;
END test_boolean;
/

Easy to use in PL/SQL:

BEGIN
   IF test_boolean.is_equal(1,1)
   THEN
      dbms_output.put_line('The parameters are equal.');
   END IF;
END;
/

The parameters are equal.

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

SELECT test_boolean.is_equal(1,1)
  FROM dual;

You’ll receive some errors:

ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

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

CREATE OR REPLACE PACKAGE test_boolean
IS
   FUNCTION is_equal( p_value_1 IN NUMBER
                    , p_value_2 IN NUMBER
                    )
   RETURN BOOLEAN;

   FUNCTION is_equal_num( p_value_1 IN NUMBER
                        , p_value_2 IN NUMBER
                        )
   RETURN NUMBER;
END test_boolean;
/

CREATE OR REPLACE PACKAGE BODY test_boolean
IS
   FUNCTION is_equal( p_value_1 IN NUMBER
                    , p_value_2 IN NUMBER
                    )
   RETURN BOOLEAN
   IS
      l_return BOOLEAN;
   BEGIN
      IF p_value_1 = p_value_2
      THEN
         l_return := TRUE;
      ELSE
         l_return := FALSE;
      END IF;

      RETURN l_return;
   END is_equal;

   FUNCTION is_equal_num( p_value_1 IN NUMBER
                        , p_value_2 IN NUMBER
                        )
   RETURN NUMBER
   IS
      l_return NUMBER(1);
   BEGIN
      IF is_equal(p_value_1, p_value_2)
      THEN
         l_return := 1;
      ELSE
         l_return := 0;
      END IF;

      RETURN l_return;
   END is_equal_num;
END test_boolean;
/

This one still works:

BEGIN
   IF test_boolean.is_equal(1,1)
   THEN
      dbms_output.put_line('The parameters are equal.');
   END IF;
END;
/

The parameters are equal.

But now this one is also working:

SELECT test_boolean.is_equal_num(1,1) is_equal
  FROM dual;

IS_EQUAL
--------
       1

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:

CREATE OR REPLACE PACKAGE subtypes
IS
   SUBTYPE st_boolean IS pls_integer range 0..1;
   gc_boolean_false CONSTANT st_boolean := 0;
   gc_boolean_true CONSTANT st_boolean := 1;
END;
/

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

CREATE OR REPLACE PACKAGE test_boolean
IS
   FUNCTION is_equal( p_value_1 IN NUMBER
                    , p_value_2 IN NUMBER
                    )
   RETURN subtypes.st_boolean;
END test_boolean;
/

CREATE OR REPLACE PACKAGE BODY test_boolean
IS
   FUNCTION is_equal( p_value_1 IN NUMBER
                    , p_value_2 IN NUMBER
                    )
   RETURN subtypes.st_boolean
   IS
      l_return subtypes.st_boolean;
   BEGIN
      IF p_value_1 = p_value_2
      THEN
         l_return := subtypes.gc_boolean_true;
      ELSE
         l_return := subtypes.gc_boolean_false;
      END IF;

      RETURN l_return;
   END is_equal;
END test_boolean;
/

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

BEGIN
   IF test_boolean.is_equal(1,1) = subtypes.gc_boolean_true
   THEN
      dbms_output.put_line('The parameters are equal.');
   END IF;
END;
/

The parameters are equal.
SELECT test_boolean.is_equal(1,1) is_equal
  FROM dual;

IS_EQUAL
--------
       1

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

CREATE OR REPLACE PACKAGE subtypes
IS
   SUBTYPE st_boolean IS VARCHAR2(1);
   gc_boolean_false CONSTANT st_boolean := 'N';
   gc_boolean_true CONSTANT st_boolean := 'Y';
END;
/
BEGIN
   IF test_boolean.is_equal(1,1) = subtypes.gc_boolean_true
   THEN
      dbms_output.put_line('The parameters are equal.');
   END IF;
END;
/

The parameters are equal.

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

SELECT test_boolean.is_equal(1,1) is_equal
  FROM dual;

IS_EQUAL
--------
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)