1 Package Body az_plsql_xml as
2 /* $Header: azxmlulb.pls 115.4 2003/03/08 00:05:04 jke noship $ */
3 --
4 --
5 -- Type Definitions
6 --
7 type tbl_parameter_name is table of varchar2(30) index by binary_integer;
8 type tbl_parameter_datatype is table of number index by binary_integer;
9 --
10 -- Error Exceptions which can be raised by dbms_describe.describe_procedure
11 --
12 --
13 -- Package does not exist in the database
14 --
15 Package_Not_Exists exception;
16 Pragma Exception_Init(Package_Not_Exists, -6564);
17 --
18 -- Procedure does not exist in the package
19 --
20 Proc_Not_In_Package exception;
21 Pragma Exception_Init(Proc_Not_In_Package, -20001);
22 --
23 -- Object is remote
24 --
25 Remote_Object exception;
26 Pragma Exception_Init(Remote_Object, -20002);
27 --
28 -- Package is invalid
29 --
30 Invalid_Package exception;
31 Pragma Exception_Init(Invalid_Package, -20003);
32 --
33 -- Invalid Object Name
34 --
35 Invalid_Object_Name exception;
36 Pragma Exception_Init(Invalid_Object_Name, -20004);
37 --
38 -- Other Error Exceptions
39 --
40 Plsql_Value_Error exception;
41 Pragma Exception_Init(Plsql_Value_Error, -6502);
42 --
43 XMLParserError exception;
44 Pragma Exception_Init(XMLParserError, -20100);
45 --
46 -- Package Variables
47 --
48 g_package varchar2(33) := ' az_plsql_xml.';
49 --
50 g_source varchar2(32767);
51 g_error_expected boolean;
52 --
53 -- Oracle Internal DataType, Parameter, Default Codes and New Line Constants
54 --
55 c_dtype_undefined constant number default 0;
56 c_dtype_varchar2 constant number default 1;
57 c_dtype_number constant number default 2;
58 c_dtype_long constant number default 8;
59 c_dtype_date constant number default 12;
60 c_dtype_boolean constant number default 252;
61 --
62 c_ptype_in constant number default 0;
63 --
64 c_default_defined constant number default 1;
65 --
66 c_new_line constant varchar2(1) default '
67 ';
68 --
69 -- Local Procedures and Functions
70 --
71 function parse
72 (p_xml in varchar2
73 ) return xmldom.DOMDocument is
74 l_retDoc xmldom.DOMDocument;
75 l_parser xmlparser.parser;
76 begin
77 l_parser := xmlparser.newParser;
78 xmlparser.parseBuffer(l_parser,p_xml);
79 l_retDoc := xmlparser.getDocument(l_parser);
80 return l_retDoc;
81 exception
82 when XMLParserError THEN
83 xmlparser.freeParser(l_parser);
84 return l_retDoc;
85 end parse;
86 --
87 function tonode(doc xmldom.DOMDocument) return xmldom.DOMNode is
88 begin
89 return xmldom.makenode(doc);
90 end tonode;
91 --
92 function valueOf
93 (p_node in xmldom.DOMNode
94 ,p_xpath in varchar2
95 ) return varchar2 is
96 begin
97 if xmldom.isnull(p_node) or p_xpath is null then
98 return null;
99 else
100 return xslprocessor.valueof(p_node, p_xpath);
101 end if;
102 end valueOf;
103 --
104 function valueOf
105 (p_doc in xmldom.DOMDocument
106 ,p_xpath in varchar2
107 ) return varchar2 is
108 begin
109 if xmldom.isnull(p_doc) or p_xpath is null then
110 return null;
111 else
112 return valueof(toNode(p_doc), p_xpath);
113 end if;
114 end valueOf;
115 --
116 function selectNodes
117 (p_node in xmldom.DOMNode
118 ,p_xpath in varchar2
119 ) return xmldom.DOMNodeList is
120 begin
121 return xslprocessor.selectNodes(p_node, p_xpath);
122 end selectNodes;
123 --
124 function selectNodes
125 (p_doc in xmldom.DOMDocument
126 ,p_xpath in varchar2
127 ) return xmldom.DOMNodeList is
128 begin
129 return selectNodes(toNode(p_doc), p_xpath);
130 end selectNodes;
131 --
132 procedure execute_source
133 (p_source in varchar2
134 ) is
135 l_dynamic_cursor integer; -- Dynamic sql cursor
136 l_execute integer; -- Value returned by
137 -- dbms_sql.execute
138 l_proc varchar2(72) := g_package||'execute_source';
139 begin
140 hr_utility.set_location('Entering:'|| l_proc, 10);
141 --
142 -- The whole of the new package body code has now been built,
143 -- use dynamic SQL to execute the create or replace package statement
144 --
145 hr_utility.set_location(l_proc, 11);
146 l_dynamic_cursor := dbms_sql.open_cursor;
147 dbms_sql.parse(l_dynamic_cursor, p_source, dbms_sql.v7);
148 l_execute := dbms_sql.execute(l_dynamic_cursor);
149 dbms_sql.close_cursor(l_dynamic_cursor);
150 hr_utility.set_location(' Leaving:'|| l_proc, 20);
151 exception
152 --
153 -- In case of an unexpected error close the dynamic cursor
154 -- if it was successfully opened.
155 --
156 when others then
157 if (dbms_sql.is_open(l_dynamic_cursor)) then
158 dbms_sql.close_cursor(l_dynamic_cursor);
159 end if;
160 raise;
161 end execute_source;
162 --
163 -- Public Procedures and Functions
164 --
165 procedure call_plsql_api
166 (p_xml in varchar2
167 ) as
168 -- Note this procedure makes many assumptions including.
169 -- *) The API to call has at least one parameter reference.
170 -- *) API call is a package procedure, no procedure onlys
171 -- or functions inside or outside of a package.
172 --
173 -- Local variables to catch the values returned from
174 -- hr_general.describe_procedure
175 --
176 l_overload dbms_describe.number_table;
177 l_position dbms_describe.number_table;
178 l_level dbms_describe.number_table;
179 l_argument_name dbms_describe.varchar2_table;
180 l_datatype dbms_describe.number_table;
181 l_default_value dbms_describe.number_table;
182 l_in_out dbms_describe.number_table;
183 l_length dbms_describe.number_table;
184 l_precision dbms_describe.number_table;
185 l_scale dbms_describe.number_table;
186 l_radix dbms_describe.number_table;
187 l_spare dbms_describe.number_table;
188 --
189 -- Other local variables
190 --
191 l_xmldoc xmldom.DOMDocument;
192 l_api_name varchar2(32);
193 l_api_pkg varchar2(32);
194 l_param_value varchar2(200); -- Value of parameter as listed
195 -- in xml document.
196 l_param_link varchar2(200); -- Value of parameter link argument
197 -- in the xml document. A null
198 -- indicates the argument has not
199 -- been defined for this parameter.
200 l_var_name varchar2(30); -- Holds the variable name used with
201 -- OUT and IN OUT parameters.
202 l_not_first_param boolean := false; -- A true value indicates the
203 -- first parameter has already
204 -- been added to the code. So
205 -- for the remaining parameters
206 -- , need to be included.
207 l_loop binary_integer; -- Loop counter.
208 l_pre_overload number; -- Overload number for the previous
209 -- parameter.
210 l_describe_error boolean := false; -- Indicates if the hr_general.
211 -- describe_procedure raised an
212 -- error for the call package
213 -- procedure.
214 l_call_code varchar2(32767) := null;
215 l_declare_code varchar2(32767) := null;
216 l_api_call_list xmldom.DOMNodeList; -- List of nodes pointing to each
217 -- API call in the API Call Set.
218 l_print_length number(15); -- Length position for printing out
219 -- source code for execution.
220 l_proc varchar2(72) := g_package||'call_plsql_api';
221 begin
222 hr_utility.set_location('Entering:'|| l_proc, 10);
223 --hr_utility.set_location(p_xml,11);
224 --
225 -- Parse the XML Document
226 --
227 l_xmldoc := parse(p_xml);
228 if not xmldom.isnull(l_xmldoc) then
229 hr_utility.set_location(l_proc, 12);
230 --
231 -- Once only set-up for start of PL/SQL block
232 --
233 l_declare_code := 'declare ' || c_new_line || 'l_proc varchar2(30);' || c_new_line;
234 l_call_code := 'begin ' || c_new_line;
235 hr_utility.set_location(l_proc, 13);
236 --
237 -- Loop for each APICall in the APICallSet
238 --
239 l_api_call_list := selectNodes(l_xmldoc, '/APICallSet/APICall');
240 for l_num in 1..xmldom.getlength(l_api_call_list) loop
241 --
242 -- Obtain the Name of the API to call from the XML Document
243 --
244 l_api_name := valueOf(xmldom.item(l_api_call_list, l_num - 1), 'APIName');
245 l_api_pkg := valueOf(xmldom.item(l_api_call_list, l_num - 1), 'APIPkg');
246 --
247 -- Above lines before processing sets were:
248 -- l_api_name := valueof(l_xmldoc, '/APICallSet/APICall/APIName');
249 -- l_api_pkg := valueof(l_xmldoc, '/APICallSet/APICall/APIPkg');
250 --
251 hr_utility.trace('Now processing ' || l_api_pkg || '.' || l_api_name);
252 hr_utility.set_location(l_proc, 15);
253 --
254 -- Obtain the API parameter list by calling an RDBMS procedure
255 -- to obtain the list of parameters to the call package procedure.
256 -- A separate begin ... end block has been specified so that errors
257 -- raised by hr_general.describe_procedure can be trapped and
258 -- handled locally.
259 --
260 begin
261 hr_general.describe_procedure
262 (object_name => l_api_pkg || '.' || l_api_name
263 ,reserved1 => null
264 ,reserved2 => null
265 ,overload => l_overload
266 ,position => l_position
267 ,level => l_level
268 ,argument_name => l_argument_name
269 ,datatype => l_datatype
270 ,default_value => l_default_value
271 ,in_out => l_in_out
272 ,length => l_length
273 ,precision => l_precision
274 ,scale => l_scale
275 ,radix => l_radix
276 ,spare => l_spare
277 );
278 exception
279 when Package_Not_Exists then
280 -- Error: The call_package does not exist in the database. Code to
281 -- carry out this hook call has not been created.
282 hr_utility.set_message(800, 'HR_51950_AHC_CALL_PKG_NO_EXIST');
283 l_describe_error := true;
284 hr_utility.set_location(l_proc, 20);
285 when Proc_Not_In_Package then
286 -- Error: The call_procedure does not exist in the call_package.
287 -- Code to carry out this hook call has not been created.
288 hr_utility.set_message(800, 'HR_51951_AHC_CALL_PRO_NO_EXIST');
289 l_describe_error := true;
290 hr_utility.set_location(l_proc, 30);
291 when Remote_Object then
292 -- Error: Remote objects cannot be called from API User Hooks.
293 -- Code to carry out this hook call has not been created.
294 hr_utility.set_message(800, 'HR_51952_AHC_CALL_REMOTE_OBJ');
295 l_describe_error := true;
296 hr_utility.set_location(l_proc, 40);
297 when Invalid_Package then
298 -- Error: The call_package code in the database is invalid.
299 -- Code to carry out this hook call has not been created.
300 hr_utility.set_message(800, 'HR_51953_AHC_CALL_PKG_INVALID');
301 l_describe_error := true;
302 hr_utility.set_location(l_proc, 50);
303 when Invalid_Object_Name then
304 -- Error: An error has occurred while attempting to parse the name of
305 -- the call package and call procedure. Check the package and procedure
306 -- names. Code to carry out this hook call has not been created.
307 hr_utility.set_message(800, 'HR_51954_AHC_CALL_PARSE');
308 l_describe_error := true;
309 hr_utility.set_location(l_proc, 60);
310 end;
311 hr_utility.set_location(l_proc, 70);
312 if not l_describe_error then
313 --
314 -- Loop through the API parameter list.
315 -- For IN and IN OUT parameters checking to see if the
316 -- parameter is mentioned in the XML document. If it then
317 -- use the value as an IN parameter.
318 -- FOR OUT and IN OUT parameters add a local variable to
319 -- catch the returned value.
320 --
321 l_call_code := l_call_code || l_api_pkg || '.' || l_api_name || '(' || c_new_line;
322 hr_utility.set_location(l_proc, 90);
323 l_not_first_param := false;
324 --
325 -- Search through the tables returned to create the parameter list
326 --
327 l_loop := 1;
328 l_pre_overload := l_overload(1);
329 begin
330 --
331 -- There is separate PL/SQL block for reading from the PL/SQL tables.
332 -- We do not know how many parameters exist. So we have to keep reading
333 -- from the tables until PL/SQL finds a row when has not been
334 -- initialised and raises a NO_DATA_FOUND exception or an invalid
335 -- parameter is found.
336 -- Assumption: If an API has more than one overloaded version then
337 -- the only the first one returned by the DBMS_DESCRIBLE package will
338 -- be used. Ideally should do the same as DataPump. When more than
339 -- one exists then identify the latest version, by the greater number
340 -- of parameters.
341 --
342 while l_pre_overload = l_overload(l_loop) loop
343 --
344 -- Find if parameter is has value in the XML document
345 --
346 l_param_value :=
347 valueOf(xmldom.item(l_api_call_list, l_num - 1), l_argument_name(l_loop));
348 l_param_link :=
349 valueOf(xmldom.item(l_api_call_list, l_num - 1), l_argument_name(l_loop)||'/@LINK');
350 --
351 -- Processing before sets the above line was:
352 -- l_param_value := valueof(l_xmldoc, '/APICall/' || l_argument_name(l_loop));
353 --
354 -- hr_utility.trace(l_argument_name(l_loop) ||' l_param_value is ' || l_param_value || ' l_param_link is ' || l_param_link);
355 -- Process depending on IN, OUT, IN OUT status
356 if l_in_out(l_loop) = 0 then
357 -- hr_utility.set_location(l_proc, 100);
358 -- Parameter is IN
359 -- Only add to procedure list if the parameter has
360 -- been given a value in the XML document or a link
361 -- value has been defined.
362 if l_param_value is not null or
363 l_param_link is not null then
364 if l_not_first_param then
365 -- Have already processed the first parameter. Separate this
366 -- parameter from the previous parameter with a ,
367 l_call_code := l_call_code || ',';
368 else
369 l_not_first_param := true;
370 end if;
371 -- Add parameter to list of parameters in call
372 l_call_code := l_call_code || l_argument_name(l_loop) || ' => ';
376 -- No link defined. Use data value from XML document
373 -- Decide what the parameter value should be set to
374 -- depending on whether a link argument has been defined
375 if l_param_link is null then
377 if l_datatype(l_loop) = c_dtype_varchar2 then
378 l_call_code := l_call_code || '''' || l_param_value || '''' || c_new_line;
379 else
380 l_call_code := l_call_code || l_param_value || c_new_line;
381 end if;
382 else -- Link has been defined
383 -- N.B. This assumes that link has already been
384 -- defined in the local variable list from a previous
385 -- API IN OUT or OUT parameter. This assumption is not
386 -- validated.
387 l_call_code := l_call_code || 'l_' || l_param_link || c_new_line;
388 end if;
389 end if;
390 elsif l_in_out(l_loop) = 1 then
391 -- hr_utility.set_location(l_proc, 110);
392 -- Parameter is OUT
393 -- Add local variable to the declare section with the
394 -- same datatype. Add to the procedure list so local
395 -- variable catches any return result.
396 -- For now just ignore any value in the XML document.
397 if l_not_first_param then
398 -- Have already processed the first parameter. Separate this
399 -- parameter from the previous parameter with a ,
400 l_call_code := l_call_code || ',';
401 else
402 l_not_first_param := true;
403 end if;
404 --
405 -- Name of local variable depends on whether a link has been defined
406 -- in the XML document for this parameter.
407 if l_param_link is not null then
408 l_var_name := 'l_' || l_param_link;
409 else
410 -- Derive from parameter name change p_ for l_
411 l_var_name := 'l' || substr(l_argument_name(l_loop), 2);
412 end if;
413 -- Only add local variable to delcare section if does not
414 -- already exist. This check is required for allow for re-use
415 -- of Link names. At the moment their is an assumption that
416 -- each instance of the local variable will have the same datatype
417 -- length.
418 --
419 if instr(l_declare_code, l_var_name) = 0 then
420 -- hr_utility.set_location(l_proc, 120);
421 -- Add local variable name
422 l_declare_code := l_declare_code || l_var_name;
423 -- Add datatype
424 if l_datatype(l_loop) = c_dtype_varchar2 then
425 l_declare_code := l_declare_code || ' varchar2(200);' || c_new_line;
426 elsif l_datatype(l_loop) = c_dtype_number then
427 l_declare_code := l_declare_code || ' number(15);' || c_new_line;
428 elsif l_datatype(l_loop) = c_dtype_long then
429 l_declare_code := l_declare_code || ' long;' || c_new_line;
430 elsif l_datatype(l_loop) = c_dtype_date then
431 l_declare_code := l_declare_code || ' date;' || c_new_line;
432 elsif l_datatype(l_loop) = c_dtype_boolean then
433 l_declare_code := l_declare_code || ' boolean;' || c_new_line;
434 elsif l_datatype(l_loop) = c_dtype_undefined then
435 -- Unexpected need to raise error;
436 null;
437 end if;
438 end if;
439 -- hr_utility.set_location(l_proc, 130);
440 l_call_code := l_call_code || l_argument_name(l_loop) || ' => ';
441 l_call_code := l_call_code || l_var_name || c_new_line;
442 elsif l_in_out(l_loop) = 2 then
443 -- hr_utility.set_location(l_proc, 140);
444 -- Parameter is IN OUT
445 -- Add local variable to the declare section. If a value
446 -- as been given in the XML document then default the
447 -- local variable to that value. Also add to the
448 -- procedure list so local variable catches any return
449 -- result.
450 if l_not_first_param then
451 -- Have already processed the first parameter. Separate this
452 -- parameter from the previous parameter with a ,
453 l_call_code := l_call_code || ',';
454 else
455 l_not_first_param := true;
456 end if;
457 --
458 -- Name of local variable depends on whether a link has been defined
459 -- in the XML document for this parameter.
460 if l_param_link is not null then
461 l_var_name := 'l_' || l_param_link;
462 else
463 -- Derive from parameter name change p_ for l_
464 l_var_name := 'l' || substr(l_argument_name(l_loop), 2);
465 end if;
466 -- Only add local variable to delcare section if does not
467 -- already exist. This check is required for allow for re-use
468 -- of Link names. At the moment their is an assumption that
469 -- each instance of the local variable will have the same datatype
470 -- length.
474 -- Add local variable name
471 --
472 if instr(l_declare_code, l_var_name) = 0 then
473 -- hr_utility.set_location(l_proc, 150);
475 l_declare_code := l_declare_code || l_var_name;
476 -- Add datatype with or without defaulted value
477 -- Assumption: When a local variable is being used with
478 -- an IN OUT parameter, there is only one default in parameter.
479 -- At the moment if an there is a second API call with an IN OUT
480 -- parameter of the same name then any further IN values provided
481 -- in the XML document will be ignored. So second API call will
482 -- actually get the OUT value from the first API call.
483 --
484 -- When an IN parameter value has been provided include default value
485 -- in local variable declare. Otherwise don't mention default in declare.
486 if l_param_value is not null then
487 -- hr_utility.set_location(l_proc, 160);
488 if l_datatype(l_loop) = c_dtype_varchar2 then
489 l_declare_code := l_declare_code || ' varchar2(200) := ' || '''' || l_param_value || '''' ;
490 l_declare_code := l_declare_code || ';' || c_new_line;
491 elsif l_datatype(l_loop) = c_dtype_number then
492 l_declare_code := l_declare_code || ' number(15) := ' || l_param_value;
493 l_declare_code := l_declare_code || ';' || c_new_line;
494 elsif l_datatype(l_loop) = c_dtype_long then
495 l_declare_code := l_declare_code || ' long := ' || l_param_value;
496 l_declare_code := l_declare_code || ';' || c_new_line;
497 elsif l_datatype(l_loop) = c_dtype_date then
498 l_declare_code := l_declare_code || ' date := ' || l_param_value;
499 l_declare_code := l_declare_code || ';' || c_new_line;
500 elsif l_datatype(l_loop) = c_dtype_boolean then
501 l_declare_code := l_declare_code || ' boolean := ' || l_param_value;
502 l_declare_code := l_declare_code || ';' || c_new_line;
503 elsif l_datatype(l_loop) = c_dtype_undefined then
504 -- Unexpected need to raise error;
505 null;
506 end if;
507 else
508 -- hr_utility.set_location(l_proc, 170);
509 if l_datatype(l_loop) = c_dtype_varchar2 then
510 l_declare_code := l_declare_code || ' varchar2(200);' || c_new_line;
511 elsif l_datatype(l_loop) = c_dtype_number then
512 l_declare_code := l_declare_code || ' number(15);' || c_new_line;
513 elsif l_datatype(l_loop) = c_dtype_long then
514 l_declare_code := l_declare_code || ' long;' || c_new_line;
515 elsif l_datatype(l_loop) = c_dtype_date then
516 l_declare_code := l_declare_code || ' date;' || c_new_line;
517 elsif l_datatype(l_loop) = c_dtype_boolean then
518 l_declare_code := l_declare_code || ' boolean;' || c_new_line;
519 elsif l_datatype(l_loop) = c_dtype_undefined then
520 -- Unexpected need to raise error;
521 null;
522 end if;
523 end if;
524 end if; -- instr(l_declare_code ...
525 l_call_code := l_call_code || l_argument_name(l_loop) || ' => ';
526 l_call_code := l_call_code || l_var_name || c_new_line;
527 else
528 -- Unexpected need to raise error.
529 null;
530 end if;
531 l_pre_overload := l_overload(l_loop);
532 l_loop := l_loop + 1;
533 -- hr_utility.set_location(l_proc, 180);
534 end loop; -- end of while loop
535
536 -- Following IF statement may be executed when there is
537 -- more than one overload for the same API
538 if l_loop > 1 then
539 -- There must have been at least one parameter in the list. End the
540 -- parameter list with a closing bracket. The bracket should not be
541 -- included when there are zero parameters.
542 l_call_code := l_call_code || ');' || c_new_line;
543 end if;
544 exception
545 when no_data_found then
546 -- Trap the PL/SQL no_data_found exception. Know we have already
547 -- read the details of the last parameter from the tables.
548 if l_loop > 1 then
549 -- There must have been at least one parameter in the list. End the
550 -- parameter list with a closing bracket. The bracket should not be
551 -- included when there are zero parameters.
552 l_call_code := l_call_code || ');' || c_new_line;
553 end if;
554 -- hr_utility.set_location(l_proc, 190);
555 end;
556 end if; -- if not l_describe_error
557 hr_utility.set_location(l_proc, 200);
558 end loop;
559 --
560 -- Once only end PL/SQL block
561 --
562 l_call_code := l_call_code || 'end;' || c_new_line;
563 hr_utility.set_location(l_proc, 210);
564 --
568 end if;
565 for l_print IN 0..length(l_declare_code) loop
566 if mod(l_print, 200) = 0 then
567 hr_utility.trace(substr(l_declare_code, l_print, 200));
569 end loop;
570 for l_print IN 0..length(l_call_code) loop
571 if mod(l_print, 200) = 0 then
572 hr_utility.trace(substr(l_call_code, l_print, 200));
573 end if;
574 end loop;
575 -- l_print_length := 1;
576 -- while l_print_length < length(l_declare_code) loop
577 -- hr_utility.trace(substr(l_declare_code, l_print_length, l_print_length + 200));
578 -- l_print_length := l_print_length + 200;
579 -- end loop;
580 -- l_print_length := 1;
581 -- while l_print_length < length(l_call_code) loop
582 -- hr_utility.trace(substr(l_call_code, l_print_length, l_print_length + 200));
583 -- l_print_length := l_print_length + 200;
584 -- end loop;
585 --
586 -- Use dynamic SQL to perform the actual API call
587 --
588
589 execute_source(l_declare_code || l_call_code);
590 end if; -- not xmldom.isnull
591 hr_utility.set_location(' Leaving:'||l_proc, 220);
592 end call_plsql_api;
593
594 end az_plsql_xml;
[Home] [Help]