[Home] [Help]
1: package body cz_base_mgr as
2: /* $Header: czbsmgrb.pls 120.9.12020000.2 2012/08/17 14:38:07 smanna ship $ */
3:
4: RECORD_COUNTER INTEGER:=0;
5:
42: ' fetch tab_cursor bulk collect into '||p_pk_cols_tbl_str||' limit '||TO_CHAR(BATCH_SIZE)||'; ' ||
43: ' exit when (tab_cursor%NOTFOUND and pk_col1_tbl.COUNT = 0); ' ||
44: ' FORALL j in pk_col1_tbl.FIRST..pk_col1_tbl.LAST ' ||
45: ' update '||p_table_name||' set deleted_flag = ''1'' where '||p_update_where_str||'; ' ||
46: ' CZ_BASE_MGR.WRITE_TO_DB_LOGS(''CZ_PURGE_LOG'',''Logical Delete of table : '||p_table_name||''', '''||p_table_name||''', pk_col1_tbl.COUNT, 1); ' ||
47: ' COMMIT; ' ||
48: ' END LOOP; ' ||
49: 'END;';
50:
69: ' fetch tab_cursor bulk collect into '||p_pk_cols_tbl_str||' limit '||TO_CHAR(BATCH_SIZE)||'; ' ||
70: ' exit when (tab_cursor%NOTFOUND and pk_col1_tbl.COUNT = 0); ' ||
71: ' FORALL j in pk_col1_tbl.FIRST..pk_col1_tbl.LAST ' ||
72: ' delete from '||p_table_name||' where '||p_update_where_str||'; ' ||
73: ' CZ_BASE_MGR.WRITE_TO_DB_LOGS(''CZ_PURGE_LOG'',''Physical Delete of table : '||p_table_name||''', '''||p_table_name||''', pk_col1_tbl.COUNT, 2); ' ||
74: ' COMMIT; ' ||
75: ' END LOOP; ' ||
76: 'END;';
77:
144: );
145:
146: EXCEPTION
147: WHEN OTHERS THEN
148: insert into cz_db_logs (caller, message, logtime) values ('cz_base_mgr', 'exec_it failure: '|| p_table_name, sysdate);
149: raise;
150: END exec_it;
151:
152: PROCEDURE exec
602: begin
603: RECORD_COUNTER:=0;
604:
605: begin
606: select TO_NUMBER(VALUE) into CZ_BASE_MGR.BATCH_SIZE from CZ_DB_SETTINGS
607: where upper(setting_id)='BATCHSIZE';
608: exception
609: when no_data_found then
610: null;
859: end loop;
860: dbms_sql.close_cursor(cur);
861: exception
862: when OTHERS then
863: LOG_REPORT('CZ_BASE_MGR.exec','Error : '||SQLERRM||' : statement : "'||var_stmt||'"');
864: end;
865:
866: /*>>>>>>>>>>>>>>>>>>SUN Bug: 12346474 for Additional Logging during Purge >>>>>>
867: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/