[Home] [Help]
PACKAGE BODY: APPS.POA_MV_UTILS_PKG
Source
1 package body POA_MV_UTILS_PKG as
2 /* $Header: POAMVUTLB.pls 120.0 2005/06/01 14:19:22 appldev noship $ */
3
4 procedure drop_MV_Log(p_mv_log varchar2)
5 is
6 begin
7 execute immediate 'drop materialized view log on '||p_mv_log;
8 exception
9 when others then null;
10 end drop_MV_Log;
11
12 procedure create_MV_Log(p_base_table varchar2
13 ,p_column_list varchar2 := NULL
14 ,p_sequence_flag varchar2 := 'Y'
15 ,p_rowid varchar2 := 'Y'
16 ,p_new_values varchar2 := 'Y'
17 ,p_data_tablespace varchar2 := null
18 ,p_index_tablespace varchar2 := null
19 ,p_next_extent varchar2 := '32K'
20 )
21 is
22 MV_LOG_EXISTS exception;
23 pragma exception_init(MV_LOG_EXISTS, -12000);
24 l_sequence varchar2(20) := 'SEQUENCE';
25 l_rowid varchar2(20) := 'ROWID';
26 l_data_tablespace varchar2(30) := p_data_tablespace;
27 l_index_tablespace varchar2(30) := p_index_tablespace;
28 l_column_list varchar2(3000);
29 begin
30 if(p_sequence_flag = 'N') then l_sequence := ''; end if;
31 if(p_rowid = 'N') then
32 l_rowid := '';
33 elsif(p_sequence_flag = 'Y') then
34 l_sequence := 'SEQUENCE,';
35 end if;
36 if(p_column_list is not null) then l_column_list := '('||p_column_list||')'; end if;
37
38 if p_data_tablespace is null then
39 l_data_tablespace := ad_mv.g_mv_data_tablespace;
40 l_index_tablespace := ad_mv.g_mv_index_tablespace;
41 end if;
42
43 EXECUTE IMMEDIATE
44 ' create materialized view log on '||p_base_table||
45 ' tablespace '||l_data_tablespace||
46 ' INITRANS 4 MAXTRANS 255 '||
47 ' storage(INITIAL 4K NEXT '||p_next_extent||
48 ' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)'||
49 ' with '||l_sequence||l_rowid||l_column_list||
50 ' including new values';
51 exception
52 when MV_LOG_EXISTS then null;
53 end create_MV_Log;
54
55 procedure drop_MV(p_mv varchar2)
56 is
57 MV_NOTEXISTS exception;
58 pragma exception_init(MV_NOTEXISTS, -12003);
59 begin
60 execute immediate 'drop materialized view '||p_mv;
61 exception
62 when MV_NOTEXISTS then null;
63 end drop_MV;
64
65 procedure create_MV(p_mv_name varchar2
66 ,p_mv_sql varchar2
67 ,p_build_mode varchar2 := 'D' -- DEFERRED
68 ,p_refresh_mode varchar2 := 'F' -- FAST
69 ,p_enable_qrewrite varchar2 := 'N'
70 ,p_partition_flag varchar2 := 'N'
71 ,p_next_extent varchar2 := '2M'
72 )
73 is
74 MV_EXISTS exception;
75 pragma exception_init(MV_EXISTS, -12006);
76 l_data_tablespace varchar2(30);
77 l_index_tablespace varchar2(30);
78 l_build_mode varchar2(20) := 'DEFERRED';
79 l_refresh_mode varchar2(20) := 'FAST';
80 l_enable_qrewrite varchar2(30) := 'DISABLE';
81 l_storage varchar2(256);
82 l_partition_clause varchar2(2000);
83 l_query varchar2(32767);
84 begin
85 if p_build_mode = 'I' then
86 l_build_mode := 'IMMEDIATE';
87 end if;
88
89 if p_refresh_mode = 'C' then
90 l_refresh_mode := 'COMPLETE';
91 end if;
92
93 if p_enable_qrewrite = 'Y' then
94 l_enable_qrewrite := 'ENABLE';
95 end if;
96
97 l_storage := 'storage(INITIAL 4K NEXT '||p_next_extent||' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)';
98
99 if(p_partition_flag = 'Y') then
100 l_partition_clause := ' PARTITION by LIST (grp_id)
101 (PARTITION quarter VALUES (7) PCTFREE 10 PCTUSED 80 '||l_storage||',
102 PARTITION month VALUES (11) PCTFREE 10 PCTUSED 80 '||l_storage||',
103 PARTITION week VALUES (13) PCTFREE 10 PCTUSED 80 '||l_storage||',
104 PARTITION day VALUES (14) PCTFREE 10 PCTUSED 80 '||l_storage||'
105 ) ';
106 end if;
107
108 l_data_tablespace := ad_mv.g_mv_data_tablespace;
109 l_index_tablespace := ad_mv.g_mv_index_tablespace;
110
111 /*
112 l_query := 'create materialized view '||p_mv_name|| '
113 TABLESPACE '||l_data_tablespace||'
114 INITRANS 4 MAXTRANS 255
115 ' || l_storage|| '
116 ' || l_partition_clause || '
117 BUILD '||l_build_mode|| '
118 USING index tablespace '|| l_index_tablespace || '
119 STORAGE (INITIAL 4K NEXT '|| p_next_extent || '
120 MAXEXTENTS UNLIMITED PCTINCREASE 0)
121 REFRESH ' || l_refresh_mode || ' ON DEMAND
122 ' || l_enable_qrewrite || ' QUERY REWRITE '|| '
123 as
124 ' || p_mv_sql;
125 */
126
127 l_query :=
128 ' create materialized view '||p_mv_name||
129 ' tablespace '||l_data_tablespace||
130 ' INITRANS 4 MAXTRANS 255 '||
131 l_storage||l_partition_clause||
132 ' build '||l_build_mode||
133 ' using index tablespace '||l_index_tablespace||
134 ' storage (INITIAL 4K NEXT '||p_next_extent||
135 ' MAXEXTENTS UNLIMITED PCTINCREASE 0) '||
136 ' refresh '||l_refresh_mode ||' ON DEMAND '||
137 -- ' with <rowid|primary key> '||
138 l_enable_qrewrite||' QUERY REWRITE '||
139 ' as '||
140 p_mv_sql;
141
142 ad_mv.create_mv(p_mv_name, l_query);
143
144 exception
145 when MV_EXISTS then null;
146 end create_MV;
147
148
149 procedure create_part_MV(p_mv_name varchar2
150 ,p_mv_sql varchar2
151 ,p_build_mode varchar2 := 'D' -- DEFERRED
152 ,p_refresh_mode varchar2 := 'F' -- FAST
153 ,p_enable_qrewrite varchar2 := 'N'
154 ,p_partition_clause varchar2 := NULL
155 ,p_next_extent varchar2 := '2M'
156 )
157 is
158 MV_EXISTS exception;
159 pragma exception_init(MV_EXISTS, -12006);
160 l_data_tablespace varchar2(30);
161 l_index_tablespace varchar2(30);
162 l_build_mode varchar2(20) := 'DEFERRED';
163 l_refresh_mode varchar2(20) := 'FAST';
164 l_enable_qrewrite varchar2(30) := 'DISABLE';
165 l_storage varchar2(256);
166 l_query varchar2(32767);
167 begin
168 if p_build_mode = 'I' then
169 l_build_mode := 'IMMEDIATE';
170 end if;
171
172 if p_refresh_mode = 'C' then
173 l_refresh_mode := 'COMPLETE';
174 end if;
175
176 if p_enable_qrewrite = 'Y' then
177 l_enable_qrewrite := 'ENABLE';
178 end if;
179
180 l_storage := 'storage(INITIAL 4K NEXT '||p_next_extent||' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)';
181
182 l_data_tablespace := ad_mv.g_mv_data_tablespace;
183 l_index_tablespace := ad_mv.g_mv_index_tablespace;
184
185
186 l_query :=
187 ' create materialized view '||p_mv_name||
188 ' tablespace '||l_data_tablespace||
189 ' INITRANS 4 MAXTRANS 255 '||
190 l_storage||p_partition_clause||
191 ' build '||l_build_mode||
192 ' using index tablespace '||l_index_tablespace||
193 ' storage (INITIAL 4K NEXT '||p_next_extent||
194 ' MAXEXTENTS UNLIMITED PCTINCREASE 0) '||
195 ' refresh '||l_refresh_mode ||' ON DEMAND '||
196 -- ' with <rowid|primary key> '||
197 l_enable_qrewrite||' QUERY REWRITE '||
198 ' as '||
199 p_mv_sql;
200
201 ad_mv.create_mv(p_mv_name, l_query);
202
203 exception
204 when MV_EXISTS then null;
205 end create_part_MV;
206
207 procedure create_MV_Index(p_mv_name varchar2
208 ,p_ind_name varchar2
209 ,p_ind_col_list varchar2
210 ,p_unique_flag varchar2 := 'N'
211 ,p_ind_type varchar2 := 'B' -- B = BTree , M = BitMap
212 ,p_next_extent varchar2 := '32K'
213 ,p_partition_type varchar2 := 'L' -- L = Local
214 )
215 is
216 l_data_tablespace varchar2(30);
217 l_index_tablespace varchar2(30);
218 l_unique varchar2(10);
219 l_index_type varchar2(10);
220 l_partition_clause varchar2(100);
221 MV_INDEX_EXISTS exception;
222 pragma exception_init(MV_INDEX_EXISTS, -955);
223 begin
224 if(p_unique_flag = 'Y') then l_unique := 'UNIQUE '; end if;
225 if(p_ind_type = 'M') then l_index_type := 'BITMAP'; end if;
226
227 if(p_partition_type = 'L') then
228 l_partition_clause := ' LOCAL ';
229 elsif(p_partition_type = 'G') then
230 l_partition_clause := ' GLOBAL ';
231 end if;
232
233 l_data_tablespace := ad_mv.g_mv_data_tablespace;
234 l_index_tablespace := ad_mv.g_mv_index_tablespace;
235
236 execute immediate
237 ' create '||l_unique||l_index_type||' index '||p_ind_name||' ON '||p_mv_name ||'('||p_ind_col_list||') '||
238 ' tablespace '||l_index_tablespace||l_partition_clause||
239 ' INITRANS 4 MAXTRANS 255'||
240 ' storage(INITIAL 4K NEXT '||p_next_extent||
241 ' MAXEXTENTS UNLIMITED PCTINCREASE 0)';
242 exception
243 when MV_INDEX_EXISTS then null;
244 end create_MV_Index;
245
246
247 end POA_MV_UTILS_PKG;