1 package body EDR_INDEXED_XML_UTIL as
2 /* $Header: EDRGIXEB.pls 120.0.12000000.1 2007/01/18 05:53:31 appldev ship $ */
3
4 -- Bug 3196897: new site-level no-user-access profile name
5 G_PROFILE_SYNC_TIME CONSTANT varchar2(100) := 'EDR_INDEX_SYNC_TIME';
6
7 procedure CREATE_INDEX(ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY VARCHAR2)
8 IS
9
10 cursor c1 is
11 select index_section_name, index_tag, status
12 from edr_idx_xml_element_vl;
13
14 l_section_name varchar2(30);
15 l_tag varchar2(64);
16 l_status char(1);
17 l_str varchar2(1000);
18 l_time_str varchar2(30);
19
20 no_ctx_object EXCEPTION;
21 PRAGMA EXCEPTION_INIT(no_ctx_object, -20000);
22
23 no_index EXCEPTION;
24 PRAGMA EXCEPTION_INIT(no_index, -01418);
25
26 begin
27 begin
28 l_str:= 'drop index edr_psig_textindex';
29 execute immediate l_str;
30 fnd_file.put_line(FND_FILE.LOG,'Index dropped for refresh');
31 exception
32 when no_index then
33 fnd_file.put_line(FND_FILE.LOG,'Index does not exist. Creating index for the first time.');
34 end;
35
36 begin
37 Ctx_Ddl.Drop_Section_Group
38 ( group_name => 'edr_section_group'
39 );
40 fnd_file.put_line(FND_FILE.LOG,'Section group dropped for refresh');
41 exception
42 when no_ctx_object then
43 fnd_file.put_line(FND_FILE.LOG,'Section group does not exist');
44 end;
45
46 begin
47 ctx_ddl.drop_preference('edrlex');
48 fnd_file.put_line(FND_FILE.LOG,'Lexer preference dropped');
49 exception
50 when no_ctx_object then
51 fnd_file.put_line(FND_FILE.LOG,'Lexer preference does not exist.');
52 end;
53
54 Ctx_Ddl.Create_Section_Group
55 (group_name => 'edr_section_group',
56 group_type => 'xml_section_group');
57 fnd_file.put_line(FND_FILE.LOG,'Section group created ');
58
59 ctx_ddl.create_preference('edrlex','BASIC_LEXER');
60
61 --Bug 2783886: Start
62 --added .?_! as printjoin attributes
63 ctx_ddl.set_attribute('edrlex','printjoins','{},&?\()[]-;~|$!>*=.?_!');
64 --ctx_ddl.set_attribute('edrlex','printjoins','{},&?\()[]-;~|$!>*=');
65
66 --add the prefix index attribute to the basic wordlist so that
67 --performance of the right wild card % queries improves
68 --FYI we are now putting a wild card at the end of ALL queries
69
70 begin
71 ctx_ddl.drop_preference('edrwordlist');
72 fnd_file.put_line(FND_FILE.LOG,'Wordlist preference dropped');
73 exception
74 when no_ctx_object then
75 fnd_file.put_line(FND_FILE.LOG,'Wordlist preference does not exist.');
76 end;
77
78 ctx_ddl.create_preference('edrwordlist', 'BASIC_WORDLIST');
79 ctx_ddl.set_attribute('edrwordlist','PREFIX_INDEX','YES');
80 ctx_ddl.set_attribute('edrwordlist','PREFIX_MIN_LENGTH',1);
81 ctx_ddl.set_attribute('edrwordlist','PREFIX_MAX_LENGTH', 64);
82
83 --Bug 2783886: End
84
85 fnd_file.put_line(FND_FILE.LOG,'Lexer preference created');
86 open c1;
87 loop
88 fetch c1 into l_section_name, l_tag, l_status;
89 exit when c1%notfound;
90 if (l_status = 'N') then
91 update edr_idx_xml_element_b set status = 'I' where index_tag = l_tag;
92 fnd_file.put_line(FND_FILE.LOG,'Activated section: '||l_section_name);
93 end if;
94 Ctx_Ddl.Add_zone_Section
95 (group_name =>'edr_section_group',
96 section_name =>l_section_name,
97 tag =>l_tag
98 );
99 fnd_file.put_line(FND_FILE.LOG,'Created section: '||l_section_name);
100 end loop;
101 close c1;
102
103 fnd_file.put_line(FND_FILE.LOG,'Sections created for indexed xml elements');
104
105 l_str:= 'create index edr_psig_textindex on edr_psig_documents(psig_xml) '||
106 'indextype is ctxsys.context parameters '||
107 '(''LEXER edrlex FILTER ctxsys.null_filter SECTION GROUP edr_section_group'')';
108
109 execute immediate l_str;
110 fnd_file.put_line(FND_FILE.LOG,'Index created successfully');
111
112 commit;
113
114 -- Bug 3196897: start:
115 l_time_str := fnd_date.Date_To_DisplayDT(sysdate, fnd_timezones.Get_Server_Timezone_Code);
116 fnd_message.set_name( 'EDR', 'EDR_PLS_IXE_BUILD_END' );
117 fnd_message.set_token( 'FINISH_TIME', l_time_str );
118 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
119
120 -- note: fnd_profile.defined( NAME ) return false if profile exist with no value
121 -- note: if profile not exist, fnd_profile.Save() will fail and return false
122 IF fnd_profile.Save( G_PROFILE_SYNC_TIME, l_time_str, 'SITE' ) THEN
123 commit; -- need commit for the profile to take immediate effect
124 ELSE
125 fnd_message.set_name( 'EDR', 'EDR_GENERAL_UPDATE_FAIL' );
126 fnd_message.set_token( 'OBJECT_NAME', G_PROFILE_SYNC_TIME );
127 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
128 END IF;
129 -- Bug 3196897: end:
130
131 exception
132 when OTHERS then
133 rollback;
134 fnd_file.put_line(FND_FILE.LOG,'An error occured with the following error message. '||
135 'Please rerun the job after correcting the cause of error');
136 fnd_file.put_line(FND_FILE.LOG,SQLERRM(SQLCODE));
137
138 END CREATE_INDEX;
139
140
141 -- 2979172 start: need new procedures to sync/optimize index
142 -- Procedure: Synchronize_Index
143 -- In Param :
144 -- Function : use ctx_ddl.sync_index to do the work scheduled by concurrent manager
145
146 PROCEDURE Synchronize_Index (
147 ERRBUF OUT NOCOPY VARCHAR2,
148 RETCODE OUT NOCOPY NUMBER )
149 IS
150 l_syn_id NUMBER;
151 l_time_str varchar2(30);
152
153 BEGIN
154
155 fnd_message.set_name('EDR', 'EDR_PLS_IXE_SYNC_START');
156 fnd_message.set_token('XML_INDEX', 'EDR_PSIG_TEXTINDEX');
157 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
158
159 ctx_ddl.sync_index ( 'EDR_PSIG_TEXTINDEX' );
160
161 -- Bug 3196897: start:
162 l_time_str := fnd_date.Date_To_DisplayDT(sysdate, fnd_timezones.Get_Server_Timezone_Code);
163 fnd_message.set_name( 'EDR', 'EDR_PLS_IXE_SYNC_END' );
164 fnd_message.set_token( 'FINISH_TIME', l_time_str );
165 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
166
167 -- note: fnd_profile.defined( NAME ) return false if profile exist with no value
168 -- note: if profile not exist, fnd_profile.Save() will fail and return false
169 IF fnd_profile.Save( G_PROFILE_SYNC_TIME, l_time_str, 'SITE' ) THEN
170 commit; -- need commit to take immediate effect
171 ELSE
172 fnd_message.set_name( 'EDR', 'EDR_GENERAL_UPDATE_FAIL' );
173 fnd_message.set_token( 'OBJECT_NAME', G_PROFILE_SYNC_TIME );
174 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
175 END IF;
176 -- Bug 3196897: end:
177
178 EXCEPTION
179 when others then
180 errbuf := substr(sqlerrm, 1, 240);
181 retcode := 2;
182 END Synchronize_Index;
183
184
185 -- Procedure: Optimize_Index the Concurrent Program defines/uses value set for p_optimize_level
186 -- In Param : p_optimize_level 'FAST' for defragmentation only
187 -- 'FULL' does both defragmentation and garbage collection
188 -- p_duration number of minutes for the duration of running one optimization
189 -- next time the optimization will continue what's left last time
190 -- Function : use ctx_ddl.sync_index to do the work scheduled by concurrent manager
191
192 PROCEDURE Optimize_Index (
193 ERRBUF OUT NOCOPY VARCHAR2,
194 RETCODE OUT NOCOPY NUMBER,
195 p_optimize_level IN VARCHAR2,
196 p_duration IN NUMBER )
197 IS
198 l_opt_level VARCHAR2(20);
199 l_maxtime NUMBER;
200
201 BEGIN
202 -- check the valid parameter for optimization level
203 IF p_optimize_level is null THEN
204 l_opt_level := ctx_ddl.optlevel_full;
205 ELSIF upper(p_optimize_level) not in ('FAST','FULL') then
206 fnd_message.set_name( 'EDR', 'EDR_PLS_PARAM_INVALID' );
207 fnd_message.set_token( 'PLSPROC', 'EDR_INDEXED_XML_UTIL.Optimize_Index' );
208 fnd_message.set_token( 'PARAM', 'p_optimize_level' );
209 fnd_message.set_token( 'VALUE', p_optimize_level );
210 raise_application_error(-20000, fnd_message.get);
211 ELSE l_opt_level := p_optimize_level;
212 END IF;
213 fnd_message.set_name('EDR', 'EDR_PLS_IXE_OPTIM_LEVEL');
214 fnd_message.set_token('XML_INDEX', 'EDR_PSIG_TEXTINDEX');
215 fnd_message.set_token('IXE_OPT_LEVEL', l_opt_level );
216 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
217
218 -- check the valid parameter for the time spent in each running of optimization
219 IF p_duration is null THEN
220 l_maxtime := ctx_ddl.maxtime_unlimited;
221 ELSE
222 IF trunc(p_duration) <> p_duration or p_duration < 0
223 or p_duration > ctx_ddl.maxtime_unlimited THEN
224 fnd_message.set_name( 'EDR', 'EDR_PLS_PARAM_INVALID' );
225 fnd_message.set_token( 'PLSPROC', 'EDR_INDEXED_XML_UTIL.Optimize_Index' );
226 fnd_message.set_token( 'PARAM', 'p_duration' );
227 fnd_message.set_token( 'VALUE', p_duration );
228 Raise_application_error(-20000, fnd_message.get);
229 END IF;
230 END IF;
231
232 IF p_optimize_level = 'FAST' THEN
233 l_maxtime := null;
234 ELSE
235 IF p_duration IS NULL THEN
236 l_maxtime := ctx_ddl.maxtime_unlimited;
237 ELSE
238 l_maxtime := trunc( p_duration );
239 END IF;
240 END IF;
241 IF l_maxtime is not null THEN
242 fnd_message.set_name('EDR', 'EDR_PLS_IXE_OPTIM_TIME');
243 fnd_message.set_token('XML_INDEX', 'EDR_PSIG_TEXTINDEX');
244 fnd_message.set_token('IXE_OPT_TIME', l_maxtime );
245 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
246 END IF;
247
248 ctx_ddl.optimize_index( 'EDR_PSIG_TEXTINDEX', l_opt_level, l_maxtime );
249 fnd_message.set_name('EDR', 'EDR_PLS_IXE_OPTIM_END');
250 fnd_message.set_token('XML_INDEX', 'EDR_PSIG_TEXTINDEX');
251 fnd_file.put_line( FND_FILE.LOG, fnd_message.get );
252
253 EXCEPTION
254 when others then
255 errbuf := substr(sqlerrm, 1, 240);
256 retcode := 2;
257 END Optimize_Index;
258 -- 2979172 end: these procedures are used in concurrent program
259
260
261 FUNCTION GET_WF_PARAMS(p_param_name IN varchar2, p_event_guid IN RAW) return varchar2 as
262 l_str varchar2(4000);
263 l_param_value varchar2(320);
264 BEGIN
265 l_param_value := wf_event_functions_pkg.SUBSCRIPTIONPARAMETERS(l_str,p_param_name,p_event_guid);
266 return l_param_value;
267 END GET_WF_PARAMS;
268
269 end EDR_INDEXED_XML_UTIL;
[Home] [Help]