Oracle dynamic sql with Trigger using :new and :old variables

Question

I am trying to simplify large trigger code into concise code using all_tab_columns since table contains 200 columns. Some reason when tried with dynamic sql, it does not let me update declared variables.

DECLARE
v_new_rec SOME_TABLE%ROWTYPE;
v_sql VARCHAR(4000);
BEGIN

  SELECT 'v_new_act.' || lower(column_name) || ' := :new.' || lower(column_name)
    INTO v_sql
    FROM all_tab_columns
   WHERE table_name = 'SOME_TABLE'
ORDER BY column_id;

EXECUTE IMMEDIATE v_sql USING v_new_rec;

EXEC my_stored_proc(v_new_rec);

END;
/

Any suggestions???

Answer 1

This approach does not and can not work. You can't dynamically refer to the :new or :old pseudo-record.

If you want to go down this path, you'd realistically want to write dynamic SQL that generated the trigger, not dynamic SQL within the trigger. Something along the lines of (obviously untested)

l_sql := 'CREATE OR REPLACE TRIGGER trg_' || l_table_name ||
         '  BEFORE INSERT ON ' || l_table_name ||
         '  FOR EACH ROW ' ||
         'DECLARE ' ||
         '  l_rec ' || l_table_name || '%ROWTYPE' ||
         'BEGIN ';
for cols in (select * from all_tab_cols where table_name = l_table_name)
loop
  l_sql := l_sql || ' l_rec.' || cols.column_name || 
                       ' = :new.' || cols.column_name || ';';
end loop;
...

Alternately, if you want to declare your table based on object types, :new and :old would be actual instances of the object type that could then be passed to your stored procedure.