-- Script to remove a value from the specified UDA's list of values
-- Input parameters:
--   <class name>     - The name of the class of the UDA
--   <attribute name> - The name of the UDA
--   <value>          - The value to be deleted from the list of values
--                      (in case if the UDA has type Date, the value
--                       should be in 'mm/dd/yyyy hh24:mi:ss' format)

WHENEVER OSERROR  EXIT 1 ROLLBACK;
WHENEVER SQLERROR EXIT 2 ROLLBACK;

set serveroutput on
set pause off
set pages 50000
set numw  6
set feedb 1
set verif off
set lines 500
set trims on
set recsep off
set arrays 1

set termout off
col username new_v username noprint
select decode(upper(user), 'SYSTEM', 'PDM.',
                           'SYS',    'PDM.',
              NULL) username
  from dual;
set termout on
--
DECLARE
CURSOR attr(cname VARCHAR2, aname VARCHAR2, aval VARCHAR2) IS
  SELECT cdef.clid, adef.claid, adef.clatype typid,
         enum.claeid, enum.claeseqnum, enum.claevalue val, 
         db.dbaattrtable tab, db.dbaattrcol col 
    FROM &&username.pdm_ClAttrEnum enum,
         &&username.pdm_ClassAttr  adef,
         &&username.pdm_ClassDef   cdef,
         &&username.pdm_DBAttrDef  db
   WHERE enum.claid = adef.claid
     AND adef.clid  = cdef.clid
     AND db.claid   = adef.claid
     AND adef.clauserdef          = 1
     AND UPPER(cdef.clname)       = UPPER(cname)
     AND adef.claname             = aname
     AND decode(adef.clatype, 2, decode(TO_NUMBER(SUBSTR(enum.claevalue,2)),         TO_NUMBER(aval), 1, 0),
                              3, decode(TO_NUMBER(SUBSTR(enum.claevalue,2)),         TO_NUMBER(aval), 1, 0),
                              6, decode(TO_DATE(SUBSTR(enum.claevalue,2),'J:SSSSS'), TO_DATE(aval, 'mm/dd/yyyy hh24:mi:ss'), 1, 0),
                                 decode(SUBSTR(enum.claevalue,2),                    aval, 1, 0)) = 1
         ;
CURSOR c(id NUMBER) IS SELECT claid, claeid  FROM &&username.pdm_clattrenum
                        WHERE claid = id
                        ORDER BY claid, claeseqnum
                          FOR UPDATE OF claeseqnum;
   cnt     NUMBER  :=0;
   sqlstm  VARCHAR2(2000);
   seq     INTEGER := 0;
   current NUMBER  := -9999;
   r       attr%ROWTYPE;
   exi     BOOLEAN := FALSE;
BEGIN
  IF '&&1' IS NULL OR '&&2' IS NULL OR '&&3' IS NULL THEN
     DBMS_OUTPUT.PUT_LINE('Input parameters: ');
     DBMS_OUTPUT.PUT_LINE('<class name> <attribute name> <value>');
  ELSE
    OPEN attr('&&1','&&2','&&3'); 
    FETCH attr INTO r; exi := attr%FOUND; 
    CLOSE attr;
    IF NOT exi THEN
      DBMS_OUTPUT.PUT_LINE('Class-&&1 , Attribute-&&2 , Value-&&3 not found!');
    ELSE
      sqlstm :=  'SELECT COUNT(*) FROM &&username.' || r.tab ||
                      ' WHERE ' || r.col || ' = ';
      IF r.typid    = 2 THEN 
         sqlstm := sqlstm || 'TO_NUMBER(''' || SUBSTR(r.val,2) || ''')';
      ELSIF r.typid = 3 THEN 
         sqlstm := sqlstm || 'TO_NUMBER(''' || SUBSTR(r.val,2) || ''')';
      ELSIF r.typid = 6 THEN
         sqlstm := sqlstm || 'TO_DATE(''' || SUBSTR(r.val,2) || ''',''J:SSSSS'')';
      ELSE 
         sqlstm := sqlstm || '''' || SUBSTR(r.val,2) || '''';
      END IF;
      BEGIN
        EXECUTE IMMEDIATE sqlstm INTO cnt ;              
        EXCEPTION WHEN OTHERS THEN NULL;
      END;
      IF cnt != 0 THEN 
         DBMS_OUTPUT.PUT_LINE('This value can not be deleted !');
         DBMS_OUTPUT.PUT_LINE(cnt||' reference[s] found.');
      ELSE
         DELETE &&username.pdm_clattrenum 
          WHERE claid = r.claid 
            AND claevalue = r.val;
         FOR i IN c(r.claid) LOOP
           IF current != i.claid THEN current := i.claid; seq := 0; END IF;
           UPDATE &&username.pdm_clattrenum SET claeseqnum = seq
            WHERE claeid = i.claeid AND claeseqnum != seq;
           seq := seq + 1;
         END LOOP;
         DBMS_OUTPUT.PUT_LINE('Class-&&1 , Attribute-&&2 , Value-&&3 is deleted !');
      END IF;
    END IF;
  END IF;
END;
/
--
EXIT;