Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic PL/SQL

Re: Dynamic PL/SQL

From: Robbert van der Hoorn <reply_at_forum.only>
Date: Fri, 6 Oct 2006 13:36:16 +0200
Message-ID: <45263fc7$0$4517$e4fe514c@news.xs4all.nl>

"Vince" <vinnyop_at_yahoo.com> schreef in bericht news:1160073138.503991.231850_at_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.
>

Vince,

as mentioned in other discussions, this kind of 'dynamic' or 'universal' implementations almost always lead to unmaintainable applications. How about performance, debugging etc? Why calling a procedure, trigger or whatever and try to find out runtime what you already knew at design-time? If you have to add columns as often as you suggest, you have either a bad design of an incomplete analysis.
If you want to reduce the workload for programmers, try to build or use a generator for your logging triggers, and regenerate whenever a change is made to the table structure. It's some work upfront, but it has great return on investment. Your *method* is still generic, but your *software* will be specific.

I have seen a lot of projects fail in some stage over these universal, dynamic, generic or what ever you call them methods. Call me old fashioned if you want!
Generic software is almost never verifiable against a (functional) design, is hard to debug, is hard to maintain, and hard to understand for new people in a project. Result is often that work arounds are created, not because the generic software does not work, but because it's not understood, or is not right for one single type of functionality, and in stead of changing the (misunderstood) generic software, workarounds are built.. And then all your effort on creating this generic solution is in vein.

Just an advice from an old fashioned project manager...

Robbert van der Hoorn
OSA it BV
The Netherlands Received on Fri Oct 06 2006 - 06:36:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US