Oracle Database PLSQL Subtype of certain characters

Question

I want to make a subtype in PLSQL which can only hold this values: '♠', '♥', '♦', '♣' Is this possible? In my code I have subtype suit_t is char(1) but with this code it is possible to store other characters in a variable with this subtype. And yeah.. my users are pretty dumb so I want to restrict them to do so.

Answer 1

I don't think it is possible to do this directly as a subtype.

My advice for this is to have a constant in a package to be used. Here is an example:

create or replace package P_SUIT is
  -- Purpose : Handle possible chars
  -- Public type declarations
  --type <TypeName> is <Datatype>;

  -- Public constant declarations
  SPADES   constant char:='♠';
  HEARTS   constant char:='♥';
  CLUBS    constant char:='♣';
  DIAMONDS constant char:='♦';

  -- Public variable declarations
  --<VariableName> <Datatype>;

  -- Public function and procedure declarations
  function validate(c char) return boolean;

end P_SUIT;

create or replace package body P_SUIT is
  -- Private type declarations
  --type <TypeName> is <Datatype>;

  -- Private constant declarations
  --<ConstantName> constant <Datatype> := <Value>;

  -- Private variable declarations
  --<VariableName> <Datatype>;

  -- Function and procedure implementations
  function validate(c char) return boolean is
    ret_val boolean := false;
  begin
    if c = P_SUIT.SPADES or c = P_SUIT.HEARTS or c = P_SUIT.CLUBS or c = P_SUIT.DIAMONDS then
      ret_val := true;
    else 
      ret_val := false;
    end if;
    return(ret_val);
  end;

begin
  -- Initialization
  --<Statement>;
  null;
end P_SUIT;

Now to use it in your PLSQL conditions:

begin 
  if p_suit.validate('o') then
       dbms_output.put_line('yes'); 
  else dbms_output.put_line('no'); 
  end if;
  if p_suit.validate('♠') then
       dbms_output.put_line('yes'); 
  else dbms_output.put_line('no'); 
  end if;
  if p_suit.validate(p_suit.HEARTS) then 
       dbms_output.put_line('yes'); 
  else dbms_output.put_line('no'); 
  end if;
end;