Skip to Main Content
  • Questions
  • Global Temporary Table - Commit issue over database link

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 04, 2017 - 5:07 pm UTC

Last updated: June 05, 2017 - 3:13 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Below SQL gives 696 rows which I am processing in BULK collect 300

SELECT ROWID FROM TB_EMPLOYEE_TEST WHERE DT <=  TO_DATE('01/01/2008', 'MM/DD/YYYY');

Global temporary table is created with ON COMMIT DELETE ROWS; still data in global table is not getting deleted after commit.

 

I have shared below two blocks with PUT_LINE output.

 

Can you please help to understand why Global temp table is not getting flushed here when COMMIT;

 

 

create or replace TYPE "TAB_ARRAY" is table of varchar2(4000);

/

 

 

CREATE GLOBAL TEMPORARY TABLE tb_global_temp

(           "ROW_ID" VARCHAR2(50 BYTE)

) ON COMMIT DELETE ROWS

/

 

This is with COMMIT option..

set SERVEROUTPUT ON;

declare

  TYPE r_cursor IS REF CURSOR;

  c_cur_var r_cursor;

  n_count number;

  tb_arr_tab_id TAB_ARRAY;

begin

 

OPEN c_cur_var FOR SELECT ROWID FROM TB_EMPLOYEE_TEST WHERE DT <=  TO_DATE('01/01/2008', 'MM/DD/YYYY');

LOOP

    FETCH c_cur_var BULK COLLECT INTO tb_arr_tab_id LIMIT 300;

    EXIT WHEN tb_arr_tab_id.COUNT = 0;

 

                        FORALL i IN tb_arr_tab_id.FIRST .. tb_arr_tab_id.LAST

             INSERT INTO tb_global_temp VALUES (tb_arr_tab_id(i));

      DBMS_OUTPUT.PUT_LINE('1....tb_arr_tab_id.COUNT>>>>'||tb_arr_tab_id.COUNT);

 

    insert into TB_EMPLOYEE_TEST_1@db

    SELECT * FROM TB_EMPLOYEE_TEST WHERE ROWID IN (SELECT ROW_ID from tb_global_temp);

    DBMS_OUTPUT.PUT_LINE('ROWCOUNT>>>>'||SQL%ROWCOUNT);

   

   --execute immediate 'truncate table tb_global_temp';

    commit;

    tb_arr_tab_id.DELETE;

end loop;

 

exception when others then

rollback;

  DBMS_OUTPUT.PUT_LINE('OTHERS>>>>'||SQLERRM);

END;

-----------------------------------------------------------------------

1....tb_arr_tab_id.COUNT>>>>300

ROWCOUNT>>>>300

1....tb_arr_tab_id.COUNT>>>>300

ROWCOUNT>>>>600

1....tb_arr_tab_id.COUNT>>>>96

ROWCOUNT>>>>696

-----------------------------------------------------------------------

--########################################################################--

 

 

But when used TRUNCATE Global table is getting flushed in 2nd iteration

 

set SERVEROUTPUT ON;

declare

  TYPE r_cursor IS REF CURSOR;

  c_cur_var r_cursor;

  n_count number;

  tb_arr_tab_id TAB_ARRAY;

begin

 

OPEN c_cur_var FOR SELECT ROWID FROM TB_EMPLOYEE_TEST WHERE DT <=  TO_DATE('01/01/2008', 'MM/DD/YYYY');

LOOP

    FETCH c_cur_var BULK COLLECT INTO tb_arr_tab_id LIMIT 300;

    EXIT WHEN tb_arr_tab_id.COUNT = 0;

 

                        FORALL i IN tb_arr_tab_id.FIRST .. tb_arr_tab_id.LAST

             INSERT INTO tb_global_temp VALUES (tb_arr_tab_id(i));

      DBMS_OUTPUT.PUT_LINE('1....tb_arr_tab_id.COUNT>>>>'||tb_arr_tab_id.COUNT);

 

    insert into TB_EMPLOYEE_TEST_1@db

    SELECT * FROM TB_EMPLOYEE_TEST WHERE ROWID IN (SELECT ROW_ID from tb_global_temp);

    DBMS_OUTPUT.PUT_LINE('ROWCOUNT>>>>'||SQL%ROWCOUNT);

   

   execute immediate 'truncate table tb_global_temp';

    --commit;

    tb_arr_tab_id.DELETE;

end loop;

 

exception when others then

rollback;

  DBMS_OUTPUT.PUT_LINE('OTHERS>>>>'||SQLERRM);

END;

-----------------------------------------------------------------------

1....tb_arr_tab_id.COUNT>>>>300

ROWCOUNT>>>>300

1....tb_arr_tab_id.COUNT>>>>300

ROWCOUNT>>>>300

1....tb_arr_tab_id.COUNT>>>>96

ROWCOUNT>>>>96

and Chris said...

This is because you're inserting rows over a database link. As discussed in MOS note 2254676.1, distributed transactions aren't supported for temporary tables.

You need to manually delete the rows or truncate as you have done.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Chris!!

A reader, June 06, 2017 - 5:49 am UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library