CREATE OR REPLACE TRIGGER ti_device_addports
AFTER INSERT ON devices
FOR EACH ROW
DECLARE
BEGIN
  IPDB_MODEL_PORTS.NEW_DEVICE(:NEW.device_id, :NEW.device_model_id, :NEW.creator_id);
END;

• Package

CREATE OR REPLACE
PACKAGE "IPDB_DEVICES" AS
  PROCEDURE ...
  PROCEDURE ...
 
  TYPE integer_varray IS VARRAY(100) OF INTEGER;
  v_affected_devices  INTEGER_VARRAY := integer_varray();
  v_flag BOOLEAN := FALSE;
END;

• Row level trigger

CREATE OR REPLACE
TRIGGER TI_DEVICE_UPDATE_PATH AFTER INSERT OR UPDATE ON DEVICES 
FOR EACH ROW
WHEN (NEW.parent_device_id != OLD.parent_device_id OR NEW.device_name != OLD.device_name)
BEGIN
    ipdb_devices.v_flag := TRUE;
    ipdb_devices.v_affected_devices.EXTEND;
    ipdb_devices.v_affected_devices(ipdb_devices.v_affected_devices.COUNT) := :NEW.device_id;
END;

• After statement trigger

CREATE OR REPLACE
TRIGGER TI_DEVICE_UPDATE_PATH_EXEC AFTER INSERT OR UPDATE ON DEVICES
BEGIN
  IF (ipdb_devices.v_flag) THEN
    ipdb_devices.v_flag := FALSE;
    FOR i IN 1..ipdb_devices.v_affected_devices.COUNT LOOP   
      ipdb_devices.update_device_path(ipdb_devices.v_affected_devices(i));      
    END LOOP;
    ipdb_devices.v_flag := TRUE;
    -- nullime vanad andmed
    ipdb_devices.v_affected_devices.DELETE;   
  END IF;
END;

CREATE OR REPLACE
PACKAGE BODY "X_TEST" AS
  FUNCTION kala(v_in IN VARCHAR2) RETURN VARCHAR2 AS
    v_bc_addr VARCHAR2(128);
    v_dev devices%ROWTYPE;
  BEGIN
    TYPE changed_device IS RECORD (device devices%ROWTYPE, action VARCHAR2(50));
    TYPE x_varray IS VARRAY(100) OF changed_device;
    v_affected_devices  X_VARRAY := x_varray();
 
    SELECT * INTO v_dev FROM devices WHERE device_id=133115;
    x_test.v_affected_devices.EXTEND;
    x_test.v_affected_devices(x_test.v_affected_devices.COUNT).device := v_dev;
    x_test.v_affected_devices(x_test.v_affected_devices.COUNT).action := 'do it!';
 
    dbms_output.put_line(x_test.v_affected_devices(1).device.device_name);
 
    RETURN 'kala';
  END;
END;

• Custum exeption:

raise_application_error(-20002, 'Sloti tüüp ei kata seadme tüüpi');

  FUNCTION bin2int (bin VARCHAR2)
  RETURN PLS_INTEGER IS
  len PLS_INTEGER := LENGTH(bin);
  BEGIN
    IF NVL(len,1) = 1 THEN
      RETURN bin;
    ELSE RETURN
      2 * bin2int(SUBSTR(bin,1,len-1)) + SUBSTR(bin,-1);
    END IF;
  END bin2int;

  FUNCTION int2bin(INT PLS_INTEGER)
  RETURN VARCHAR2 IS
  BEGIN
  IF INT > 0 THEN
    RETURN int2bin(TRUNC(INT/2))||
      SUBSTR('01',MOD(INT,2)+1,1);
  ELSE
    RETURN NULL;
  END IF;
  END int2bin;

  FUNCTION bin_to_IP(p_bin VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    IF LENGTH(p_bin) != 32 THEN
      RETURN NULL;
    ELSE
      RETURN bin2int(SUBSTR(p_bin, 1, 8)) || '.' || bin2int(SUBSTR(p_bin, 9, 8)) || '.' || bin2int(SUBSTR(p_bin, 17, 8)) || '.' || bin2int(SUBSTR(p_bin, 25, 8));
    END IF;
  END;

  FUNCTION IP_to_bin(p_ip VARCHAR2) RETURN VARCHAR2 AS
    v_bin VARCHAR2(8);
    v_bin_ip VARCHAR2(32) := '';
  BEGIN
    FOR nr IN 1 .. 4 LOOP
      -- teisendame kymnend numbri kahendnumbirks
      v_bin := int2bin(get_token(p_ip, nr, '.'));
 
      -- kahendnumber peab olema alati 8 kohta (vajadusel lisame nullid ette)
      IF LENGTH(v_bin) != 8 THEN
        FOR i IN LENGTH(v_bin) .. 7 LOOP
          v_bin := '0' || v_bin;
        END LOOP;
      END IF;
 
      v_bin_ip := v_bin_ip || v_bin;
    END LOOP;
 
    RETURN v_bin_ip;
  END;