title: Oracle PL/SQL: Loop Over Trigger Columns Dynamically
body: <p>Inside of a trigger I'm trying to loop over all columns on a table and compare the new values to the old values.  Here is what I have so far:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER "JOSH".TEST#UPD BEFORE 
  UPDATE ON "JOSH"."TEST_TRIGGER_TABLE" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
  declare    
     oldval varchar(2000);   
     newval varchar(2000);   
  begin    
     for row in (SELECT column_name from user_tab_columns where table_name='TEST_TRIGGER_TABLE') loop  
       execute immediate 'select :old.'||row.column_name||' from dual'   into oldval;  
       execute immediate 'select :new.'||row.column_name||' from dual'   into newval;  
       --Do something here with the old and new values
     end loop;  
  end;
  </code></pre>
  
  <p>The trigger compiles, but when the trigger fires, I'm getting:</p>
  
  <blockquote>
    <p>ORA-01008: not all variables bound</p>
  </blockquote>
  
  <p>on the first execute immediate because it's expecting a value for <code>:old</code>.  <code>:old</code> and <code>:new</code> are already defined as part of the trigger, but it appears that execute immediate can't see those variables.</p>
  
  <p>Is there a way to dynamically iterate over the column values in a trigger?</p>