Re: :new in Dynamic sql

On 25 May 2007 07:59:48 -0700, Amit <amitdictionary_at_gmail.com> wrote:

>CREATE TRIGGER ......
>....
>DECLARE
>CURSOR cur IN SELECT column_name FROM user_tab_cols WHERE table_name =
>ora_dict_obj_name;
>sQuery VARCHAR2(1000);
>BEGIN
>FOR data IN cur LOOP
>-- double single quote in UPDATE text
>sQuery := 'INSERT INTO auditor_table VALUES (' || ':NEW.' ||
>data.column_name || ', :OLD.' || data.column_name || ', ''UPDATE'')';
>EXECUTE IMMEDIATE sQuery;
>END LOOP;
>END;
>
>I am getting below error:
>"Not all the variable are bound"
>Oracle is treating :NEW as a find variable and throwing error, Please
>advice.

Just the idea of this set up is horrendous. Dynamic sql in a trigger is a recipe for an unscalable application.

You can write pl/sql to generate your *static* trigger code.

-- 
Sybrand Bakker
Senior Oracle DBA