referencing new variable using execute immediate in trigger

Question

I'm trying to reference my new variable from dynamic sql.

If I try select :NEW.zh_naam into v_var from dual;, and I print out my variable, averything works perfectly.

But when I try to use dynamic sql, like this execute immediate('select :NEW.zh_naam from dual') into v_var, I get an error message ORA-01008: not all variables bound.

Is there any work around for this problem?

Answer 1

execute immediate statements don't share variable scope with the caller. (Also : within the quoted statement indicates a bind variable.) You would have to pass the value in as a bind variable.

execute immediate 'select :b from dual' into v_var using :new.zh_naam;

Update: from the discussion below it seems you want to build the set of :new references dynamically. This is not possible. Instead you might be able to generate the entire trigger dynamically as part of your release process, or else enable the built-in Oracle auditing or Flashback Data Archive.