Triigger: Referencing Items Indirectly in PL/SQL

Within a database trigger, I'm attempting to indirectly reference the correlation variables :new.columnX and :old.columnX. I've used the code from RevealNet and hard-coded it into my database trigger.

Logical View of what I'm trying is this:

new_col_value := :new.col_name

where col_name is fetched from user_tab_columns

Actual Code:

DBMS_SQL.PARSE(sql_cursor, 'BEGIN :val := ' || ':new.'|| col_name || '; END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(sql_cursor, 'val', 'a', 2000); sql_ret := DBMS_SQL.EXECUTE(sql_cursor); DBMS_SQL.VARIABLE_VALUE(sql_cursor,'val', new_col_value);

I do not get any compile errors but runtime errors do occur:

'SOME VALUES ARE NOT BOUND'
the dynamic sql parse is attempting to bind the correlation column :new, I've tried to remove the ':' but I get the runtime error:

'NEW.COL1' IS NOT DEFINED'
The statement above does work with global and local variables and is the PL/SQL implementation of the NAME_IN function within forms. The only problem I can't seem to figure out is how do I get the above statements not to parse :new.column_nameX as a bind variable but as the trigger correlation variable since the ':' is required to reference the new and old values.

Is there a way to get these values from any internal tables X$ or $ or V$ which would avoid the ':'?

I appreciate any help in this matter - I've searched everywhere that I know of. Please email if possible with any solution I may have missed.

Thx

George Wong
gcwong_at_interlog.com

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue May 12 1998 - 00:00:00 CDT