Oracle has come a long way since 9, but there are still a lot of businesses using it. If you're one of them, and also happen to enjoy PL/SQL, you should be aware of the dangers of using boolean values as function parameters.
Now, we all know that you can't use boolean in tables - it's strictly PL/SQL-only. However, the blur between what's considered PL/SQL and what's not might get you banging your head against the wall without realizing what's actually the problem. As is my preferred fashion, I'm going to give you the bullet points:
- Calling PL/SQL procedures and functions with boolean parameters within PL/SQL works without problems (of course)
- Calling PL/SQL procedures with boolean parameters using EXEC works fine
- Calling PL/SQL functions with boolean parameters using SELECT does not work
- Calling PL/SQL procedures and functions using JDBC prepared statements and trying to bind a variable to a boolean value does not work
- PL/SQL procedures and functions using boolean parameters with default values will work as long as you don't try to set them
- Trying to make an int2boolean(..) function for use with SELECT statements won't work, since to the parser it returns an unknown datatype
It's not supposed to be easy, is it? Usually, the solution is simply to either
- Overload the function/procedure so that it accepts both boolean and int values (0/1 or whatever you like), or..
- ..skip using boolean altogether
This code shows some of it:
create or replace function bool_me(p boolean default false) return int is
begin
if p = false then
return 0;
elsif p = true then
return 1;
else
--p is NULL
raise_application_error(-20000, 'p is NULL!');
end if;
end;
/
create or replace procedure bool_me2(p boolean default false) as
begin
if p = false then
dbms_output.put_line('p is false');
elsif p = true then
dbms_output.put_line('p is true');
else
--p is NULL
dbms_output.put_line('p is NULL');
end if;
end;
/
create or replace function return_true return boolean is
begin
return true;
end;
/
SQL> set serveroutput on
SQL> exec bool_me2(true);
p is true
SQL> exec bool_me2;
p is false
SQL> select bool_me(true) from dual;
select bool_me(true) from dual
*
FEIL på linje 1:
ORA-00904: "TRUE": invalid identifier
SQL> select bool_me from dual;
BOOL_ME
----------
0
SQL> select return_true from dual;
select return_true from dual
*
FEIL på linje 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
