CODEWITHSUNDEEP
oracle-databaseplsql
gpa
gpa

Reputation: 2451

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

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

Upvotes: 1

Views: 4556

Answers (2)

Justin Cave
Justin Cave

Reputation: 231711

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.

Upvotes: 3

smnbbrv
smnbbrv

Reputation: 24551

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

Upvotes: 4

Related Questions