1 PACKAGE BODY ecx_diagnostics_test AS
2 /* $Header: ECXDIAGB.pls 120.0.12020000.4 2013/01/09 10:37:09 dhragarw noship $ */
3
4 /*
5 This procedure is being used for 'Procedure Validation Test' inside
6 'XML Gateway Health Check' diagnostic. This procedure retrieves the
7 details of those procedures/functions which are referenced in uploaded XML Gateway
8 maps but do not exist in database
9 */
10
11 procedure get_procedure_status(
12 cur out nocopy sys_refcursor)
13 is
14 v_tab_procedure_details tab_procedure_details := tab_procedure_details();
15 tmp varchar2(50);
16 tmp_schema_name varchar2(500);
17 tmp_package_name varchar2(500);
18 tmp_procedure_name varchar2(500);
19 v_counter pls_integer := 1;
20
21
22 /*
23 This nested procedure is called from main procedure "get_procedure_status" to check first
24 that the package exist or not and whether the package is valid or not before checking the
25 procedure details with nested procedure "is_proc_not_found_or_invalid"
26 */
27
28 procedure is_pkg_not_found_or_invalid(
29 v_rec_package_details in out nocopy rec_procedure_details)
30 is
31 cursor cur_get_pkg_dtls
32 is
33 select status,
34 object_type
35 from all_objects
36 where object_type in ('PACKAGE', 'PACKAGE BODY')
37 and object_name = v_rec_package_details.package_name
38 and owner =v_rec_package_details.schema_name;
39 v_get_pkg_dtls cur_get_pkg_dtls%rowtype;
40
41 begin
42 open cur_get_pkg_dtls;
43 loop
44 fetch cur_get_pkg_dtls into v_get_pkg_dtls;
45 if cur_get_pkg_dtls%notfound then
46 if cur_get_pkg_dtls%rowcount =0 then
47 v_rec_package_details.problem_details := 'Package Not Found';
48 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
49 elsif cur_get_pkg_dtls%rowcount =1 then
50 v_rec_package_details.problem_details := 'Package Body Not Found';
51 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
52 end if;
53 exit;
54 else
55 if v_get_pkg_dtls.status = 'INVALID' then
56 v_rec_package_details.problem_details := 'Package is Invaild';
57 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
58 end if;
59 end if;
60 end loop;
61 close cur_get_pkg_dtls;
62 exception
63 when others then
64 if (cur_get_pkg_dtls%isopen) then
65 close cur_get_pkg_dtls;
66 end if;
67 raise;
68 end is_pkg_not_found_or_invalid;
69
70
71 /*
72 This nested procedure is called from main procedure "get_procedure_status"
73 to check whether a standalone or packaged procedure/function exist or not
74 into the database and also matches its parameters as defined in map
75 */
76 procedure is_proc_not_found_or_invalid(
77 v_rec_package_details in out nocopy rec_procedure_details )
78 is
79
80
81 /* If the procedure is standalone procedure then this cursor checks that
82 whether procedure exist or not and also its status
83 */
84 cursor cur_get_standalone_proc_dtls
85 is
86 select status
87 from all_objects
88 where object_type = 'PROCEDURE'
89 and object_name = v_rec_package_details.procedure_name
90 and owner =v_rec_package_details.schema_name;
91
92
93 /* If the procedure is packaged procedure then this cursor checks
94 that the procedure exist or not. It does not match the parameters
95 */
96 cursor cur_get_packaged_proc_dtls
97 is
98 select count(1) count
99 from all_procedures
100 where object_name = v_rec_package_details.package_name
101 and procedure_name= v_rec_package_details.procedure_name
102 and owner = v_rec_package_details.schema_name;
103
104
105 /*This cursor retrieves the parameter details of procedure/function from map.*/
106
107 cursor cur_getparam_dtl_mapproc
108 is
109 select upper(epm.parameter_name) parameter_name,
110 case epm.data_type
111 when 1
112 then 'VARCHAR2'
113 when 2
114 then 'NUMBER'
115 when 12
116 then 'DATE'
117 when 96
118 then 'CHAR'
119 when 112
120 then 'CLOB'
121 end data_type,
122 case epm.action_type
123 when 1060
124 then 'IN'
125 when 1070
126 then 'OUT'
127 when 1080
128 then 'IN/OUT'
129 end direction
130 from ecx_proc_mappings epm
131 where epm.map_id =v_rec_package_details.map_id
132 and epm.transtage_id =
133 (select transtage_id
134 from ecx_tran_stage_data etsd
135 where etsd.map_id = v_rec_package_details.map_id
136 and upper(etsd.custom_procedure_name)= v_rec_package_details.full_name
137 and rownum =1
138 )
139 order by epm.procmap_id;
140
141
142 /*
143 This cursor retrieves the parameter details of a packaged procedure from the database.
144 This cursor will be used to match the parameter details as defined in map (retrieved from
145 cur_getparam_dtl_mapproc), if the procedure/function is found in database
146 */
147
148 cursor cur_get_param_dtl_packagedproc (overloadnum in pls_integer)
149 is
150 select aa.argument_name,
151 aa.data_type,
152 aa.in_out
153 from all_arguments aa,
154 all_objects ao
155 where ao.object_id = aa.object_id
156 and aa.object_name =v_rec_package_details.procedure_name
157 and aa.owner =v_rec_package_details.schema_name
158 and nvl(aa.overload,1) =overloadnum
159 and aa.argument_name is not null
160 and ao.object_name = v_rec_package_details.package_name
161 and ao.object_type = 'PACKAGE'
162 and ao.owner =v_rec_package_details.schema_name
163 order by position;
164
165
166 /* Get the count of overloaded procedures / functions which are a within a PACKAGE */
167
168 cursor cur_get_overload_num
169 is
170 select distinct nvl(overload, 1) overloadnum
171 from all_arguments aa,
172 all_objects ao
173 where ao.object_id = aa.object_id
174 and aa.object_name =v_rec_package_details.procedure_name
175 and aa.owner =v_rec_package_details.schema_name
176 and ao.object_name = v_rec_package_details.package_name
177 and ao.object_type = 'PACKAGE'
178 and ao.owner =v_rec_package_details.schema_name
179 order by overloadnum;
180
181
182 /*
183 This cursor retrieves the parameter details of stand-alone procedure/function
184 This cursor will be used to match the parameter details as defined in map
185 (retrieved from cur_getparam_dtl_mapproc), if the procedure/function is found in database
186 */
187
188 cursor cur_getparamdtl_standaloneproc
189 is
190 select aa.argument_name,
191 aa.data_type,
192 aa.in_out
193 from all_arguments aa,
194 all_objects ao
195 where ao.object_id = aa.object_id
196 and ao.object_name =v_rec_package_details.procedure_name
197 and ao.owner =v_rec_package_details.schema_name
198 and ao.object_type = 'PROCEDURE'
199 and aa.owner =v_rec_package_details.schema_name
200 order by position;
201
202 type tab_getparam_dtl_mapproc
203 is table of cur_getparam_dtl_mapproc%rowtype index by binary_integer;
204
205 v_tab_getparam_dtl_mapproc tab_getparam_dtl_mapproc;
206 v_get_standalone_proc_dtls cur_get_standalone_proc_dtls%rowtype;
207 v_get_packaged_proc_dtls cur_get_packaged_proc_dtls%rowtype;
208 v_counter pls_integer;
209
210 begin
211
212 /*Retrieves the parameter details of map procedure/function*/
213
214 open cur_getparam_dtl_mapproc;
215 fetch cur_getparam_dtl_mapproc bulk collect into v_tab_getparam_dtl_mapproc;
216 close cur_getparam_dtl_mapproc;
217
218 /* If block if the procedure/function is standalone procedure*/
219
220 if v_rec_package_details.package_name is null then
221 v_rec_package_details.problem_details:=null;
222
223 /* Checks whether the standalone procedure/function exist in database*/
224
225 open cur_get_standalone_proc_dtls;
226 loop
227 fetch cur_get_standalone_proc_dtls into v_get_standalone_proc_dtls;
228 if cur_get_standalone_proc_dtls%notfound then
229 if cur_get_standalone_proc_dtls%rowcount <1 then
230 v_rec_package_details.problem_details := 'Procedure Not Found';
231 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
232 end if;
233 exit;
234 else
235 if v_get_standalone_proc_dtls.status = 'INVALID' then
236 v_rec_package_details.problem_details := 'Procedure is Invaild';
237 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
238 end if;
239 end if;
240 end loop;
241 close cur_get_standalone_proc_dtls;
242
243 /* If the stanalone procedure/function name exist in database then matches its parameters*/
244
245 if v_rec_package_details.problem_details is null then
246 v_counter :=0;
247 for x in cur_getparamdtl_standaloneproc
248 loop
249 if v_tab_getparam_dtl_mapproc.exists(v_counter) then
250
251 if x.argument_name = v_tab_getparam_dtl_mapproc(v_counter).parameter_name
252 and (x.data_type= v_tab_getparam_dtl_mapproc(v_counter).data_type
253 or v_tab_getparam_dtl_mapproc(v_counter).data_type='VARCHAR2')
254 and x.in_out= v_tab_getparam_dtl_mapproc(v_counter).direction then
255 v_rec_package_details.problem_details :=null;
256 v_rec_package_details.pkg_proc_notfoundorinvalid := 'false';
257 else
258 v_rec_package_details.problem_details := 'Procedure parameters not matched';
259 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
260 end if;
261
262 end if;
263 end loop;
264
265 if v_tab_getparam_dtl_mapproc.count <> v_counter then
266 v_rec_package_details.problem_details := 'Procedure parameters not matched';
267 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
268 end if;
269 end if;
270
271 /* Else block to check packaged procedures/functions in database */
272
273 else
274 /* Cursor to check if packaged procedure/function name exist */
275
276 open cur_get_packaged_proc_dtls;
277 fetch cur_get_packaged_proc_dtls
278 into v_get_packaged_proc_dtls;
279
280 if v_get_packaged_proc_dtls.count = 0 then
281
282 if v_rec_package_details.problem_details is null then
283 v_rec_package_details.problem_details := 'Procedure Not Found';
284 end if;
285
286 v_rec_package_details.pkg_proc_notfoundorinvalid:= 'TRUE';
287 end if;
288 close cur_get_packaged_proc_dtls;
289
290 /*
291 Block to match parameter details of packaged procedure/functions if they exist
292 in database. If the packaged procedure/function is overloaded then it will continue in loop
293 to match parameters with any one of them till it finds a match
294 */
295
296 if v_rec_package_details.problem_details is null then
297 for y in cur_get_overload_num
298 loop
299 if (v_rec_package_details.problem_details is null and y.overloadnum>1) then
300 exit;
301 end if;
302
303 v_counter := 0;
304 v_rec_package_details.problem_details :=null;
305 v_rec_package_details.pkg_proc_notfoundorinvalid := 'FALSE';
306
307 for x in cur_get_param_dtl_packagedproc (y.overloadnum)
308 loop
309 v_counter:= v_counter+1;
310 if v_tab_getparam_dtl_mapproc.exists(v_counter) then
311 if (x.argument_name = v_tab_getparam_dtl_mapproc(v_counter).parameter_name
312 and (x.data_type= v_tab_getparam_dtl_mapproc(v_counter).data_type
313 or v_tab_getparam_dtl_mapproc(v_counter).data_type='VARCHAR2')
314 and x.in_out= v_tab_getparam_dtl_mapproc(v_counter).direction ) then
315 null;
316 else
317 v_rec_package_details.problem_details := 'Procedure parameters not matched';
318 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
319 end if;
320 end if;
321 end loop;
322
323 if v_tab_getparam_dtl_mapproc.count <> v_counter then
324 v_rec_package_details.problem_details := 'Procedure parameters not matched';
325 v_rec_package_details.pkg_proc_notfoundorinvalid := 'TRUE';
326 end if;
327
328 end loop;
329 end if;
330 end if;
331 exception
332 when others then
333 if (cur_get_standalone_proc_dtls%isopen) then
334 close cur_get_standalone_proc_dtls;
335 end if;
336
337 if (cur_get_packaged_proc_dtls%isopen) then
338 close cur_get_packaged_proc_dtls;
339 end if;
340
341 if (cur_getparam_dtl_mapproc%isopen) then
342 close cur_getparam_dtl_mapproc;
343 end if;
344
345 if (cur_get_param_dtl_packagedproc%isopen) then
346 close cur_get_param_dtl_packagedproc;
347 end if;
348
349 if (cur_get_overload_num%isopen) then
350 close cur_get_overload_num;
351 end if;
352
353 if (cur_getparamdtl_standaloneproc%isopen) then
354 close cur_getparamdtl_standaloneproc;
355 end if;
356
357 raise;
358 end is_proc_not_found_or_invalid;
359
360 /* Begin block for main procedure--"get_procedure_status" */
361 begin
362
363 /*
364 Retrives details of all referenced procedures/functions from maps uploaded in database
365 and stores the details into a nested table
366 */
367
368 for x in
369 (select distinct etsd.map_id,
370 em.map_code,
371 upper(etsd.custom_procedure_name) full_name
372 from ecx_tran_stage_data etsd,
373 ecx_mappings em
374 where etsd.map_id = em.map_id
375 and custom_procedure_name is not null
376 order by full_name
377 )
378 loop
379 v_tab_procedure_details.extend(1);
380 tmp_schema_name := substr(x.full_name,1, instr(x.full_name, '.', 1, 1)-1);
381 tmp_schema_name := substr(x.full_name,1, instr(x.full_name, '.', 1, 1)-1);
382 tmp := substr(x.full_name, instr(x.full_name, '.', 1, 1) +1,
383 instr(x.full_name, '.', 1, 2)-instr(x.full_name, '.', 1, 1)-1);
384 if tmp is null then
385 tmp_package_name := null;
386 tmp_procedure_name:= substr(x.full_name, instr(x.full_name, '.', 1, 1)+1);
387 else
388 tmp_package_name := tmp;
389 tmp_procedure_name:= substr(x.full_name, instr(x.full_name, '.', 1, 2)+1);
390 end if;
391 v_tab_procedure_details(v_counter) := rec_procedure_details(x.map_id, x.map_code,
392 x.full_name, tmp_schema_name, tmp_procedure_name,
393 tmp_package_name, NULL, 'FALSE');
394
395 /* Calls the nested procedure--is_pkg_not_found_or_invalid */
396 is_pkg_not_found_or_invalid(v_tab_procedure_details(v_counter));
397
398 /* Calls the nested procedure--is_proc_not_found_or_invalid */
399 is_proc_not_found_or_invalid(v_tab_procedure_details(v_counter));
400 v_counter:= v_counter+1;
401 end loop;
402
403 /*
404 Values from this opened ref cursor are being fetched into java class--
405 oracle.apps.fnd.wf.diagnose.procedurevalidationtest
406 */
407 open cur for
408 select *
409 from table(cast(v_tab_procedure_details as tab_procedure_details)) t1
410 where t1.pkg_proc_notfoundorinvalid= 'TRUE';
411
412 exception
413 when others then
414 raise;
415
416 end get_procedure_status;
417
418
419 /*
420 This procedure is being used for 'XSL Transform Test' inside 'XML Gateway Health Check' diagnostic
421 This procedure transforms a sample xml with special character against a sample xsl using database
422 XDK and checks whether the XML is transformed as expected
423 */
424
425 procedure xsl_transform_test(
426 p_result out nocopy varchar2,
427 p_xdkversion out nocopy varchar2)
428 is
429 v_parser_xml xmlparser.parser;
430 v_xml_node xmldom.domnode;
431 v_xmldoc xmldom.domdocument;
432 v_out clob;
433 v_doctype xmldom.domdocumenttype;
434 -- xsl
435 v_parser_xsl xmlparser.parser;
436 v_xmldoc_xsl xmldom.domdocument;
437 v_stylesheet xslprocessor.stylesheet;
438 v_processor xslprocessor.processor;
439 v_docfrag xmldom.domdocumentfragment;
440 v_docfragnode xmldom.domnode;
441 l_xml varchar2(1000);
442 l_xsl varchar2(1000);
443 l_xml_clob clob;
444 l_xsl_clob clob;
445 expected_output varchar2(235):= 'Result:Test Case with and:& and quote: pH (larutan 10%, 27''C)';
446 begin
447 -- dbms_output.put_line('==========================================');
448 l_xml := '<?xml version="1.0" standalone="no"?><temp>Test Case with and:& and quote: pH (larutan 10%, 27'C) </temp>';
449 l_xsl := '<?xml version="1.0"?>';
450 l_xsl := l_xsl || '<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">';
451 l_xsl := l_xsl || '<xsl:output method="text" indent="no" encoding="ISO-8859-1"/>';
452 l_xsl := l_xsl || '<xsl:template match="/">Result:';
453 l_xsl := l_xsl || '<xsl:value-of select="temp"/>';
454 l_xsl := l_xsl || '</xsl:template></xsl:stylesheet>';
455 l_xml_clob := l_xml;
456 l_xsl_clob := l_xsl;
457 p_xdkversion:= ecx_utils.xmlversion;
458 dbms_lob.createtemporary(v_out,false,dbms_lob.session);
459 -- parse and get ge DOMDocument from xml
460 v_parser_xml := xmlparser.newparser;
461 xmlparser.setvalidationmode(v_parser_xml, false);
462 xmlparser.parseclob(v_parser_xml,l_xml_clob);
463 --get document
464 v_xmldoc := xmlparser.getdocument(v_parser_xml);
465 --starting the transformation
466 -- parse and get the DOMDocument from xsl
467 v_parser_xsl := xmlparser.newparser;
468 xmlparser.setpreservewhitespace(v_parser_xsl,true);
469 --xmlparser.parse(v_parser_xsl,dir||'/'||xslfile);
470 xmlparser.parseclob(v_parser_xsl,l_xsl_clob);
471 --get the DOMDocument from xsl
472 v_xmldoc_xsl := xmlparser.getdocument(v_parser_xsl);
473 -- make stylesheet
474 v_stylesheet := xslprocessor.newstylesheet(v_xmldoc_xsl,NULL);
475 -- process xsl
476 v_processor := xslprocessor.newprocessor;
477 --one way [will not escape any characters] $ as $
478 xslprocessor.processxsl(v_processor, v_stylesheet, v_xmldoc,v_out);
479 if trim(dbms_lob.substr(v_out,235,1))= expected_output then
480 p_result := 'TRUE';
481 else
482 p_result:= 'FALSE';
483 end if;
484 -- free resources
485 xslprocessor.freestylesheet(v_stylesheet);
486 xslprocessor.freeprocessor(v_processor);
487 xmldom.freedocument(v_xmldoc_xsl);
488 xmlparser.freeparser(v_parser_xsl);
489 xmldom.freedocument(v_xmldoc);
490 xmlparser.freeparser(v_parser_xml);
491 dbms_lob.freetemporary(v_out);
492 --p_result:= 'TRUE';
493 exception
494 when others then
495 p_result := 'FALSE';
496 raise;
497 end xsl_transform_test;
498
499
500 /*
501 This function is used for 'Message Monitor Test' to verify that whether a particular message exist in
502 ecx outbound queue or not
503 */
504
505 function check_msg_outqueue(
506 p_msgid in varchar2)
507 return varchar2
508 is
509 msgfoundinoutqueue varchar2(10):= 'FALSE';
510 begin
511 for x in
512 (select 1 from ecx_outqueue where msgid= p_msgid
513 )
514 loop
515 msgfoundinoutqueue:= 'TRUE';
516 end loop;
517 return msgfoundinoutqueue;
518 exception
519 when others then
520 raise;
521 end check_msg_outqueue;
522
523 end ecx_diagnostics_test;
[Home] [Help]