-
Hi ,
I created a database trigger. In that i am dynamically inserting the values.
the questions is i am using EXECUTE IMMEDIATE.
when i am executing the database trigger, data is not inserting into the corresponding table.
can anyone help me for this issue..
i am using Oracle8i under HPunix 10.2.
I copied my coding here..
Jgun..
CREATE OR REPLACE TRIGGER T_DYN AFTER
INSERT ON EMP FOR EACH ROW
DECLARE
CURSOR C1 IS
SELECT COLUMN_NAME
FROM SYS.USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP';
v_ins_col VARCHAR2(100);
v_ins_val VARCHAR2(100);
v_ins_col_st VARCHAR2(100);
v_ins_val_st VARCHAR2(100);
v_column_name VARCHAR2(100);
BEGIN
v_ins_col := ' ';
v_ins_val := ' ';
v_ins_col_st := 'INSERT INTO AUDIT_EMP(';
v_ins_val_st := 'VALUES(';
OPEN C1;
WHILE 1=1 LOOP
FETCH C1 INTO v_column_name;
IF (C1%NOTFOUND) THEN
EXIT;
ELSE
IF (v_ins_col != ' ') THEN
v_ins_col := v_ins_col || ',';
END IF;
IF (v_ins_val != ' ') THEN
v_ins_val := v_ins_val || ',';
END IF;
END IF;
v_ins_col := v_ins_col || v_column_name;
v_ins_val := v_ins_val ||':new.'|| v_column_name;
END LOOP;
CLOSE C1;
v_ins_col := v_ins_col_st ||v_ins_col || ')';
v_ins_val := v_ins_val_st||v_ins_val || ')';
v_ins_col := v_ins_col ||v_ins_val;
EXECUTE IMMEDIATE v_ins_col;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
/
-
Execute Immediate
Dear Friend,
There are some mistakes in you trigger;
please first replace dbms_output.put_line(sqlcode)
with
Raise_Application_Error(-20001,sqlcode)
now recompile you trigger.
insert a row into emp table
you will get error messages
it will also error line no where your Execute Immediate
command is given.
your are missing "Using" clause in Execute Immediate command.
In youor script, you are not getting any error regarding Excetion because you using Dbms_Output.Put_line command
which does not work for trigger. so use Raise_Application_Error() instead.
better use of Execute Immediate command is for
dynamic SQl statement
use can use this command in procedure to create a table.
see the example
SQL>DECLARE
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE T1 (T1 NUMBER)';
END;
PL/SQL procedure successfully completed.
SQL> DESC T1;
Name Null? Type
------------------------------- -------- ----
T1 NUMBER
Thanks
Pitamber Soni
-
