1 PACKAGE BODY "AZ_DELETE" AS
2 /* $Header: azdeleteb.pls 120.1.12000000.2 2007/02/23 06:32:11 lmathur noship $ */
3 COMMIT_BATCH_SIZE NUMBER;
4 v_dml_count NUMBER := 0;
5 /**********************************************************/
6 --Procedure to delete all records irrespective to the 'source'
7 PROCEDURE delete_all(p_request_id IN NUMBER,
8 p_table_name IN VARCHAR2) IS
9
10
11 v_source_list TYP_NEST_TAB_VARCHAR;
12 v_id_list TYP_NEST_TAB_NUMBER;
13
14 BEGIN
15 EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=''SIMILAR''';
16 COMMIT_BATCH_SIZE := FND_PROFILE.VALUE('AZ_COMMIT_ROWCOUNT');
17 COMMIT_BATCH_SIZE := COMMIT_BATCH_SIZE*2; --can take more records for deletion
18 --gather stats on the table from which the records are to be deleted
19 FND_STATS.GATHER_TABLE_STATS('AZ',p_table_name);
20 execute immediate 'select distinct(source) from '||p_table_name||
21 ' where request_id='||p_request_id
22 BULK COLLECT INTO v_source_list;
23
24 IF v_source_list.COUNT >0 THEN
25 FOR i IN 1 .. v_source_list.COUNT LOOP
26 BEGIN
27 delete_source(p_request_id,v_source_list(i),p_table_name);
28 EXCEPTION
29
30 WHEN OTHERS THEN
31 raise_application_error(-20001,
32 'DELETE_ALL: Error while deleting records for a given source:'||v_source_list(i));
33 END;
34 END LOOP; -- loop
35 END IF; -- the number of distinct source > 0
36
37 COMMIT;
38
39 EXCEPTION
40
41 WHEN OTHERS THEN
42 raise_application_error(-20001,
43 'Error occurred while deleting records for request id:'||p_request_id);
44 FND_STATS.GATHER_TABLE_STATS('AZ',p_table_name);
45 END delete_all;
46
47
48 -- procedure to delete all the records for a given source
49 PROCEDURE delete_source(p_request_id IN NUMBER,p_source IN VARCHAR2,p_table_name IN VARCHAR2)
50 IS
51 v_id_list TYP_NEST_TAB_NUMBER;
52 TYPE cur_type IS REF CURSOR;
53 cur_id cur_type;
54
55 BEGIN
56 open cur_id for 'select id from '||p_table_name||
57 ' where request_id=:1 and source=:2'
58 USING p_request_id,p_source;
59 LOOP
60 FETCH cur_id BULK COLLECT INTO v_id_list LIMIT COMMIT_BATCH_SIZE;
61
62 FORALL i IN 1..v_id_list.COUNT
63 execute immediate 'delete from '||p_table_name||' where
64 request_id=:1 and source=:2 and
65 id=:3' using p_request_id,p_source,v_id_list(i);
66 COMMIT;
67 EXIT WHEN cur_id%NOTFOUND;
68 END LOOP;
69 COMMIT;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 raise_application_error(-20001,
74 'Error in deletion for source:'||p_source);
75 END delete_source;
76
77
78 END;
[Home] [Help]