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?

fyi, this has come up before: stackoverflow.com/…

William Robertson
– William Robertson

2016-10-06 10:24:34 +00:00
Commented Oct 6, 2016 at 10:24 — William Robertson
– William Robertson, Commented Oct 6, 2016 at 10:24

Community · Accepted Answer · 2017-05-23 11:48:20Z

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.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 6, 2016 at 9:56

William Robertson

16.1k4 gold badges41 silver badges49 bronze badges

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

8 Comments

Walle Over a year ago

Thanks for the answer. I suppose I can't make the ':new.zh_naam' variable?

XING Over a year ago

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.

William Robertson Over a year ago

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 if that's any help.

Wernfried Domscheit Over a year ago

Do simply v_var := :new.zh_naam;

William Robertson Over a year ago

@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.

|

Collectives™ on Stack Overflow

referencing new variable using execute immediate in trigger

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related