SQL & PL/SQL

Hi,

I am using Oracle Database 11g R2. I had made a trigger which stops users from performing any 'DROP' operations on the schema. The main intentions are that any user working on that schema should not be able to drop any table.

On the other-side this trigger also stops the user to drop any CONSTRAINT.

I want a trigger at the schema level which should not allow 'DROP TABLE' but should allow 'DROP CONSTRAINT'.

The code i have written is as follows :-

CREATE OR REPLACE TRIGGER TRG_DDL_SCHEMA_LOG
BEFORE DROP OR TRUNCATE
ON BANK.schema

DECLARE
  oper                 ddl_schema_log.ora_sysevent%TYPE;
  OBJ                  ddl_schema_log.ora_dict_obj_name%type;
  V_USER_NAME          varchar2(100);
  V_CONSTRAINT_PRESENT number := 1;
BEGIN
  SELECT ora_sysevent INTO oper FROM DUAL;

  select ora_dict_obj_name into OBJ from dual;

  select UPPER(SYS_CONTEXT('USERENV', 'OS_USER'))
    into V_USER_NAME
    from dual;
    
  BEGIN
    select count(*)
      into V_CONSTRAINT_PRESENT     -- I tried a logic here that it should check that if this object in consideration is present in the constraints view then 
      from dba_constraints c                 -- this trigger should not do anything. But this logic also fails. can anyone clear this point?
     where c.owner = 'BANK'
       and c.constraint_name = ora_dict_obj_name;
    If V_CONSTRAINT_PRESENT > 0 Then
      NULL;
      GOTO FINISH;
    END if;
  END;

 
    If OBJ like '%_W' or OBJ = 'CZ_NAB_MW_PK_SANDSTONE_EOD_BAL' then
    
      null;
    
    else
    
      P_DDL_LOG_SCHEMA(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);
    
      IF oper = 'DROP' THEN
        RAISE_APPLICATION_ERROR(-20998,
                                'Attempt To Drop In Database Is Not Allowed and Has Been Logged');
      ELSIF oper = 'TRUNCATE' THEN
        RAISE_APPLICATION_ERROR(-20999,
                                'Attempt To Truncate A Database Table Is Not Allowed and Has Been Logged');
      END IF;
    
    END IF;
  
  END IF;
  <<FINISH>>
  NULL;
END TRG_DDL_SCHEMA_LOG;

Also i tried to build a logic in which it will search if that object is in the dba_constraints view, if found then the trigger should do nothing.
Please let me know any ideas.
Thanks in advance.