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