Error in Dynamic Query inside Oracle Trigger

Question

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

Alistair Wall · Accepted Answer · 2020-07-14 10:27:26Z

1

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;

answered Jul 14, 2020 at 10:27

Alistair Wall

3321 gold badge2 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roberto Hernandez · Accepted Answer · 2020-07-14 10:48:04Z

0

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;

answered Jul 14, 2020 at 10:48

Roberto Hernandez

8,5833 gold badges22 silver badges51 bronze badges

Collectives™ on Stack Overflow

Error in Dynamic Query inside Oracle Trigger

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related