2

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?

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Walle
  • 540
  • 1
  • 9
  • 32

1 Answers1

2

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.

Community
  • 1
  • 1
William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • Thanks for the answer. I suppose I can't make the ':new.zh_naam' variable? – Walle Oct 06 '16 at 09:58
  • I'm not sure what you mean. – William Robertson Oct 06 '16 at 09:59
  • The thing is, I want to create a trigger which archives a table dynamically. If someone adds a column to the table, I don't want to rewrite the trigger to archive the new column as well. So I get all the columns from the table of an external function, a function that returns me ':new.zh_naam,:new.an_extra_column' for example. I want to use that variable to 'read' the :new.zh_naam and :new.an_extra_column variables in my trigger. Is it a little bit more clear now? I'm not that good in explaining this stuff in English. – Walle Oct 06 '16 at 10:04
  • 2
    You cannot do that. :new and :old are reserved key words and get the values when some event occurs on the table. When you write it in dynamic query, it means you wanted to use it as a bind variable. – XING Oct 06 '16 at 10:11
  • 1
    I'm afraid that won't be possible. You could perhaps generate the entire trigger dynamically as part of your release process, or else enable the built-in Oracle auditing or [Flashback Data Archive](https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1) if that's any help. – William Robertson Oct 06 '16 at 10:17
  • 1
    Do simply `v_var := :new.zh_naam;` – Wernfried Domscheit Oct 06 '16 at 10:55
  • 1
    @WernfriedDomscheit - agreed, if it was just a case of getting the value of `:new.zh_naam` into `v_var` that would be the way to do it. Unfortunately he wants the trigger code to figure out all of the `:new` names dynamically at runtime. – William Robertson Oct 06 '16 at 10:58
  • That is correct. Too bad it is impossible. I'll try to figure out another way. Thanks for the responses! – Walle Oct 06 '16 at 11:11