"Boolean" parameter for Oracle stored procedure
You can use Booleans as parameters to stored procedures:
procedure p (p_bool in boolean) is...
However you cannot use Booleans in SQL, e.g. select statements:
select my_function(TRUE) from dual; -- NOT allowed
For a number parameter there is no way to declaratively add a "check constraint" to it, you would have to code some validation e.g.
procedure p (p_num in number) isbegin if p_num not in (0,1) then raise_application_error(-20001,'p_num out of range'); end if; ...
Yes and no.You can do..
create or replace package t_bool is subtype t_bool_num IS PLS_INTEGER RANGE 0..1; function f_test (i_bool_num t_bool_num) return varchar2;end t_bool;/create or replace package body t_bool is function f_test (i_bool_num t_bool_num) return varchar2 is begin if i_bool_num = 0 then return 'false'; elsif i_bool_num = 1 then return 'true'; elsif i_bool_num is null then return 'null'; else return to_char(i_bool_num); end if; end;end t_bool;/
The good news is that, if you do
exec dbms_output.put_line(t_bool.f_test(5));
it reports an error.
The bad news is that if you do
select t_bool.f_test(5) from dual;
then you don't get an error