[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_MV_UTIL_PVT
Source
1 PACKAGE BODY oki_dbi_mv_util_pvt AS
2 /* $Header: OKIRIMUB.pls 115.8 2003/04/29 22:07:11 rpotnuru noship $ */
3
4 PROCEDURE drop_mv_log
5 ( p_mv_log IN VARCHAR2
6 ) IS
7
8 MV_LOG_NOTEXISTS EXCEPTION ;
9 PRAGMA exception_init(MV_LOG_NOTEXISTS, -12002) ;
10 BEGIN
11 EXECUTE IMMEDIATE 'drop materialized view log on ' || p_mv_log ;
12 EXCEPTION
13 WHEN MV_LOG_NOTEXISTS THEN NULL;
14 WHEN OTHERS THEN null ;
15 END drop_mv_log ;
16
17 procedure create_mv_log
18 ( p_base_table IN VARCHAR2
19 , p_column_list IN VARCHAR2
20 , p_sequence_flag IN VARCHAR2
21 , p_rowid IN VARCHAR2
22 , p_new_values IN VARCHAR2
23 , p_data_tablespace IN VARCHAR2
24 , p_index_tablespace IN VARCHAR2
25 , p_next_extent IN VARCHAR2
26 ) IS
27
28 MV_LOG_EXISTS EXCEPTION ;
29
30 PRAGMA exception_init(MV_LOG_EXISTS, -12000) ;
31
32 l_sequence VARCHAR2(20) := 'SEQUENCE' ;
33 l_rowid VARCHAR2(20) := 'ROWID' ;
34 l_data_tablespace VARCHAR2(30) := p_data_tablespace ;
35 l_index_tablespace VARCHAR2(30) := p_index_tablespace ;
36 l_column_list VARCHAR2(3000) ;
37
38 BEGIN
39
40 IF (p_sequence_flag = 'N') THEN
41 l_sequence := '' ;
42 END IF ;
43
44 IF (p_rowid = 'N') THEN
45 l_rowid := '' ;
46 ELSIF (p_sequence_flag = 'Y') THEN
47 l_sequence := 'SEQUENCE,' ;
48 END IF ;
49
50 IF (p_column_list IS NOT NULL) THEN
51 l_column_list := '(' || p_column_list || ')' ;
52 END IF ;
53
54 IF p_data_tablespace IS NULL THEN
55
56 l_data_tablespace := ad_mv.g_mv_data_tablespace;
57 l_index_tablespace := ad_mv.g_mv_index_tablespace;
58
59 END IF ;
60
61
62 EXECUTE IMMEDIATE
63 ' CREATE MATERIALIZED VIEW LOG ON ' || p_base_table ||
64 ' TABLESPACE '||l_data_tablespace ||
65 ' INITRANS 4 MAXTRANS 255 ' ||
66 ' STORAGE(INITIAL 4K NEXT ' || p_next_extent ||
67 ' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)' ||
68 ' WITH '|| l_sequence || l_rowid || l_column_list ||
69 ' INCLUDING NEW VALUES' ;
70
71
72 EXCEPTION
73 WHEN MV_LOG_EXISTS THEN
74 NULL ;
75 WHEN OTHERS THEN
76 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
77 fnd_message.set_name( application => 'FND'
78 , name => 'CRM-DEBUG ERROR' ) ;
79 fnd_message.set_token( token => 'ROUTINE'
80 , value => 'OKI_DBI_MV_UTIL_PVT.create_mv_log ' ) ;
81 bis_collection_utilities.put_line(fnd_message.get) ;
82 bis_collection_utilities.put_line( ' p_base_table : ' || p_base_table ) ;
83 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
84 END create_mv_log ;
85
86 PROCEDURE drop_mv
87 ( p_mv IN VARCHAR2
88 ) IS
89 mv_notexists EXCEPTION;
90 PRAGMA EXCEPTION_INIT(mv_notexists, -12006);
91 BEGIN
92 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || p_mv;
93 EXCEPTION
94 WHEN mv_notexists THEN
95 NULL;
96 WHEN OTHERS THEN NULL ;
97 END drop_mv ;
98
99 PROCEDURE create_mv
100 ( p_mv_name IN VARCHAR2
101 , p_mv_sql IN VARCHAR2
102 , p_build_mode IN VARCHAR2
103 , p_refresh_mode IN VARCHAR2
104 , p_enable_qrewrite IN VARCHAR2
105 , p_partition_flag IN VARCHAR2
106 , p_next_extent IN VARCHAR2
107 ) IS
108
109 l_data_tablespace VARCHAR2(30) ;
110 l_index_tablespace VARCHAR2(30) ;
111 l_build_mode VARCHAR2(20) := 'DEFERRED' ;
112 l_refresh_mode VARCHAR2(20) := 'FAST' ;
113 l_enable_qrewrite VARCHAR2(30) := 'DISABLE' ;
114 l_storage VARCHAR2(256) ;
115 l_partition_clause VARCHAR2(2000) ;
116
117 l_sql VARCHAR2(32767) ;
118
119 mv_exists EXCEPTION ;
120
121 PRAGMA EXCEPTION_INIT(mv_exists, -12006) ;
122
123 BEGIN
124
125
126 IF p_build_mode = 'I' THEN
127 l_build_mode := 'IMMEDIATE' ;
128 END IF ;
129
130 IF p_refresh_mode = 'C' THEN
131 l_refresh_mode := 'COMPLETE' ;
132 END IF ;
133
134 IF p_enable_qrewrite = 'Y' THEN
135 l_enable_qrewrite := 'ENABLE' ;
136 END IF ;
137
138 l_storage := 'STORAGE(INITIAL 4K
139 NEXT ' || p_next_extent ||
140 ' MINEXTENTS 1
141 MAXEXTENTS UNLIMITED PCTINCREASE 0)';
142
143 IF (p_partition_flag = 'Y') THEN
144 l_partition_clause := ' PARTITION by LIST (grp_id)
145 (PARTITION quarter VALUES (7) PCTFREE 10 PCTUSED 80 '||l_storage||',
146 PARTITION month VALUES (11) PCTFREE 10 PCTUSED 80 '||l_storage||',
147 PARTITION week VALUES (13) PCTFREE 10 PCTUSED 80 '||l_storage||',
148 PARTITION day VALUES (14) PCTFREE 10 PCTUSED 80 '||l_storage||'
149 ) ';
150 END IF;
151
152
153 l_data_tablespace := ad_mv.g_mv_data_tablespace;
154 l_index_tablespace := ad_mv.g_mv_index_tablespace;
155
156 l_sql :=
157 ' CREATE MATERIALIZED VIEW ' || p_mv_name ||
158 ' TABLESPACE ' || l_data_tablespace ||
159 ' INITRANS 4 MAXTRANS 255 ' ||
160 l_storage || l_partition_clause ||
161 ' BUILD ' || l_build_mode ||
162 ' USING INDEX TABLESPACE ' || l_index_tablespace ||
163 ' STORAGE (INITIAL 4K NEXT '||p_next_extent||
164 ' MAXEXTENTS UNLIMITED PCTINCREASE 0) '||
165 ' REFRESH ' || l_refresh_mode || ' ON DEMAND ' ||
166 -- ' with <rowid|primary key> ' ||
167 l_enable_qrewrite || ' QUERY REWRITE ' ||
168 ' AS ' ||
169 p_mv_sql ;
170
171 ad_mv.create_mv(p_mv_name, l_sql);
172
173 EXCEPTION
174 WHEN mv_exists THEN
175 NULL ;
176 WHEN OTHERS THEN
177 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
178 fnd_message.set_name( application => 'FND'
179 , name => 'CRM-DEBUG ERROR' ) ;
180 fnd_message.set_token( token => 'ROUTINE'
181 , value => 'OKI_DBI_MV_UTIL_PVT.create_mv ' ) ;
182 bis_collection_utilities.put_line(fnd_message.get) ;
183 bis_collection_utilities.put_line( ' p_mv_name : ' || p_mv_name ) ;
184 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
185 END create_mv ;
186
187 PROCEDURE create_mv_index
188 ( p_mv_name IN VARCHAR2
189 , p_ind_name IN VARCHAR2
190 , p_ind_col_list IN VARCHAR2
191 , p_unique_flag IN VARCHAR2
192 , p_ind_type IN VARCHAR2
193 , p_next_extent IN VARCHAR2
194 , p_partition_type IN VARCHAR2
195 ) IS
196
197 l_data_tablespace VARCHAR2(30) ;
198 l_index_tablespace VARCHAR2(30) ;
199 l_unique VARCHAR2(10) ;
200 l_index_type VARCHAR2(10) ;
201 l_partition_clause VARCHAR2(100) ;
202
203 mv_index_exists EXCEPTION ;
204
205 PRAGMA EXCEPTION_INIT(mv_index_exists, -955) ;
206
207 BEGIN
208
209 IF (p_unique_flag = 'Y') THEN
210 l_unique := 'UNIQUE ';
211 END IF ;
212
213 IF (p_ind_type = 'M') THEN
214 l_index_type := 'BITMAP' ;
215 END IF ;
216
217 IF (p_partition_type = 'L') THEN
218 l_partition_clause := ' LOCAL ' ;
219 ELSIF (p_partition_type = 'G') THEN
220 l_partition_clause := ' GLOBAL ' ;
221 END IF ;
222
223 l_data_tablespace := ad_mv.g_mv_data_tablespace;
224 l_index_tablespace := ad_mv.g_mv_index_tablespace;
225
226 EXECUTE IMMEDIATE
227 ' CREATE ' || l_unique || l_index_type || ' index ' || p_ind_name||
228 ' ON ' || p_mv_name || '(' || p_ind_col_list || ' ) ' ||
229 ' TABLESPACE '|| l_index_tablespace || l_partition_clause ||
230 ' INITRANS 4 MAXTRANS 255' ||
231 ' STORAGE(INITIAL 4K NEXT ' || p_next_extent ||
232 ' MAXEXTENTS UNLIMITED PCTINCREASE 0)' ;
233 EXCEPTION
234 WHEN mv_index_exists THEN
235 NULL ;
236 WHEN OTHERS THEN
237 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
238 fnd_message.set_name( application => 'FND'
239 , name => 'CRM-DEBUG ERROR' ) ;
240 fnd_message.set_token( token => 'ROUTINE'
241 , value => 'OKI_DBI_MV_UTIL_PVT.create_mv_Index ' ) ;
242 bis_collection_utilities.put_line(fnd_message.get) ;
243 bis_collection_utilities.put_line( ' p_mv_name : ' || p_mv_name ) ;
244 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
245 END create_mv_index;
246
247 PROCEDURE drop_index
248 ( p_index_name IN VARCHAR2
249 ) IS
250 mv_index_doesnotexist exception;
251 PRAGMA EXCEPTION_INIT(mv_index_doesnotexist, -1418);
252 BEGIN
253 EXECUTE IMMEDIATE 'DROP INDEX ' || p_index_name ;
254 EXCEPTION
255 WHEN mv_index_doesnotexist THEN NULL;
256 WHEN OTHERS THEN NULL ;
257 END drop_index ;
258
259
260 PROCEDURE refresh
261 ( p_mv_name IN VARCHAR2
262 , p_parallel_degree IN NUMBER
263 ) IS
264 BEGIN
265 DBMS_MVIEW.REFRESH(
266 list => p_mv_name
267 , method => '?'
268 , parallelism => p_parallel_degree ) ;
269 EXCEPTION
270 WHEN OTHERS THEN
271 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
272 fnd_message.set_name( application => 'FND'
273 , name => 'CRM-DEBUG ERROR' ) ;
274 fnd_message.set_token( token => 'ROUTINE'
275 , value => 'OKI_DBI_MV_UTIL_PVT.refresh ' ) ;
276 bis_collection_utilities.put_line(fnd_message.get) ;
277 bis_collection_utilities.put_line( ' p_mv_name : ' || p_mv_name ) ;
278 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
279 END refresh ;
280
281 PROCEDURE crt_mx
282 ( p_id IN NUMBER
283 , p_user_id IN NUMBER
284 , p_run_date IN DATE
285 , p_login_id IN NUMBER
286 ) IS
287 BEGIN
288 INSERT INTO oki_dbi_multiplexer_b(
289 id
290 , created_by
291 , last_update_login
292 , creation_date
293 , last_updated_by
294 , last_update_date)
295 VALUES (p_id
296 , p_user_id
297 , p_login_id
298 , p_run_date
299 , p_user_id
300 , p_run_date ) ;
301
302 EXCEPTION
303 WHEN DUP_VAL_ON_INDEX THEN
304 -- ignore the error if an attempt is made to insert an existing record
305 NULL ;
306 WHEN OTHERS THEN
307 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
308 fnd_message.set_name( application => 'FND'
309 , name => 'CRM-DEBUG ERROR' ) ;
310 fnd_message.set_token( token => 'ROUTINE'
311 , value => 'OKI_DBI_MV_UTIL_PVT.crt_mx ' ) ;
312 bis_collection_utilities.put_line(fnd_message.get) ;
313 bis_collection_utilities.put_line( ' p_id : ' || p_id ) ;
314 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
315 END crt_mx ;
316
317 END oki_dbi_mv_util_pvt ;