good night. I have a problem with the EXECUTE IMMEDIATE in oracle. I look in others topics, but no answer was helpful. This is the code: First, the table that i need insert inside of trigger...
create global temporary table TEMP_PK (COL_NAME VARCHAR(100), COL_TYPE VARCHAR(100), ROW_VALUE VARCHAR(100)) on commit preserve rows;
Now, my trigger
CREATE OR REPLACE TRIGGER ICidade AFTER
INSERT ON Cidade FOR EACH ROW DECLARE TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
col_name_aux VARCHAR(100);
col_type_aux VARCHAR(100);
stm VARCHAR(4000):='';
BEGIN
-- Pegando as PKS
FOR j IN
(SELECT d.Column_Name coluna,
d.DATA_TYPE tipo
FROM user_cons_columns ucc,
user_constraints uc,
(SELECT COLUMN_NAME,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='CIDADE'
) d
WHERE uc.constraint_name=ucc.constraint_name
AND uc.constraint_type ='P'
AND uc.table_name = 'CIDADE'
AND d.COLUMN_NAME =ucc.Column_Name
)
LOOP
stm := concat
(
stm, 'INSERT INTO TEMP_PK VALUES ('
)
;
stm := concat(stm, j.coluna);
stm := concat(stm,', ');
stm := concat(stm, j.tipo);
stm := concat(stm,', ');
stm := concat(stm, ':NEW.');
stm := concat(stm,j.coluna);
stm := concat(stm,')');
EXECUTE IMMEDIATE stm;
stm:='';
END LOOP;
END ICidade;
/
Is compiling good. The problem is when I add an entry in the table "Cidade" When I try to insert, for example:
Insert into Cidade Values (11,'Alegre','ES');
When I run without the immediate execute, using the print function, this is the result:
INSERT INTO TEMP_PK VALUES ( CODCIDADE, NUMBER, :NEW.CODCIDADE);
With the EXECUTE IMMEDIATE, gives the following error:
Error starting at line : 62 in command - Insert into Cidade VAlues (11,'Alegre','ES') Error report - SQL Error: ORA-00936: missing expression ORA-06512: at "PAULA.ICIDADE", line 34 ORA-04088: error during execution of trigger 'PAULA.ICIDADE' 00936. 00000 - "missing expression"
Probably is an idiot error, but i can't find.
So can anyone help me? Since now, thank you.