On Aug 8, 9:58 am, Yuvaraj Sundaresan <[email protected]>
wrote:
> Dear friends,
> I need one urgent help, my requirement is one table many people will
> update at the end of the day i need all the update script of that table. So
> i used the trigger to build the update statement dynamically but its not
> working see the below coding and help to proceed. In this coding after
> execute immediate its not working.
>
> create or replace trigger TEST
> after insert OR UPDATE OR DELETE on person_table REFERENCING OLD AS OLD
> NEW AS NEW
> for each row
> declare
> SQL1 VARCHAR2(4000);
> SQL2 VARCHAR2(4000);
> SQL3 VARCHAR2(4000);
> SQL4 VARCHAR2(4000);
> begin
> IF INSERTING THEN
> NULL;
> ELSIF UPDATING THEN
> SQL1 := 'UPDATE person_table ';
> FOR I IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME =
> 'PERSON_TABLE') LOOP
> DBMS_OUTPUT.PUT_LINE(1);
> EXECUTE IMMEDIATE('SELECT :OLD.'||I.COLUMN_NAME||' FROM DUAL') INTO
> SQL2;
> DBMS_OUTPUT.PUT_LINE(2);
> EXECUTE IMMEDIATE('SELECT :NEW.'||I.COLUMN_NAME||' FROM DUAL') INTO
> SQL3;
> IF SQL2 <> SQL3 THEN
> IF SQL4 IS NOT NULL THEN
> SQL4 := SQL4 ||' , '||I.COLUMN_NAME||' = '||SQL3;
> ELSE
> SQL4 := SQL4 ||' , '||I.COLUMN_NAME||' = '||SQL3;
> END IF;
> END IF;
> END LOOP;
> DBMS_OUTPUT.PUT_LINE(3);
> SQL1 := SQL1 || SQL4 ||' WHERE NAME = '||:NEW.NAME;
> DBMS_OUTPUT.PUT_LINE(4||'--'||SQL1);
> INSERT INTO MY_SQL VALUES (SQL1);
> ELSIF DELETING THEN
> NULL;
> END IF;
> end TEST;
What, exactly, are you trying to do with this? The error I see is
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "BING.TEST", line 14
ORA-04088: error during execution of trigger 'BING.TEST'
because of the selects against DUAL you're trying to make
dynamically. Your trigger can be reduced to:
SQL> create or replace trigger TEST
2 after insert OR UPDATE OR DELETE on person_table -- REFERENCING
OLD AS OLD NEW AS NEW
3 for each row
4 declare
5 SQL1 VARCHAR2(4000);
6 SQL2 VARCHAR2(4000);
7 SQL3 VARCHAR2(4000);
8 SQL4 VARCHAR2(4000);
9 begin
10 IF INSERTING THEN
11 NULL;
12 ELSIF UPDATING THEN
13 SQL1 := 'UPDATE person_table set yarp = '||:new.yarp||',
znip = '''||:new.znip||''', pling = '''||:new.pling||''' where name =
'''||:new.name||'''';
14 DBMS_OUTPUT.PUT_LINE(1);
15 DBMS_OUTPUT.PUT_LINE(2);
16 DBMS_OUTPUT.PUT_LINE(3);
17 DBMS_OUTPUT.PUT_LINE('4 --'||SQL1);
18 INSERT INTO MY_SQL VALUES (SQL1);
19 ELSIF DELETING THEN
20 NULL;
21 END IF;
22 end TEST;
23 /
Trigger created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on size 1000000
SQL>
And it will function correctly:
SQL> insert into person_table
2 values(1, 'Bleebo','Tran', sysdate);
1 row created.
SQL>
SQL> update person_table
2 set name = 'Roopwinger' where yarp = 1;
1
2
3
4 --UPDATE person_table set yarp = 1, znip = 'Tran', pling = '09-
AUG-11' where name = 'Roopwinger'
1 row updated.
SQL>
SQL> select * from my_sql;
SQLTXT
------------------------------------------------------------------------------------------------------------------------------------
UPDATE person_table set yarp = 1, znip = 'Tran', pling = '09-AUG-11'
where name = 'Roopwinger'
SQL>
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
