{"id":73236,"date":"2013-06-21T13:54:20","date_gmt":"2013-06-21T13:54:20","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/using-subtypes-for-booleans\/"},"modified":"2021-07-14T13:07:41","modified_gmt":"2021-07-14T13:07:41","slug":"using-subtypes-for-booleans","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/using-subtypes-for-booleans\/","title":{"rendered":"Using Subtypes for Booleans"},"content":{"rendered":"

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<\/a>, I want to add something to his article about the use of subtypes.<\/p>\n

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.<\/p>\n

Here’s an example…<\/p>\n

I create a package with a function that returns a BOOLEAN.<\/p>\n

CREATE OR REPLACE PACKAGE test_boolean\r\nIS\r\n   FUNCTION is_equal( p_value_1 IN NUMBER\r\n                    , p_value_2 IN NUMBER\r\n                    )\r\n   RETURN BOOLEAN;\r\nEND test_boolean;\r\n\/\r\n\r\nCREATE OR REPLACE PACKAGE BODY test_boolean\r\nIS\r\n   FUNCTION is_equal( p_value_1 IN NUMBER\r\n                    , p_value_2 IN NUMBER\r\n                    )\r\n   RETURN BOOLEAN\r\n   IS\r\n      l_return BOOLEAN;\r\n   BEGIN\r\n      IF p_value_1 = p_value_2\r\n      THEN\r\n         l_return := TRUE;\r\n      ELSE\r\n         l_return := FALSE;\r\n      END IF;\r\n\r\n      RETURN l_return;\r\n   END is_equal;\r\nEND test_boolean;\r\n\/<\/pre>\n
Easy to use in PL\/SQL:<\/p>\n
BEGIN\r\n   IF test_boolean.is_equal(1,1)\r\n   THEN\r\n      dbms_output.put_line('The parameters are equal.');\r\n   END IF;\r\nEND;\r\n\/\r\n\r\nThe parameters are equal.<\/pre>\n
But when you use this in SQL, for example in a query:<\/p>\n
SELECT test_boolean.is_equal(1,1)\r\n  FROM dual;\r\n<\/pre>\n
You’ll receive some errors:<\/p>\n
ORA-06552: PL\/SQL: Statement ignored\r\nORA-06553: PLS-382: expression is of wrong type<\/pre>\n
When you want to use the function also in SQL, you usualy create a wrapper function.<\/p>\n
CREATE OR REPLACE PACKAGE test_boolean\r\nIS\r\n   FUNCTION is_equal( p_value_1 IN NUMBER\r\n                    , p_value_2 IN NUMBER\r\n                    )\r\n   RETURN BOOLEAN;\r\n\r\n   FUNCTION is_equal_num( p_value_1 IN NUMBER\r\n                        , p_value_2 IN NUMBER\r\n                        )\r\n   RETURN NUMBER;\r\nEND test_boolean;\r\n\/\r\n\r\nCREATE OR REPLACE PACKAGE BODY test_boolean\r\nIS\r\n   FUNCTION is_equal( p_value_1 IN NUMBER\r\n                    , p_value_2 IN NUMBER\r\n                    )\r\n   RETURN BOOLEAN\r\n   IS\r\n      l_return BOOLEAN;\r\n   BEGIN\r\n      IF p_value_1 = p_value_2\r\n      THEN\r\n         l_return := TRUE;\r\n      ELSE\r\n         l_return := FALSE;\r\n      END IF;\r\n\r\n      RETURN l_return;\r\n   END is_equal;\r\n\r\n   FUNCTION is_equal_num( p_value_1 IN NUMBER\r\n                        , p_value_2 IN NUMBER\r\n                        )\r\n   RETURN NUMBER\r\n   IS\r\n      l_return NUMBER(1);\r\n   BEGIN\r\n      IF is_equal(p_value_1, p_value_2)\r\n      THEN\r\n         l_return := 1;\r\n      ELSE\r\n         l_return := 0;\r\n      END IF;\r\n\r\n      RETURN l_return;\r\n   END is_equal_num;\r\nEND test_boolean;\r\n\/<\/pre>\n
This one still works:<\/p>\n
BEGIN\r\n   IF test_boolean.is_equal(1,1)\r\n   THEN\r\n      dbms_output.put_line('The parameters are equal.');\r\n   END IF;\r\nEND;\r\n\/\r\n\r\nThe parameters are equal.<\/pre>\n
But now this one is also working:<\/p>\n
SELECT test_boolean.is_equal_num(1,1) is_equal\r\n  FROM dual;\r\n\r\nIS_EQUAL\r\n--------\r\n       1<\/pre>\n
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”.<\/p>\n
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.<\/p>\n
Here’s what I do…<\/p>\n
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’, …<\/p>\n
This is what my package looks like:<\/p>\n
CREATE OR REPLACE PACKAGE subtypes\r\nIS\r\n   SUBTYPE st_boolean IS pls_integer range 0..1;\r\n   gc_boolean_false CONSTANT st_boolean := 0;\r\n   gc_boolean_true CONSTANT st_boolean := 1;\r\nEND;\r\n\/<\/pre>\n
I changed my function in my package to use my own booleans from the subtypes package:<\/p>\n
CREATE OR REPLACE PACKAGE test_boolean\r\nIS\r\n   FUNCTION is_equal( p_value_1 IN NUMBER\r\n                    , p_value_2 IN NUMBER\r\n                    )\r\n   RETURN subtypes.st_boolean;\r\nEND test_boolean;\r\n\/\r\n\r\nCREATE OR REPLACE PACKAGE BODY test_boolean\r\nIS\r\n   FUNCTION is_equal( p_value_1 IN NUMBER\r\n                    , p_value_2 IN NUMBER\r\n                    )\r\n   RETURN subtypes.st_boolean\r\n   IS\r\n      l_return subtypes.st_boolean;\r\n   BEGIN\r\n      IF p_value_1 = p_value_2\r\n      THEN\r\n         l_return := subtypes.gc_boolean_true;\r\n      ELSE\r\n         l_return := subtypes.gc_boolean_false;\r\n      END IF;\r\n\r\n      RETURN l_return;\r\n   END is_equal;\r\nEND test_boolean;\r\n\/<\/pre>\n
Both the test cases will work without a wrapper function(all though now we’ll have to compare in PL\/SQL):<\/p>\n
BEGIN\r\n   IF test_boolean.is_equal(1,1) = subtypes.gc_boolean_true\r\n   THEN\r\n      dbms_output.put_line('The parameters are equal.');\r\n   END IF;\r\nEND;\r\n\/\r\n\r\nThe parameters are equal.<\/pre>\n
SELECT test_boolean.is_equal(1,1) is_equal\r\n  FROM dual;\r\n\r\nIS_EQUAL\r\n--------\r\n       1<\/pre>\n
When you want to change the subtypes, you don’t have to change any code besides the subtypes package:<\/p>\n
CREATE OR REPLACE PACKAGE subtypes\r\nIS\r\n   SUBTYPE st_boolean IS VARCHAR2(1);\r\n   gc_boolean_false CONSTANT st_boolean := 'N';\r\n   gc_boolean_true CONSTANT st_boolean := 'Y';\r\nEND;\r\n\/<\/pre>\n
BEGIN\r\n   IF test_boolean.is_equal(1,1) = subtypes.gc_boolean_true\r\n   THEN\r\n      dbms_output.put_line('The parameters are equal.');\r\n   END IF;\r\nEND;\r\n\/\r\n\r\nThe parameters are equal.<\/pre>\n
But this time in SQL the outcome is a VARCHAR2(1) and contains ‘Y’:<\/p>\n
SELECT test_boolean.is_equal(1,1) is_equal\r\n  FROM dual;\r\n\r\nIS_EQUAL\r\n--------\r\nY<\/pre>\n
You can also use a DATE datatype for your boolean. Give it a try! <\/p>\n
(But you shouldn’t use a date in a “real” application environment, it’s not the most suitable datatype)<\/p>\n","protected":false},"excerpt":{"rendered":"
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 us…<\/p>\n","protected":false},"author":316191,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73236","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73236","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316191"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73236"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73236\/revisions"}],"predecessor-version":[{"id":91718,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73236\/revisions\/91718"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73236"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73236"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73236"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73236"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}