CODEWITHSUNDEEP
oracle-databaseplsqlparametersprocedure
JeremyS
JeremyS

Reputation: 35

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

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

Upvotes: 3

Views: 1624

Answers (3)

J. Chomel
J. Chomel

Reputation: 8395

A solution would also be to use a Boolean, since you have only two choices.

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

From this interesting article, it is advise to create a separate function to validate your input if you want some kind of ENUM in PLSQL (externalize the if GurV suggests).

So what is a guy to do?

  1. If you want to use enum in a table, use a check constraint.
  2. If you want to use enum in a stored procedure, write a separate procedure to validate the input.

What you could also do, if more than 2 values: If you can afford to store global variables in a package and also advise your developers to look for constants in a defined package (GUI like PLSQL Developer or alike make it very easy to use):

CREATE OR REPLACE PACKAGE global_vars IS
  P_STATUS_enable  CONSTANT varchar2(2) := 'E';
  P_STATUS_disable CONSTANT varchar2(2) := 'D';
  P_STATUS_drop    CONSTANT varchar2(2) := 'Dr';
end global_vars;
/

  create or replace procedure TRIGGER_PRC (P_TRGNAME IN VARCHAR2, P_STATUS IN varchar2)
  AS 
  begin
    if P_STATUS = global_vars.P_STATUS_enable then
        -- do something
        dbms_output.put_line('ENABLE');
    elsif P_STATUS = global_vars.P_STATUS_disable then
      --  p_status = P_STATUS_disable
        dbms_output.put_line('DISABLE');
    elsif P_STATUS = global_vars.P_STATUS_drop then
      -- do other stuff
        dbms_output.put_line('DROP?');
    end if;

  end TRIGGER_PRC;
/


begin
  TRIGGER_PRC ('TRIG', global_vars.P_STATUS_enable);
end;
/

Upvotes: 0

g00dy
g00dy

Reputation: 6778

I'd rather take the following approach:

CREATE OR REPLACE PROCEDURE ENABLE_TRIGGER_PRC (P_TRGNAME IN VARCHAR2)
AS ....
BEGIN
  -- Enable the trigger P_TRGNAME here
END;

CREATE OR REPLACE PROCEDURE DISABLE_TRIGGER_PRC (P_TRGNAME IN VARCHAR2)
AS ....
BEGIN
  -- Disable the trigger P_TRGNAME here
END;

You can make the second to call the first etc., but you should always have the IF statement (as GurV mentioned), to validate it once inside the Procedure. In the future, you might want to add drop as well. I suggest you put those into a package, so that they are all consolidated there, having one procedure to execute the actual statement, say in execute immediate, so that all the other procedures can reuse the same code.

Cheers

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

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;
/

Upvotes: 3

Related Questions