Dynamic PL/SQL

From: Vince <vinnyop_at_yahoo.com>
Date: 5 Oct 2006 11:32:18 -0700
Message-ID: <1160073138.503991.231850@c28g2000cwb.googlegroups.com>

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 running on Windoze XP, however will be moved to Linux (Redhat Enterprise 3).

I have a requirement that all data changes (via UI, mostly) must be recorded (who, what and why the change was made) permanently.

I have created a history table to record the above facts and wanted to use dynamic PL/SQL to implement the update via a trigger or directly call from the UI (havent decided which yet). I have created an overloaded proc to implement, one version where the pre-update record is known and one where it is not known (in the latter, I simply query the old record in the db and pass it, along with the other args into the former).

I chose dynamic PL/SQL because if new columns are added to the table, I do not want to have to revise my program (I will be doing this for many, many tables)

I am having trouble with the dynamic part as it appears to be firing out of the scope of the procedure that contains the execute immediate statement. I am getting the error p_old_record.study_id must be declared (this is the first column). I thought about creating bind variables to use instead, however, I will be faced with the same problem with the USING clause.

Any suggestions on how to make this possible or is there another approach (I'd like to make this work before resorting to hard coding all the columns). I realize I must do something about null values for the columns, but wanted to get this working before hand.

Here is my procedure (copied from its package):

  PROCEDURE upd( p_old_record patients%ROWTYPE,
                 p_new_record patients%ROWTYPE,
                 p_reason_id  update_reasons.id%TYPE,
                 p_user_id    system_users.id%TYPE )

  IS

      l_upd_record patient_updates%ROWTYPE;
      l_col        user_tab_columns.column_name%TYPE;
      l_sql        VARCHAR2(4000);

  BEGIN

      l_upd_record.patient_id       := p_new_record.id;
      l_upd_record.user_id          := p_user_id;
      l_upd_record.update_reason_id := p_reason_id;

      -- the following procedure checks package global of assoc array
of
      -- column names for table if empty, queries user_tab_columns to
get
     --  listing of column names
      populate_columns;

      -- g_cols = package global assoc array of column names
      FOR i IN g_cols.FIRST .. g_cols.LAST LOOP
          l_col := g_cols(i).column_name;
          l_sql :=

'BEGIN ' ||
'IF p_old_record.' || l_col || ' != p_new_record.' || l_col || ' THEN ' ||
'record_change( l_upd_record, ''' || l_col || ''', p_old_record.' || l_col ||

', p_new_record.' || l_col || '); ' ||
'END IF; ' ||
'END;';


          -- example output (formatted for ease of reading):

          --BEGIN


          --    IF p_old_record.study_id != p_new_record.study_id THEN

          --        record_change( l_upd_record, 'study_id',

          --                       p_old_record.study_id,

          --                       p_new_record.study_id);

          --    END IF;

          -- END;

          dbms_output.put_line(l_sql);



          -- fire anyonymous pl/sql block to determine if old rec's

value is


          -- different from new rec's value. If different, execute

          -- record_change procedure to log old and new values.

          -- Spec for record_change procedure:

          -- record_change( p_upd_record IN patient_updates%ROWTYPE,

          --                p_attribute  IN VARCHAR2,

          --                p_old_val    IN VARCHAR2,

          --                p_new_val    IN VARCHAR2

          EXECUTE IMMEDIATE l_sql;

      END LOOP;

  END upd;

my test case (with error messages):

declare

  l_new_rec patients%ROWTYPE;

begin

  l_new_rec.id            := 1;
  l_new_rec.study_id      := 1;
  l_new_rec.code          := '205';
  l_new_rec.study_site_id := 1;
  l_new_rec.initials      := 'OY';
  l_new_rec.date_of_birth := '01-jan-1946';
  l_new_rec.gender        := 'M';
  l_new_rec.status_code   := 'R';

  patient_update.upd( p_new_record => l_new_rec,
                      p_reason_id  => 1,
                      p_user_id    => 1 );

end;

ORA-06550: line 1, column 10:
PLS-00201: identifier 'P_OLD_RECORD.STUDY_ID' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-06512: at "PTDB.PATIENT_UPDATE", line 111
ORA-06512: at "PTDB.PATIENT_UPDATE", line 132
ORA-06512: at line 18

Thanks. Received on Thu Oct 05 2006 - 13:32:18 CDT