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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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:
1 2 3 4 5 6 7 8 9 |
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:
1 2 |
SELECT test_boolean.is_equal(1,1) FROM dual; |
You’ll receive some errors:
1 2 |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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:
1 2 3 4 5 6 7 8 9 |
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:
1 2 3 4 5 6 |
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:
1 2 3 4 5 6 7 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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):
1 2 3 4 5 6 7 8 9 |
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. |
1 2 3 4 5 6 |
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:
1 2 3 4 5 6 7 |
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; / |
1 2 3 4 5 6 7 8 9 |
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’:
1 2 3 4 5 6 |
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)
Load comments