Re: Dynamic PL/SQL

Vince wrote:
> 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.

NDS was not meant to create/execute 'dynamic' PL/SQL; as you've found out. You lose scope because of the context change.

David Fitzjarrell Received on Thu Oct 05 2006 - 14:08:52 CDT