"Boolean" parameter for Oracle stored procedure

Question

I'm aware that Oracle does not have a boolean type to use for parameters, and am currently taking in a NUMBER type which would have 1/0 for True/False (instead of the 'Y'/'N' CHAR(1) approach).

I'm not a very advanced Oracle programmer, but after doing some digging and reading some ASKTOM posts, it seems like you can restrict a field using a format for the column like:

MyBool NUMBER(1) CHECK (MyBool IN (0,1))

Is there a way to apply the same sort of a check constraint to an input parameter to a stored procedure? I'd like to restrict the possible inputs to 0 or 1, rather than checking for it explicitly after receiving the input.

Answer 1

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) is
begin
   if p_num not in (0,1) then
      raise_application_error(-20001,'p_num out of range');
   end if;
   ...

Answer 2

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