1 package body ad_mv as
2 /* $Header: admvb.pls 120.4 2006/06/01 19:18:16 vlim noship $*/
3
4 gs_package_name_c CONSTANT VARCHAR2(30) := 'ad_mv';
5
6 gn_timeout_c CONSTANT INTEGER := dbms_lock.maxwait;
7 gs_lockhandle_name_c CONSTANT VARCHAR2(12) := 'AD_MV_CREATE';
8
9 ga_stmt dbms_sql.varchar2s;
10
11 check_tspace_exist varchar2(100);
12
13 mv_exists EXCEPTION;
14 PRAGMA EXCEPTION_INIT(mv_exists, -12006);
15
16 -- Bug 3562360: sallamse
17 -- Removed the set_sess_parm_if_required procedure
18 -- global constants for package body
19 -- everything between 8.1.7 and 9.0.0
20 gn_limit_min_c CONSTANT PLS_INTEGER := 817;
21 gn_limit_max_c CONSTANT PLS_INTEGER := 900;
22
23 ls_version VARCHAR2(20);
24 ls_compatibility VARCHAR2(20);
25
26 --
27 -- This procedure should not be called directly from an
28 -- external program when the statement is really long
29 -- and dbms_sql.parse() is expected to be used, i.e.
30 -- ab_long_stmt_i=TRUE. In these cases, the call should
31 -- be made to do_mv_ddl().
32 --
33 PROCEDURE create_mv (
34 as_mview_name_i VARCHAR2
35 , as_stmt_i VARCHAR2
36 , ab_long_stmt_i BOOLEAN DEFAULT FALSE
37 )
38 IS
39 ls_name_c CONSTANT VARCHAR2(30) := gs_package_name_c || '.' || 'create_mv';
40 ls_dummy VARCHAR2(1);
41 ln_resource_status INTEGER;
42 ls_lockhandle VARCHAR2(128);
43 l_db_version VARCHAR2(30);
44 l_statement VARCHAR2(500);
45 ln_cursor INTEGER;
46 ln_dummy INTEGER;
47 BEGIN
48 dbms_lock.allocate_unique (
49 lockname => gs_lockhandle_name_c
50 , lockhandle => ls_lockhandle
51 );
52
53 -- requesting lock
54 ln_resource_status := dbms_lock.request (
55 lockhandle => ls_lockhandle
56 , lockmode => dbms_lock.x_mode
57 , timeout => gn_timeout_c
58 , release_on_commit => FALSE
59 );
60 -- 0 - success
61 -- 1 - timeout
62 -- 2 - deadlock
63 -- 3 - parameter error
64 -- 4 - already own lock specified by 'id' or 'lockhandle'
65 -- 5 - illegal lockhandle
66 -- Everything except 'success' is bad
67 IF (ln_resource_status <> 0)
68 THEN
69 RAISE_APPLICATION_ERROR(-20001, ls_name_c || ': could not get the lock: ' || ln_resource_status);
70 END IF;
71
72 -- Is the mview 'partially' created? If so, it has to be dropped
73 -- IF (as_mview_name_i IS NOT NULL)
74 -- THEN
75 BEGIN
76 SELECT 'x'
77 INTO ls_dummy
78 FROM user_tables
79 , user_mviews
80 WHERE table_name = mview_name
81 AND mview_name = create_mv.as_mview_name_i
82 AND NOT EXISTS (
83 SELECT null
84 FROM user_objects
85 WHERE object_type = 'MATERIALIZED VIEW'
86 AND object_name = table_name
87 AND object_name = create_mv.as_mview_name_i
88 )
89 ;
90 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || as_mview_name_i;
91 EXCEPTION
92 WHEN NO_DATA_FOUND
93 THEN null;
94 END;
95 -- END IF;
96
97 BEGIN
98
99 BEGIN
100 l_statement := 'select version from v$instance';
101 execute immediate l_statement into l_db_version;
102 EXCEPTION
103 when others then
104 raise_application_error(-20000,
105 'Unable to get the DB Version.');
106 END;
107
108 -- Bug 3562360: sallamse
109 -- Moved setting "_mv_refresh_selections" session parameter
110 -- to init section of the package body
111
112 IF (ab_long_stmt_i)
113 THEN
114 ln_cursor := dbms_sql.open_cursor;
115 dbms_sql.parse(c => ln_cursor, statement => ga_stmt
116 , lb => 1, ub => ga_stmt.COUNT
117 , lfflg => FALSE, language_flag => dbms_sql.native
118 );
119 ln_dummy := dbms_sql.execute(ln_cursor);
120 dbms_sql.close_cursor(ln_cursor);
121 ga_stmt.DELETE;
122 ELSE
123 EXECUTE IMMEDIATE as_stmt_i;
124 END IF;
125 EXCEPTION
126 WHEN mv_exists
127 THEN null;
128 WHEN OTHERS
129 THEN ln_resource_status := dbms_lock.release(lockhandle => ls_lockhandle);
130 ga_stmt.DELETE;
131 RAISE;
132 END;
133
134 ln_resource_status := dbms_lock.release(lockhandle => ls_lockhandle);
135 IF (ln_resource_status <> 0)
136 THEN
137 RAISE_APPLICATION_ERROR(-20002, ls_name_c || ': could not release the lock: ' || ln_resource_status);
138 END IF;
139 END create_mv;
140
141 -- Bug 3562360: sallamse
142 -- Created wraper procedure as Java JDBC driver does not support
143 -- passing boolean parameters in PL/SQL Stored Procedures.
144 --
145 -- This procedure should not be called directly from an
146 -- external program when the statement is really long
147 -- and dbms_sql.parse() is expected to be used, i.e.
148 -- ab_long_stmt_i=1. In these cases, the call should
149 -- be made to do_mv_ddl2().
150 --
151 PROCEDURE create_mv2 (
152 as_mview_name_i VARCHAR2
153 , as_stmt_i VARCHAR2
154 , ab_long_stmt_i INTEGER DEFAULT 0
155 )
156 IS
157 BEGIN
158 IF (ab_long_stmt_i=1) THEN
159 create_mv(as_mview_name_i, as_stmt_i, TRUE);
160 ELSE
161 create_mv(as_mview_name_i, as_stmt_i, FALSE);
162 END IF;
163 END create_mv2;
164
165 --
166 -- This procedure should not be called directly from an
167 -- external program when the statement is really long
168 -- and dbms_sql.parse() is expected to be used, i.e.
169 -- ab_long_stmt_i=TRUE. In these cases, the call should
170 -- be made to do_mv_ddl().
171 --
172 PROCEDURE mv_ddl (
173 as_mview_name_i VARCHAR2
174 , as_stmt_i VARCHAR2
175 , ab_long_stmt_i BOOLEAN DEFAULT FALSE
176 )
177 IS
178 ls_name_c CONSTANT VARCHAR2(30) := gs_package_name_c || '.' || 'mv_ddl';
179 ls_dummy VARCHAR2(1);
180 ln_resource_status INTEGER;
181 ls_lockhandle VARCHAR2(128);
182 ln_cursor INTEGER;
183 ln_dummy INTEGER;
184 BEGIN
185 dbms_lock.allocate_unique (
186 lockname => gs_lockhandle_name_c
187 , lockhandle => ls_lockhandle
188 );
189
190 -- requesting lock
191 ln_resource_status := dbms_lock.request (
192 lockhandle => ls_lockhandle
193 , lockmode => dbms_lock.x_mode
194 , timeout => gn_timeout_c
195 , release_on_commit => FALSE
196 );
197 IF (ln_resource_status <> 0)
198 THEN
199 RAISE_APPLICATION_ERROR(-20001, ls_name_c || ': could not get the lock: ' || ln_resource_status);
200 END IF;
201
202 BEGIN
203 IF (ab_long_stmt_i)
204 THEN
205 ln_cursor := dbms_sql.open_cursor;
206 dbms_sql.parse(c => ln_cursor, statement => ga_stmt
207 , lb => 1, ub => ga_stmt.COUNT
208 , lfflg => FALSE, language_flag => dbms_sql.native
209 );
210 ln_dummy := dbms_sql.execute(ln_cursor);
211 dbms_sql.close_cursor(ln_cursor);
212 ga_stmt.DELETE;
213 ELSE
214 EXECUTE IMMEDIATE as_stmt_i;
215 END IF;
216 EXCEPTION
217 WHEN OTHERS
218 THEN
219 ln_resource_status := dbms_lock.release(lockhandle => ls_lockhandle);
220 ga_stmt.DELETE;
221 RAISE;
222 END;
223
224 ln_resource_status := dbms_lock.release(lockhandle => ls_lockhandle);
225 IF (ln_resource_status <> 0)
226 THEN
227 RAISE_APPLICATION_ERROR(-20002, ls_name_c || ': could not release the lock: ' || ln_resource_status);
228 END IF;
229 END mv_ddl;
230
231 PROCEDURE alter_mv (
232 as_mview_name_i VARCHAR2
233 , as_stmt_i VARCHAR2
234 )
235 IS
236 BEGIN
237 mv_ddl(as_mview_name_i, as_stmt_i);
238 END alter_mv;
239
240 PROCEDURE drop_mv (
241 as_mview_name_i VARCHAR2
242 , as_stmt_i VARCHAR2
243 )
244 IS
245 BEGIN
246 mv_ddl(as_mview_name_i, as_stmt_i);
247 END drop_mv;
248
249 --
250 -- This procedure allows really long statements to be
251 -- executed by preparing ga_stmt before dbms_sql.parse()
252 -- is called in other procedures.
253 --
254 -- If the calling program needs to call this procedure
255 -- several times to pass a single statement, make sure
256 -- that ab_execute_i is set to FALSE for all calls except
257 -- for the last one where it should be set to TRUE.
258 --
259 PROCEDURE do_mv_ddl (
260 an_operation_i PLS_INTEGER
261 , as_mview_name_i VARCHAR2
262 , as_stmt_i VARCHAR2
263 , ab_execute_i BOOLEAN DEFAULT NULL
264 )
265 IS
266 l_chunk VARCHAR2(256);
267 l_index INTEGER := 1;
268 l_pointer INTEGER;
269 BEGIN
270 IF (an_operation_i NOT IN (ad_mv.mv_create, ad_mv.mv_alter, ad_mv.mv_drop))
271 THEN
272 RAISE_APPLICATION_ERROR(-20003, 'do_mv_ddl: unsupported operation');
273 END IF;
274 IF (ab_execute_i IS NOT NULL)
275 THEN
276 l_pointer := NVL(ga_stmt.COUNT + 1, 1);
277 LOOP
278 l_chunk := SUBSTR(as_stmt_i, l_index, 256);
279 EXIT WHEN l_chunk IS NULL;
280 ga_stmt(l_pointer) := l_chunk;
281 l_pointer := l_pointer + 1;
282 l_index := l_index + 256;
283 END LOOP;
284 END IF;
285 IF (ab_execute_i IS NULL OR ab_execute_i)
286 THEN
287 IF (an_operation_i = ad_mv.mv_create)
288 THEN
289 create_mv(as_mview_name_i, as_stmt_i, ab_execute_i);
290 ELSIF (an_operation_i IN (ad_mv.mv_alter, ad_mv.mv_drop))
291 THEN
292 mv_ddl(as_mview_name_i, as_stmt_i, ab_execute_i);
293 END IF;
294 END IF;
295 END do_mv_ddl;
296
297 -- Bug 3562360: sallamse
298 -- Created wraper procedure as Java JDBC driver does not support
299 -- passing boolean parameters in PL/SQL Stored Procedures.
300 --
301 -- If the calling program needs to call this procedure
302 -- several times to pass a single statement, make sure
303 -- that ab_execute_i is set to 0 for all calls except
304 -- for the last one where it should be set to 1.
305 --
306 PROCEDURE do_mv_ddl2 (
307 an_operation_i PLS_INTEGER
308 , as_mview_name_i VARCHAR2
309 , as_stmt_i VARCHAR2
310 , ab_execute_i INTEGER DEFAULT NULL
311 )
312 IS
313 BEGIN
314 IF (ab_execute_i IS NULL)
315 THEN
316 do_mv_ddl(an_operation_i, as_mview_name_i, as_stmt_i);
317 ELSIF (ab_execute_i=1) THEN
318 do_mv_ddl(an_operation_i, as_mview_name_i, as_stmt_i, TRUE);
319 ELSE
320 do_mv_ddl(an_operation_i, as_mview_name_i, as_stmt_i, FALSE);
321 END IF;
322 END do_mv_ddl2;
323
324 BEGIN
325 dbms_utility.db_version(ls_version, ls_compatibility);
326 IF (TO_NUMBER(TRANSLATE(SUBSTR(ls_version, 1, 5), '@.', '@')) BETWEEN
327 gn_limit_min_c AND gn_limit_max_c)
328 THEN
329 EXECUTE IMMEDIATE 'ALTER SESSION SET "_mv_refresh_selections"=TRUE';
330 END IF;
331 ad_tspace_util.get_tablespace_name('BIS','SUMMARY','Y',check_tspace_exist,g_mv_data_tablespace);
332 g_mv_index_tablespace := g_mv_data_tablespace;
333 END ad_mv;
[Home] [Help]