1

I have created 2 tables

create table t1 ( issue_date date, xml_col xmltype);

create table t2 ( col1 VARCHAR2(30 CHAR), col2 VARCHAR2(30 CHAR), col3 VARCHAR2(100 CHAR), col4 VARCHAR2(10 CHAR), col5 VARCHAR2(100 CHAR));

Moreover I have a trigger after insert on t1, which calls a procedure in order to parse the xml_col and assign values to t2:

create or replace TRIGGER t1_TRGR
AFTER INSERT ON t1
FOR EACH ROW

DECLARE
  new_REC     t1%ROWTYPE;
BEGIN
  new_REC.issue_date := :NEW.issue_date;
  new_REC.xml_col    := :NEW.xml_col; 

  update_t2(new_REC);

END t1_TRGR;
/



PROCEDURE update_t2(new_rec IN t1%rowtype )
AS
pragma autonomous_transaction;

  CURSOR GET_T2_CUR( p_xml_data  IN XMLTYPE ) IS
   SELECT *
     FROM XMLTABLE('/xml/Page/contents/item'  
              PASSING p_xml_data
              COLUMNS    
                 COL1   VARCHAR2(30  CHAR) PATH 'col1',
                 COL2   VARCHAR2(30  CHAR) PATH 'col2',
                 COL3   VARCHAR2(100 CHAR) PATH 'col3',
                 COL4   VARCHAR2(10  CHAR) PATH 'col4',
                 COL5   VARCHAR2(100 CHAR) PATH 'col5');

  v_xml         XMLTYPE;
  v_counter     INTEGER := 0;
  v_error_num   INTEGER := 0;
  v_limit       INTEGER(6) := 50;
  v_commit      INTEGER(6) := 50;

  e_bulk_errors  exception;
  PRAGMA exception_init(e_bulk_errors, -24381);

  --Types
  type t2_recs is table of T2%rowtype index by pls_integer;
  v_t2_tab t2_recs; 

BEGIN
  v_xml := new_rec.xml_col;

  open GET_T2_CUR(v_xml);
  loop
     begin
        fetch GET_T2_CUR bulk collect into v_t2_tab limit v_limit;

             forall i in v_t2_tab.first .. v_t2_tab.last save exceptions
                    insert into t2 values v_t2_tab(i);
                           v_counter := v_counter + sql%rowcount;
         exit when GET_T2_CUR%notfound;

     exception
        when e_bulk_errors then
             v_counter   := v_counter + sql%rowcount;
             v_error_num := v_error_num + sql%bulk_exceptions.count();

             for j in 1 .. sql%bulk_exceptions.count() loop
               null;
             end loop;
           commit;
     end;

     if mod(v_counter + v_error_num, v_commit) = 0 then
        commit;
     end if;

  end loop;

  close GET_T2_CUR;
  commit;

END update_t2;
/

INSERT INTO t1 VALUES(SYSDATE, 
XMLTYPE('<xml><Page><contents>
      <item> 
        <col1>11111111</col1>
        <col2>345</col2>
        <col3>asdada</col3>
        <col4>gfhgfh</col4>
        <col5>wwrregf3</col5>
      </item>
      <item> 
        <col1>1145476511111</col1>
        <col2>34ewt5</col2>
        <col3>aswerdada</col3>
        <col4>gfhgf345h</col4>
        <col5>wwrsdf45egf3</col5>
      </item></contents></Page>
  <Page><contents><item> 
        <col1>7899</col1>
        <col2>fgd</col2>
        <col3>ukkilik</col3>
        <col4>gfhgfh</col4>
        <col5>fdghhhh</col5>
      </item>
      <item> 
        <col1>045435</col1>
        <col2>34ewt5</col2>
        <col3>rtiu</col3>
        <col4>gfhgf345h</col4>
        <col5>kokyu</col5>
      </item>
    </contents></Page></xml>'));

The performance is slow.

Any ideas please ? for index or something else ..?

--oracle version 11.1

1
  • Having a COMMIT inside a trigger is always a bad idea. Commented Feb 6, 2019 at 15:09

1 Answer 1

1

I don't see any reason for such an overkilling procedure. Create a view from the table:

CREATE VIEW t2 as
SELECT *
FROM XMLTABLE('/xml/Page/contents/item'  
          PASSING p_xml_data
          COLUMNS    
             COL1   VARCHAR2(30  CHAR) PATH 'col1',
             COL2   VARCHAR2(30  CHAR) PATH 'col2',
             COL3   VARCHAR2(100 CHAR) PATH 'col3',
             COL4   VARCHAR2(10  CHAR) PATH 'col4',
             COL5   VARCHAR2(100 CHAR) PATH 'col5');

Or make a simple insert:

INSERT INTO t2 (col1, col2, col3, col4, col5)
SELECT *
FROM XMLTABLE('/xml/Page/contents/item'  
          PASSING p_xml_data
          COLUMNS    
             COL1   VARCHAR2(30  CHAR) PATH 'col1',
             COL2   VARCHAR2(30  CHAR) PATH 'col2',
             COL3   VARCHAR2(100 CHAR) PATH 'col3',
             COL4   VARCHAR2(10  CHAR) PATH 'col4',
             COL5   VARCHAR2(100 CHAR) PATH 'col5');
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.