1 package body ad_mv as
2 /* $Header: admvb.pls 120.14 2011/09/22 19:12:51 mkumandu ship $*/
3
4 gs_package_name_c CONSTANT VARCHAR2(30) := 'ad_mv';
5
6 ga_stmt dbms_sql.varchar2s;
7
8 ga_c_stmt clob;
9
10 check_tspace_exist varchar2(100);
11
12 mv_exists EXCEPTION;
13 PRAGMA EXCEPTION_INIT(mv_exists, -12006);
14
15 -- Bug 3562360: sallamse
16 -- Removed the set_sess_parm_if_required procedure
17 -- global constants for package body
18 -- everything between 8.1.7 and 9.0.0
19 gn_limit_min_c CONSTANT PLS_INTEGER := 817;
20 gn_limit_max_c CONSTANT PLS_INTEGER := 900;
21
22 ls_version VARCHAR2(20);
23 ls_compatibility VARCHAR2(20);
24
25
26 --
27 -- Split global statement
28 --
29 PROCEDURE split_stmt(as_stmt_i VARCHAR2)
30 IS
31 l_chunk VARCHAR2(256);
32 l_index INTEGER := 1;
33 l_pointer INTEGER;
34 BEGIN
35 l_pointer := NVL(ga_stmt.COUNT + 1, 1);
36 LOOP
37 l_chunk := SUBSTR(as_stmt_i, l_index, 256);
38 EXIT WHEN l_chunk IS NULL;
39 ga_stmt(l_pointer) := l_chunk;
40 l_pointer := l_pointer + 1;
41 l_index := l_index + 256;
42 END LOOP;
43 END;
44
45
46 --
47 -- This procedure will return 'Y' if db prep scripts run
48 -- Else it will return 'N'
49 --
50 FUNCTION is_edition_enabled
51 return varchar2
52 IS
53 l_enabled varchar2(1);
54 l_appsuser varchar2(30);
55 BEGIN
56
57 -- Get APPS Username
58 SELECT oracle_username
59 INTO l_appsuser
60 FROM fnd_oracle_userid
61 WHERE read_only_flag='U';
62
63 -- Check the editions enabled for APPS schema
64 SELECT editions_enabled
65 INTO l_enabled
66 FROM dba_users
67 WHERE username=l_appsuser;
68
69 return l_enabled;
70 END;
71
72
73 --
74 -- Execute the given statement
75 --
76 PROCEDURE execute_stmt(as_stmt_i clob, ab_long_stmt_i boolean)
77 is
78 ln_cursor INTEGER;
79 ln_dummy INTEGER;
80 begin
81 IF (ab_long_stmt_i)
82 THEN
83 ln_cursor := dbms_sql.open_cursor;
84 dbms_sql.parse(c => ln_cursor, statement => ga_stmt
85 , lb => 1, ub => ga_stmt.COUNT
86 , lfflg => FALSE, language_flag => dbms_sql.native
87 );
88 ln_dummy := dbms_sql.execute(ln_cursor);
89 dbms_sql.close_cursor(ln_cursor);
90 ga_stmt.DELETE;
91 ELSE
92 EXECUTE IMMEDIATE as_stmt_i;
93 END IF;
94 end;
95
96 --
97 -- This procedure should not be called directly from an
98 -- external program when the statement is really long
99 -- and dbms_sql.parse() is expected to be used, i.e.
100 -- ab_long_stmt_i=TRUE. In these cases, the call should
101 -- be made to do_mv_ddl().
102 --
103 PROCEDURE create_mv (
104 as_mview_name_i VARCHAR2
105 , as_stmt_i VARCHAR2
106 , ab_long_stmt_i BOOLEAN DEFAULT FALSE
107 )
108 IS
109 ls_name_c CONSTANT VARCHAR2(30) := gs_package_name_c || '.' || 'create_mv';
110 ls_dummy VARCHAR2(1);
111 ln_resource_status INTEGER;
112 l_db_version VARCHAR2(30);
113 l_statement VARCHAR2(500);
114 ln_cursor INTEGER;
115 ln_dummy INTEGER;
116 BEGIN
117
118 g_edition_enabled := is_edition_enabled;
119 -- Is the mview 'partially' created? If so, it has to be dropped
120 -- IF (as_mview_name_i IS NOT NULL)
121 -- THEN
122 BEGIN
123 SELECT 'x'
124 INTO ls_dummy
125 FROM user_tables
126 , user_mviews
127 WHERE table_name = mview_name
128 AND mview_name = create_mv.as_mview_name_i
129 AND NOT EXISTS (
130 SELECT null
131 FROM user_objects
132 WHERE object_type = 'MATERIALIZED VIEW'
133 AND object_name = table_name
134 AND object_name = create_mv.as_mview_name_i
135 )
136 ;
137 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || as_mview_name_i;
138 EXCEPTION
139 WHEN NO_DATA_FOUND
140 THEN null;
141 END;
142 -- END IF;
143
144 IF (g_edition_enabled = 'N')
145 THEN
146 BEGIN
147
148 BEGIN
149 l_statement := 'select version from v$instance';
150 execute immediate l_statement into l_db_version;
151 EXCEPTION
152 when others then
153 raise_application_error(-20000,
154 'Unable to get the DB Version.');
155 END;
156
157 -- Bug 3562360: sallamse
158 -- Moved setting "_mv_refresh_selections" session parameter
159 -- to init section of the package body
160 execute_stmt(as_stmt_i, ab_long_stmt_i);
161 EXCEPTION
162 WHEN mv_exists
163 THEN null;
164 WHEN OTHERS
165 THEN
166 ga_stmt.DELETE;
167 RAISE;
168 END;
169 ELSE
170 BEGIN
171 -- Install MV and MVQ
172 IF (ab_long_stmt_i)
173 THEN
174 ad_zd_mview.INSTALL_MVQ_ARCH(ga_c_stmt);
175 ELSE
176 ad_zd_mview.INSTALL_MVQ_ARCH(as_stmt_i);
177 END IF;
178
179 ga_c_stmt := '';
180 EXCEPTION
181 WHEN OTHERS THEN
182 ga_c_stmt := '';
183 raise;
184 END;
185 END IF;
186
187
188 END create_mv;
189
190 -- Bug 3562360: sallamse
191 -- Created wraper procedure as Java JDBC driver does not support
192 -- passing boolean parameters in PL/SQL Stored Procedures.
193 --
194 -- This procedure should not be called directly from an
195 -- external program when the statement is really long
196 -- and dbms_sql.parse() is expected to be used, i.e.
197 -- ab_long_stmt_i=1. In these cases, the call should
198 -- be made to do_mv_ddl2().
199 --
200 PROCEDURE create_mv2 (
201 as_mview_name_i VARCHAR2
202 , as_stmt_i VARCHAR2
203 , ab_long_stmt_i INTEGER DEFAULT 0
204 )
205 IS
206 BEGIN
207 IF (ab_long_stmt_i=1) THEN
208 create_mv(as_mview_name_i, as_stmt_i, TRUE);
209 ELSE
210 create_mv(as_mview_name_i, as_stmt_i, FALSE);
211 END IF;
212 END create_mv2;
213
214 --
215 -- This procedure should not be called directly from an
216 -- external program when the statement is really long
217 -- and dbms_sql.parse() is expected to be used, i.e.
218 -- ab_long_stmt_i=TRUE. In these cases, the call should
219 -- be made to do_mv_ddl().
220 --
221 PROCEDURE mv_ddl (
222 as_mview_name_i VARCHAR2
223 , as_stmt_i VARCHAR2
224 , ab_long_stmt_i BOOLEAN DEFAULT FALSE
225 )
226 IS
227 ls_name_c CONSTANT VARCHAR2(30) := gs_package_name_c || '.' || 'mv_ddl';
231 ln_dummy INTEGER;
228 ls_dummy VARCHAR2(1);
229 ln_resource_status INTEGER;
230 ln_cursor INTEGER;
232 BEGIN
233
234 BEGIN
235 IF (ab_long_stmt_i)
236 THEN
237 ln_cursor := dbms_sql.open_cursor;
238 dbms_sql.parse(c => ln_cursor, statement => ga_stmt
239 , lb => 1, ub => ga_stmt.COUNT
240 , lfflg => FALSE, language_flag => dbms_sql.native
241 );
242 ln_dummy := dbms_sql.execute(ln_cursor);
243 dbms_sql.close_cursor(ln_cursor);
244 ga_stmt.DELETE;
245 ELSE
246 EXECUTE IMMEDIATE as_stmt_i;
247 END IF;
248 EXCEPTION
249 WHEN OTHERS
250 THEN
251 ga_stmt.DELETE;
252 RAISE;
253 END;
254
255 END mv_ddl;
256
257 PROCEDURE alter_mv (
258 as_mview_name_i VARCHAR2
259 , as_stmt_i VARCHAR2
260 )
261 IS
262 BEGIN
263 g_edition_enabled := is_edition_enabled;
264 mv_ddl(as_mview_name_i, as_stmt_i);
265 END alter_mv;
266
267 PROCEDURE drop_mv (
268 as_mview_name_i VARCHAR2
269 , as_stmt_i VARCHAR2
270 )
271 IS
272 e_mv_nofound exception;
273 PRAGMA EXCEPTION_INIT(e_mv_nofound, -12003);
274 BEGIN
275 g_edition_enabled := is_edition_enabled;
276 begin
277 mv_ddl(as_mview_name_i, as_stmt_i);
278 exception when e_mv_nofound
279 then
280 if g_edition_enabled = 'Y'
281 then
282 ad_zd_mview.drop_mvq(p_mvname=>as_mview_name_i);
283 end if;
284 raise;
285 end;
286
287 if g_edition_enabled = 'Y'
288 then
289 ad_zd_mview.drop_mvq(p_mvname=>as_mview_name_i);
290 end if;
291 END drop_mv;
292
293 --
294 -- This procedure allows really long statements to be
295 -- executed by preparing ga_stmt before dbms_sql.parse()
296 -- is called in other procedures.
297 --
298 -- If the calling program needs to call this procedure
299 -- several times to pass a single statement, make sure
300 -- that ab_execute_i is set to FALSE for all calls except
301 -- for the last one where it should be set to TRUE.
302 --
303 PROCEDURE do_mv_ddl (
304 an_operation_i PLS_INTEGER
305 , as_mview_name_i VARCHAR2
306 , as_stmt_i VARCHAR2
307 , ab_execute_i BOOLEAN DEFAULT NULL
308 )
309 IS
310 BEGIN
311
312 g_edition_enabled := is_edition_enabled;
313 IF (an_operation_i NOT IN (ad_mv.mv_create, ad_mv.mv_alter, ad_mv.mv_drop))
314 THEN
315 RAISE_APPLICATION_ERROR(-20003, 'do_mv_ddl: unsupported operation');
316 END IF;
317
318 -- Split only if db is prepared.
319 IF (g_edition_enabled = 'N')
320 THEN
321 split_stmt(as_stmt_i);
322 ELSE
323 ga_c_stmt := ga_c_stmt||as_stmt_i;
324 END IF;
325
326 IF (ab_execute_i IS NULL OR ab_execute_i)
327 THEN
328 IF (an_operation_i = ad_mv.mv_create)
329 THEN
330 create_mv(as_mview_name_i, as_stmt_i, ab_execute_i);
331 ELSIF (an_operation_i = ad_mv.mv_drop)
332 then
333 declare
334 e_mv_nofound exception;
335 PRAGMA EXCEPTION_INIT(e_mv_nofound, -12003);
336 BEGIN
337 g_edition_enabled := is_edition_enabled;
338 begin
339 mv_ddl(as_mview_name_i, as_stmt_i);
340 exception when e_mv_nofound
341 then
342 if g_edition_enabled = 'Y'
343 then
344 ad_zd_mview.drop_mvq(p_mvname=>as_mview_name_i);
345 end if;
346 raise;
347 end;
348 if g_edition_enabled = 'Y'
349 then
350 ad_zd_mview.drop_mvq(p_mvname=>as_mview_name_i);
351 end if;
352 end;
353 ELSIF (an_operation_i = ad_mv.mv_alter)
354 THEN
355 mv_ddl(as_mview_name_i, as_stmt_i, ab_execute_i);
356 END IF;
357 END IF;
358 END do_mv_ddl;
359
360 -- Bug 3562360: sallamse
361 -- Created wraper procedure as Java JDBC driver does not support
362 -- passing boolean parameters in PL/SQL Stored Procedures.
363 --
364 -- If the calling program needs to call this procedure
365 -- several times to pass a single statement, make sure
366 -- that ab_execute_i is set to 0 for all calls except
367 -- for the last one where it should be set to 1.
368 --
369 PROCEDURE do_mv_ddl2 (
370 an_operation_i PLS_INTEGER
371 , as_mview_name_i VARCHAR2
372 , as_stmt_i VARCHAR2
376 BEGIN
373 , ab_execute_i INTEGER DEFAULT NULL
374 )
375 IS
377 IF (ab_execute_i IS NULL)
378 THEN
379 do_mv_ddl(an_operation_i, as_mview_name_i, as_stmt_i);
380 ELSIF (ab_execute_i=1) THEN
381 do_mv_ddl(an_operation_i, as_mview_name_i, as_stmt_i, TRUE);
382 ELSE
383 do_mv_ddl(an_operation_i, as_mview_name_i, as_stmt_i, FALSE);
384 END IF;
385 END do_mv_ddl2;
386
387 BEGIN
388 dbms_utility.db_version(ls_version, ls_compatibility);
389 IF (TO_NUMBER(TRANSLATE(SUBSTR(ls_version, 1, 5), '@.', '@')) BETWEEN
390 gn_limit_min_c AND gn_limit_max_c)
391 THEN
392 EXECUTE IMMEDIATE 'ALTER SESSION SET "_mv_refresh_selections"=TRUE';
393 END IF;
394 ad_tspace_util.get_tablespace_name('BIS','SUMMARY','Y',check_tspace_exist,g_mv_data_tablespace);
395 g_mv_index_tablespace := g_mv_data_tablespace;
396 END ad_mv;
[Home] [Help]