CREATE OR REPLACE PACKAGE genwhen AUTHID CURRENT_USER IS
FUNCTION generate(in_object_type IN VARCHAR2 DEFAULT 'TABLE',
in_object_name IN VARCHAR2) RETURN CLOB;
END genwhen;
Package created.
CREATE OR REPLACE PACKAGE BODY genwhen IS
FUNCTION generate(in_object_type IN VARCHAR2 DEFAULT 'TABLE',
in_object_name IN VARCHAR2) RETURN CLOB IS
l_result CLOB;
PROCEDURE pl(in_str IN VARCHAR2) IS
BEGIN
sys.dbms_lob.append(l_result, in_str);
sys.dbms_lob.append(l_result, chr(10));
END pl;
BEGIN
sys.dbms_lob.createtemporary(l_result, TRUE);
<<cols>>
FOR l_col_rec IN (SELECT column_name
FROM user_tab_columns
WHERE table_name = upper(in_object_name))
LOOP
IF sys.dbms_lob.getlength(l_result) > 0 THEN
pl('OR');
END IF;
pl('( OLD.' || l_col_rec.column_name || ' != NEW.' || l_col_rec.column_name);
pl('OR (OLD.' || l_col_rec.column_name || ' IS NULL AND NEW.' || l_col_rec.column_name || ' IS NOT NULL))');
pl('OR (OLD.' || l_col_rec.column_name || ' IS NOT NULL AND NEW.' || l_col_rec.column_name || ' IS NULL))');
END LOOP cols;
RETURN l_result;
END generate;
END genwhen;
Package Body created.
CREATE TABLE test_it (
pky INTEGER PRIMARY KEY,
nm VARCHAR2 (100),
dt DATE
)
Table created.
WITH
genwhen AS (
SELECT genwhen.generate(in_object_name => 'test_it') AS result
FROM dual
)
-- return each line in result clob as row (workaround for stripped newlines in Live SQL)
SELECT regexp_substr(result, '[^' || chr(10) || ']+', 1, level) AS result_line
FROM genwhen
CONNECT BY instr(result, chr(10), 1, level - 1) BETWEEN 1 AND length(result) - 1
RESULT_LINE | ( 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)) |
---|