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