Re: :new in Dynamic sql

"Amit" <amitdictionary_at_gmail.com> a écrit dans le message de news: 1180105188.103727.108340_at_k79g2000hse.googlegroups.com...
| 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.
|

You can't do what you want to do in this way or other one. Write a static query. There is no reason to use a dynamic one.

Regards
Michel Cadot Received on Fri May 25 2007 - 10:44:32 CDT