PLSQL - Creating procedure with parameter, but only allowing 2 values

Question

First off, I'm a DBA that dabbles in PL/SQL Programming. I have some knowledge, but some is most certainly lacking.

CREATE OR REPLACE PROCEDURE TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN VARCHAR2)
AS ....

I'd like to allow the P_STATUS parameter only be allowed values of 'E' or 'D' for enabling or disabling a trigger. I've done some searching, but can't seem to find the solution for this. Any help is greatly appreciated!

Thanks!

Jeremy

Answer 1

You could use an IF to check if the values is either E or D. If not, raise an error using raise_application_error:

CREATE OR REPLACE PROCEDURE TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN VARCHAR2)
AS 
begin
    if P_STATUS not in ('E', 'D') then
        raise_application_error(-20001, 'Invalid P_STATUS value - ' || P_STATUS);
    end if;

    . . . 
    . . .

end;
/