1 PACKAGE BODY qa_performance_temp_pkg AS
2 /* $Header: qatempb.pls 120.1 2005/06/09 07:58:38 appldev $ */
3
4
5 --
6 -- Utility function to parse a comma-separated list of
7 -- integers into a number array. Return the no. of
8 -- elements parsed.
9 --
10 FUNCTION parse_integers(
11 p_ids VARCHAR2,
12 x_ids OUT NOCOPY dbms_sql.number_table)
13 RETURN NUMBER IS
14
15 separator CONSTANT VARCHAR2(1) := ',';
16 n INTEGER;
17 l_start INTEGER;
18 l_comma INTEGER;
19 l_length INTEGER;
20
21 BEGIN
22 n := 0;
23 l_start := 1;
24 l_length := length(p_ids);
25
26 --
27 -- Keeping l_start position and l_comma position
28 -- variables avoids an unnecessary copy of the
29 -- input variable for maximum memory performance.
30 -- The following WHILE also covers the case where
31 -- input p_ids is NULL, thus l_length is null and
32 -- condition is not matched.
33 --
34 WHILE l_start <= l_length LOOP
35 n := n + 1;
36 l_comma := instr(p_ids, separator, l_start);
37 IF l_comma > 0 THEN -- a comma is found
38 x_ids(n) := substr(p_ids, l_start, l_comma-l_start);
39 l_start := l_comma+1;
40 ELSE -- final case
41 x_ids(n) := substr(p_ids, l_start);
42 EXIT;
43 END IF;
44 END LOOP;
45
46 RETURN n;
47
48 END parse_integers;
49
50 --
51 -- Parse the input ID list (comma-separated) and
52 -- insert each individual IDs into the temp table.
53 --
54 -- The input p_id_list must be a list of comma-separated
55 -- integer IDs.
56 --
57 PROCEDURE add_ids(p_key VARCHAR2, p_id_list VARCHAR2) IS
58 --
59 -- Parse the ID list into an array of integers.
60 -- Then use bulk operation to insert all values
61 -- to the temp table. The order is immaterial
62 -- because an IN operation does not care.
63 --
64 l_ids dbms_sql.number_table;
65 BEGIN
66
67 IF parse_integers(p_id_list, l_ids) > 0 THEN
68 FORALL i IN l_ids.FIRST .. l_ids.LAST
69 INSERT INTO qa_performance_temp (key, id)
70 VALUES (p_key, l_ids(i));
71 END IF;
72
73 END add_ids;
74
75
76 --
77 -- A simple purge API to delete the IN list values
78 -- for a particular key.
79 --
80 PROCEDURE purge(p_key VARCHAR2) IS
81
82 BEGIN
83 --
84 -- This is a full table scan operation but we
85 -- do not foresee any issue because this is
86 -- a temp table and there is not more than
87 -- a few dozen rows maximum at any time.
88 -- bso Sat Apr 16 14:31:23 PDT 2005
89 --
90 DELETE FROM qa_performance_temp
91 WHERE key = p_key;
92 END purge;
93
94
95 --
96 -- Since the above are being called often from pld
97 -- it is more performing to create a wrapper for
98 -- purge and add with once server call.
99 --
100 PROCEDURE purge_and_add_ids(
101 p_key VARCHAR2,
102 p_id_list VARCHAR2) IS
103
104 BEGIN
105 purge(p_key);
106 add_ids(p_key, p_id_list);
107 END purge_and_add_ids;
108
109
110
111
112 -- Bug 4345779. Audits project.
113 -- Added the following methods.
114 -- srhariha. Wed Jun 1 12:13:02 PDT 2005.
115
116 PROCEDURE add_names(p_key VARCHAR2, p_names dbms_sql.VARCHAR2_TABLE) IS
117
118 BEGIN
119
120 IF p_names IS NOT NULL THEN
121 FORALL i IN p_names.FIRST .. p_names.LAST
122 INSERT INTO qa_performance_temp (key, name)
123 VALUES (p_key, p_names(i));
124 END IF;
125
126 END add_names;
127
128
129
130 PROCEDURE purge_and_add_names(p_key VARCHAR2, p_names dbms_sql.VARCHAR2_TABLE) IS
131
132 BEGIN
133 purge(p_key);
134 add_names(p_key,p_names);
135
136 END purge_and_add_names;
137
138 -- End 4345779.
139
140 END qa_performance_temp_pkg;
[Home] [Help]