CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperdatabase-trigger
Sreenath Ganga
Sreenath Ganga

Reputation: 736

Error in Dynamic Query inside Oracle Trigger

I am trying to create a Query to convert the new data of table to xml at the trigger

create or replace TRIGGER EVAL_CHANGE_TriggerActual_Test
  AFTER INSERT OR UPDATE OR DELETE
  ON PROJ_TEST
  REFERENCING NEW AS new OLD AS old
 FOR EACH ROW
DECLARE
 p_xmldata     XMLtype;
  P_NEWROWDATA    clob;
  p_newrowxml       clob;  
BEGIN

p_newrowxml:='select XMLElement("ResearchTable",XMLElement("DESCR", :NEW.DESCR)) from dual';
EXECUTE IMMEDIATE  p_newrowxml  into p_xmldata   ; //Error here 
 p_newrowdata:=p_xmldata.getClobVal();  
END;

If I remove the NEW.DESCR to some static it will work

NB: i need to keep the query as a string because later it will be generated by some SP

Upvotes: 0

Views: 41

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

One solution might be to assign the new value to a variable instead, then use the variable into the dynamic sql statement.

create or replace TRIGGER EVAL_CHANGE_TriggerActual_Test
  AFTER INSERT OR UPDATE OR DELETE
  ON PROJ_TEST
  REFERENCING NEW AS new OLD AS old
 FOR EACH ROW
DECLARE
 p_xmldata     XMLtype;
  P_NEWROWDATA    clob;
  p_newrowxml       clob;  
  my_var          PROJ_TEST.descr%type;
BEGIN
my_var := :new.descr;
p_newrowxml:='select XMLElement("ResearchTable",XMLElement("DESCR", '||my_var||' )) from dual';
EXECUTE IMMEDIATE  p_newrowxml  into p_xmldata   ; 
 p_newrowdata:=p_xmldata.getClobVal();  
END;

Upvotes: 0

Alistair Wall
Alistair Wall

Reputation: 332

It looks as thought :NEW is not available in the context of the statement. Can you use p_newrowxml:='select XMLElement("ResearchTable",XMLElement("DESCR", :1)) from dual'; EXECUTE IMMEDIATE p_newrowxml into p_xmldata using :NEW.DESCR;

Upvotes: 1

Related Questions