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