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???

smnbbrv · Accepted Answer · 2013-12-12 17:29:06Z

4

you cannot do that: the variables :new and :old are out of scope in dynamic SQL.

You cannot do it also as a rowtype/Oracle type - I tried many times with different things.

All you can do - generate a full trigger code dynamically. I do it for triggers which reload all the things to history table on the table data change.

And as far as your trigger is very big in size (if it exceeds 32767 bytes in length) you should use dbms_sql package instead of execute immediate

answered Dec 12, 2013 at 17:29

smnbbrv

24.7k9 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Cave · Accepted Answer · 2013-12-12 17:33:08Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related