CREATE OR REPLACE FUNCTION xxx(p_limit NUMBER DEFAULT 1000000,p_min_pop_id NUMBER DEFAULT NULL,p_max_pop_id NUMBER DEFAULT NULL) RETURN CHAR AS l_min_pop_id NUMBER; l_max_pop_id NUMBER; l_cnter NUMBER := 0; l_next_pop_id NUMBER; BEGIN IF p_min_pop_id IS NOT NULL AND p_max_pop_id IS NOT NULL THEN l_min_pop_id := p_min_pop_id ; l_max_pop_id := p_max_pop_id ; ELSE SELECT min(mdm_pop_id) min_pop_id,max(mdm_pop_id) max_pop_id INTO l_min_pop_id ,l_max_pop_id FROM mdm.mdm_pop_tt WHERE mdm_valid_to_dttm is not null; END IF; l_next_pop_id := l_min_pop_id; WHILE l_next_pop_id <= l_max_pop_id LOOP l_cnter := l_cnter + 1; INSERT INTO mdm.mdm_pop_tt_history SELECT tt.*,SYSDATE FROM mdm.mdm_pop_tt tt WHERE tt.mdm_pop_id BETWEEN l_next_pop_id AND (l_next_pop_id + p_limit) AND tt.mdm_valid_to_dttm IS NOT NULL; DELETE FROM mdm.mdm_pop_tt tt WHERE tt.mdm_pop_id BETWEEN l_next_pop_id AND (l_next_pop_id + p_limit) AND tt.mdm_valid_to_dttm IS NOT NULL; --dbms_output.put_line(l_cnter || '- Iteration :-' || l_next_pop_id || '-' || (l_next_pop_id + p_limit)); COMMIT; l_next_pop_id:= l_next_pop_id + p_limit + 1; END LOOP; DBMS_STATS.gather_table_stats(ownname => 'MDM',tabname => 'MDM_pop_TT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all indexed columns', cascade => true); DBMS_STATS.gather_table_stats(ownname => 'MDM',tabname => 'MDM_pop_TT_HISTORY',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all indexed columns', cascade => true); RETURN 'Y'; EXCEPTION WHEN OTHERS THEN --dbms_output.put_line(l_cnter || 'ERROR:-' || SQLCODE || '- ' || SQLERRM); RETURN 'N'; END;
... View more