Why write code when you can generate it?
CREATE OR REPLACE PROCEDURE genwhen (tab_in IN VARCHAR2)
IS
SUBTYPE identifier_t IS VARCHAR2 (100);
v_tab identifier_t := UPPER (tab_in);
CURSOR cols_cur
IS
SELECT *
FROM user_tab_columns
WHERE table_name = v_tab;
PROCEDURE pl (str IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (str);
END;
BEGIN
pl ('WHEN (');
FOR rec IN cols_cur
LOOP
IF cols_cur%ROWCOUNT != 1
THEN
pl (' OR');
END IF;
pl ('( OLD.' || rec.column_name || ' != NEW.' || rec.column_name);
pl (
'OR (OLD.'
|| rec.column_name
|| ' IS NULL AND NEW.'
|| rec.column_name
|| ' IS NOT NULL))');
pl (
'OR (OLD.'
|| rec.column_name
|| ' IS NOT NULL AND NEW.'
|| rec.column_name
|| ' IS NULL))');
END LOOP;
pl (')');
END;
Procedure created.
CREATE TABLE test_it
(
pky INTEGER PRIMARY KEY,
nm VARCHAR2 (100),
dt DATE
)
Table created.
BEGIN
genwhen (tab_in => 'TEST_IT');
END;
WHEN (
( OLD.PKY != NEW.PKY
OR (OLD.PKY IS NULL AND NEW.PKY IS NOT NULL))
OR (OLD.PKY IS NOT NULL AND NEW.PKY IS NULL))
OR
( OLD.NM != NEW.NM
OR (OLD.NM IS NULL AND NEW.NM IS NOT NULL))
OR (OLD.NM IS NOT NULL AND NEW.NM IS NULL))
OR
( OLD.DT != NEW.DT
OR (OLD.DT IS NULL AND NEW.DT IS NOT NULL))
OR (OLD.DT IS NOT NULL AND NEW.DT IS NULL))
)