1 Package Body IBY_NETUTILS_PVT AS
2 /* $Header: ibynutlb.pls 120.7.12010000.1 2008/07/28 05:41:20 appldev ship $ */
3
4 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_NETUTILS_PVT';
5
6 --
7 PROCEDURE set_proxy(p_url IN VARCHAR2)
8 IS
9 l_proxy VARCHAR2(500) := NULL;
10 l_noproxy VARCHAR2(500);
11
12 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.use_proxy';
13 BEGIN
14
15 iby_utility_pvt.get_property(G_PROFILE_HTTP_PROXY,l_proxy);
16
17 IF (NOT (TRIM(l_proxy) IS NULL)) THEN
18 iby_utility_pvt.get_property(G_PROFILE_NO_PROXY,l_noproxy);
19 l_noproxy := TRIM(l_noproxy);
20
21 UTL_HTTP.set_proxy(l_proxy,l_noproxy);
22 END IF;
23 END set_proxy;
24
25 --
26 --
27 FUNCTION decode_url_chars (p_string IN VARCHAR2,
28 p_local_nls IN VARCHAR2 DEFAULT NULL,
29 p_remote_nls IN VARCHAR2 DEFAULT NULL)
30 RETURN VARCHAR2
31 IS
32 l_raw RAW(32767);
33 l_char VARCHAR2(4);
34 l_hex VARCHAR2(8);
35 l_len INTEGER;
36 i INTEGER := 1;
37 BEGIN
38
39 IF (p_string IS NULL) THEN
40 return p_string;
41 END IF;
42
43 l_len := length(p_string);
44
45 WHILE i <= l_len
46 LOOP
47 l_char := substr(p_string, i, 1);
48 IF l_char = '+' THEN
49 /* convert to a hex number of space characters */
50 l_hex := '20';
51 i := i + 1;
52 ELSIF l_char = '%' THEN
53 /* process hex encoded characters. just remove a % character */
54 l_hex := substr(p_string, i+1, 2);
55 i := i + 3;
56 ELSE
57 /* convert to hex numbers for all other characters */
58 l_hex := to_char(ascii(l_char), 'FM0X');
59 i := i + 1;
60 END IF;
61 /* convert a hex number to a raw datatype */
62 l_raw := l_raw || hextoraw(l_hex);
63 END LOOP;
64
65 /*
66 * convert a raw data from the source charset to the database charset,
67 * then cast it to a varchar2 string.
68 */
69 RETURN utl_raw.cast_to_varchar2(
70 utl_raw.convert(l_raw, p_local_nls, p_remote_nls));
71 EXCEPTION
72 WHEN OTHERS THEN
73 RETURN p_string;
74 END decode_url_chars;
75
76 --
77 --
78 FUNCTION escape_url_chars (p_string IN VARCHAR2,
79 p_local_nls IN VARCHAR2 DEFAULT NULL,
80 p_remote_nls IN VARCHAR2 DEFAULT NULL)
81 RETURN VARCHAR2
82 IS
83 l_local_charset VARCHAR2(200);
84 l_remote_charset VARCHAR2(200);
85
86 l_tmp VARCHAR2(32767);
87
88 -- must be large enough to hold 4 bytes multibyte char
89 l_onechar VARCHAR2(4);
90
91 -- buffer to hold converted number 2*l_onechar+1 for leading 0
92 l_str VARCHAR2(48);
93 l_byte_len INTEGER;
94
95 -- whether the local/remote machines have different character
96 -- sets
97 l_do_convert BOOLEAN := false;
98
99 -- characters which should not be touched
100 c_unreserved constant varchar2(72) :=
101 '-_.!~*''()ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
102 BEGIN
103
104 l_tmp:='';
105
106 IF ( p_string IS NULL ) THEN
107 return NULL;
108 END IF;
109
110 l_local_charset := iby_utility_pvt.get_nls_charset(p_local_nls);
111 l_remote_charset := iby_utility_pvt.get_nls_charset(p_remote_nls);
112
113 IF (l_remote_charset IS NULL) OR (l_local_charset IS NULL) THEN
114 l_do_convert := false;
115 ELSE
116 l_do_convert := (l_local_charset <> l_remote_charset );
117 END IF;
118
119 FOR i in 1 .. length(p_string) LOOP
120 l_onechar := substr(p_string,i,1);
121
122 IF instr(c_unreserved, l_onechar) > 0 THEN
123 /* if this character is excluded from encoding */
124 l_tmp := l_tmp || l_onechar;
125 ELSIF l_onechar = ' ' THEN
126 /* spaces are encoded using the plus "+" sign */
127 l_tmp := l_tmp || '+';
128 ELSE
129 IF (l_do_convert) THEN
130 /*
131 * This code to be called ONLY in case when client and server
132 * charsets are different. The performance of this code is
133 * significantly slower than "else" portion of this statement.
134 * But in this case it is guarenteed to be working in
135 * any configuration where the byte-length of the charset
136 * is different between client and server (e.g. UTF-8 to SJIS).
137 */
138
139 /*
140 * utl_raw.convert only takes a qualified NLS_LANG value in
141 * <langauge>_<territory>.<charset> format for target and
142 * source charset parameters. Need to use l_client_nls_lang
143 * and g_db_nls_lang here.
144 */
145 l_str := utl_raw.convert(utl_raw.cast_to_raw(l_onechar),
146 p_remote_nls,
147 p_local_nls);
148 l_byte_len := length(l_str);
149 IF l_byte_len = 2 THEN
150 l_tmp := l_tmp
151 || '%' || l_str;
152 ELSIF l_byte_len = 4 THEN
153 l_tmp := l_tmp
154 || '%' || substr(l_str,1,2)
155 || '%' || substr(l_str,3,2);
156 ELSIF l_byte_len = 6 THEN
157 l_tmp := l_tmp
158 || '%' || substr(l_str,1,2)
159 || '%' || substr(l_str,3,2)
160 || '%' || substr(l_str,5,2);
161 ELSIF l_byte_len = 8 THEN
162 l_tmp := l_tmp
163 || '%' || substr(l_str,1,2)
164 || '%' || substr(l_str,3,2)
165 || '%' || substr(l_str,5,2)
166 || '%' || substr(l_str,7,2);
167 ELSE /* maximum precision exceeded */
168 raise PROGRAM_ERROR;
169 END IF;
170 ELSE
171 /*
172 * This is the "simple" encoding when no charset translation
173 * is needed, so it is relatively fast.
174 */
175 l_byte_len := lengthb(l_onechar);
176 IF l_byte_len = 1 THEN
177 l_tmp := l_tmp || '%' ||
178 substr(to_char(ascii(l_onechar),'FM0X'),1,2);
179 ELSIF l_byte_len = 2 THEN
180 l_str := to_char(ascii(l_onechar),'FM0XXX');
181 l_tmp := l_tmp
182 || '%' || substr(l_str,1,2)
183 || '%' || substr(l_str,3,2);
184 ELSIF l_byte_len = 3 THEN
185 l_str := to_char(ascii(l_onechar),'FM0XXXXX');
186 l_tmp := l_tmp
187 || '%' || substr(l_str,1,2)
188 || '%' || substr(l_str,3,2)
189 || '%' || substr(l_str,5,2);
190 ELSIF l_byte_len = 4 THEN
191 l_str := to_char(ascii(l_onechar),'FM0XXXXXXX');
192 l_tmp := l_tmp
193 || '%' || substr(l_str,1,2)
194 || '%' || substr(l_str,3,2)
195 || '%' || substr(l_str,5,2)
196 || '%' || substr(l_str,7,2);
197 ELSE /* maximum precision exceeded */
198 raise PROGRAM_ERROR;
199 END IF;
200 END IF;
201 END IF;
202 END LOOP;
203
204 RETURN l_tmp;
205
206 EXCEPTION
207 WHEN others THEN
208 RAISE encoding_error;
209 END escape_url_chars;
210
211 ----------------------------------------------------------------------------------
212 /* UTILITY PROCEDURE #0.1: GET_LOCAL_NLS
213 Function returns the local (i.e. database) characterset.
214
215 */
216 ----------------------------------------------------------------------------------
217 FUNCTION get_local_nls
218 RETURN VARCHAR2
219 IS
220 BEGIN
221 return userenv('LANGUAGE');
222 EXCEPTION WHEN others THEN
223 return NULL;
224
225 END get_local_nls;
226
227
228
229 ------------------------------------------------------------------------------------
230 /* UTILITY PROCEDURE #1: UNPACK_RESULTS_URL
231 PARSER Procedure to take in given l_string in html file format,
232 parse l_string, and store the Name-Value pairs in l_names and l_values.
233 For example, if OapfPrice Name-Value pairs exist in l_string, it would be
234 stored as l_names(i) := 'OapfPrice' and l_values(i) := '17.00'.
235
236 NOTE: This procedure logic is exactly similar to the iPayment 3i version
237 of procedure with minor enhancements and bug fixes.
238 */
239 ------------------------------------------------------------------------------------
240 PROCEDURE unpack_results_url(p_string IN VARCHAR2,
241 x_names OUT NOCOPY v240_tbl_type,
242 x_values OUT NOCOPY v240_tbl_type,
243 x_status OUT NOCOPY NUMBER,
244 x_errcode OUT NOCOPY NUMBER,
245 x_errmessage OUT NOCOPY VARCHAR2
246 ) IS
247
248 l_length NUMBER(15) := LENGTH(p_string) + 1;
249 l_count NUMBER(15) := 0;
250 l_index NUMBER(15) := 1;
251 l_char VARCHAR2(1) := '';
252 l_word VARCHAR2(2400) := '';
253 l_name BOOLEAN := TRUE;
254 l_local_nls VARCHAR2(200);
255 l_remote_nls VARCHAR2(200);
256 BEGIN
257
258 iby_debug_pub.add('In unpack_results_url');
259
260 -- Initialize status, errcode, errmessage to Success.
261 x_status := 0;
262 x_errcode := 0;
263 x_errmessage := 'Success';
264
265 l_local_nls := get_local_nls();
266
267 -- verify what HTTP response format is returned by the server
268 -- NOTE: Since ECServlet is not supposed to return in this format,
269 -- this condition should not be encountered.
270 l_count := instr(p_string,'</H2>Oapf');
271 IF l_count > 0 THEN
272 l_count := l_count + 5;
273 ELSE return;
274 END IF;
275
276 --Fixing Bug from OM: 1104438
277 --Suggested improvement to this: Search for the first alphanumeric
278 --character [a-zA-Z0-9] encountered in this string.set l_count to that position.
279 --l_count := INSTR(p_string, 'Oapf');
280 --End of Bug Fix 1104438
281
282 WHILE l_count < l_length LOOP
283 IF (l_name) AND (substr(p_string,l_count,1) = ':') THEN
284 x_names(l_index) := substr( (ltrim(rtrim(l_word))), 1, 240 );
285 --dbms_output.put_line('Name : ' ||x_names(l_index) );
286 l_name := FALSE;
287 l_word := '';
288 l_count := l_count + 1;
289 ELSIF (l_name) THEN
290 l_char := substr(p_string,l_count,1);
291 l_word := l_word||l_char;
292 l_count := l_count + 1;
293 ELSIF upper(substr(p_string,l_count,4)) = '<BR>' THEN
294 x_values(l_index) := substr( (ltrim(rtrim(l_word))), 1, 240 );
295 --dbms_output.put_line('Value : ' ||x_values(l_index) );
296 -- remember the NLS Lang parameter for below decoding
297 IF (x_names(l_index) = 'OapfNlsLang') THEN
298 l_remote_nls := x_values(l_index);
299 END IF;
300
301 l_name := TRUE;
302 l_word := '';
303 l_index := l_index + 1;
304 l_count := l_count + 4;
305 ELSE
306 l_char := substr(p_string,l_count,1);
307 l_word := l_word||l_char;
308 l_count := l_count + 1;
309 END IF;
310
311 /*--Note: Can Add this to extra ensure that
312 --additional white spaces get trimmed.
313 x_names(l_count) := LTRIM(RTRIM(x_names(l_count) ));
314 x_values(l_count) := LTRIM(RTRIM(x_values(l_count) )); */
315
316 END LOOP;
317
318 -- do URL decoding if on the output values if possible
319 --
320
321 --dbms_output.put_line('unpack::local nls: ' || l_local_nls);
322 --dbms_output.put_line('unpack::remote nls: ' || l_remote_nls);
323
324 /*
325 IF ((l_remote_nls IS NOT NULL) AND (l_local_nls IS NOT NULL)) THEN
326 FOR i in 1..x_values.COUNT LOOP
327 x_values(i) := decode_url_chars(x_values(i),l_local_nls,l_remote_nls);
328 END LOOP;
329 END IF;
330 */
331
332 iby_debug_pub.add('Exit unpack_results_url');
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 /* Return a status of -1 to the calling API to indicate
337 errors in unpacking html body results. */
338 --dbms_output.put_line('error in unpacking procedure');
339 x_status := -1;
340 x_errcode := to_char(SQLCODE);
341 x_errmessage := SQLERRM;
342 END unpack_results_url;
343
344
345 --------------------------------------------------------------------------------------------
346 /* UTILITY PROCEDURE #6: GET_BASEURL
347 Procedure to retrieve the iPayment ECAPP BASE URL
348 */
349 --------------------------------------------------------------------------------------------
350 PROCEDURE get_baseurl(x_baseurl OUT NOCOPY VARCHAR2)
351 IS
352 -- Local variable to hold the property name for the URL.
353 p_temp_var VARCHAR2(2);
354
355 BEGIN
356
357 iby_debug_pub.add('In get_baseurl');
358 iby_utility_pvt.get_property(iby_payment_adapter_pub.C_ECAPP_URL_PROP_NAME,x_baseurl);
359 --dbms_output.put_line('x_return_status = '|| x_return_status);
360
361 --Raising Exception to handle errors if value is missing
362 IF ((x_baseurl IS NULL) OR (trim(x_baseurl) = '')) THEN
363 FND_MESSAGE.SET_NAME('IBY', 'IBY_204406');
364 FND_MSG_PUB.Add;
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367
368 --appending '?' if not already present in the url
369 p_temp_var := SUBSTR(x_baseurl, -1);
370
371 IF( p_temp_var <> '?' ) THEN
372 x_baseurl := x_baseurl || '?';
373 END IF;
374
375 iby_debug_pub.add('base url=' || x_baseurl);
376
377 iby_debug_pub.add('Exit get_baseurl');
378
379 END get_baseurl;
380
381
382 --------------------------------------------------------------------------------------------
383 /* UTILITY PROCEDURE #7: POST_REQUEST Handles CLOB POST message
384 Procedure to call HTTP_UTIL.POST_REQUEST and handle exceptions thrown by it
385 */
386 --------------------------------------------------------------------------------------------
387 PROCEDURE post_request(p_url IN VARCHAR2,
388 p_postbody IN CLOB,
389 x_names OUT NOCOPY v240_tbl_type,
390 x_values OUT NOCOPY v240_tbl_type,
391 x_status OUT NOCOPY NUMBER,
392 x_errcode OUT NOCOPY NUMBER,
393 x_errmessage OUT NOCOPY VARCHAR2
394 ) IS
395 l_get_baseurl VARCHAR2(2000) ;
396 --The following 3 variables are meant for output of
397 --get_baseurl procedure.
398 l_status_url VARCHAR2(2000);
399 l_msg_count_url NUMBER := 0;
400 l_msg_data_url VARCHAR2(2000);
401
402 l_url VARCHAR2(4000);
403
404 l_position NUMBER := 0;
405 l_host VARCHAR2(4000);
406 l_port VARCHAR2(80) := NULL;
407 l_post_info VARCHAR2(2000);
408 l_postbody_length NUMBER;
409 l_buff VARCHAR2(32767);
410 l_pos NUMBER := 1;
411 l_len NUMBER;
412 l_html VARCHAR2(32767);
413
414 l_conn UTL_TCP.CONNECTION; -- TCP/IP connection to the Web server
415 l_ret_val PLS_INTEGER;
416 l_content_len NUMBER := 0;
417 i NUMBER := 1;
418
419 BEGIN
420
421
422 --l_get_baseurl := 'http://incq186sc.idc.oracle.com/servlets/snoop';
423 -- Construct the full URL to send to the ECServlet.
424 l_url := p_url;
425
426 l_position := INSTR(lower(l_url),lower('http://'));
427 --remove the 'http://'
428 IF (l_position > 0) THEN
429 l_url := SUBSTR(l_url,8);
430 ELSE
431 l_position := INSTR(lower(l_url),lower('https://'));
432 --remove the 'https://'
433 IF (l_position > 0) THEN
434 l_url := SUBSTR(l_url,9);
435 END IF;
436 END IF;
437
438 -- get the host address
439 l_position := INSTR(l_url,':');
440 IF (l_position > 0) THEN
441 l_host := SUBSTR(l_url,1,l_position-1);
442 --remove the 'the host + :' from the URL
443 l_url := SUBSTR(l_url,l_position+1);
444 ELSE
445 l_position := INSTR(l_url,'/');
446 IF (l_position > 0) THEN
447 l_host := SUBSTR(l_url,1,l_position-1);
448 --remove the 'the host' from the URL
449 l_url := SUBSTR(l_url,l_position);
450 END IF;
451 END IF;
452
453 -- get the port number
454 l_position := INSTR(l_url,'/');
455 IF (l_position > 0) THEN
456 l_port := SUBSTR(l_url,1,l_position-1);
457 END IF;
458 IF (l_port is NULL) THEN
459 l_port := '80';
460 END IF;
461
462 --remove the port number from the URL
463 l_post_info := SUBSTR(l_url,l_position);
464 l_post_info := 'POST ' || l_post_info || ' HTTP/1.0';
465
466 --dbms_output.put_line('l_post_info = ' || l_post_info);
467
468 --dbms_output.put_line('l_host = ' || l_host);
469 --dbms_output.put_line('l_port = ' || l_port);
470 --dbms_output.put_line('POST BoDY Length = ' || DBMS_LOB.GETLENGTH(p_postbody));
471
472 l_conn := utl_tcp.open_connection(remote_host => l_host,
473 remote_port => l_port);
474
475 l_ret_val := utl_tcp.write_line(l_conn, l_post_info);
476 l_ret_val := utl_tcp.write_line(l_conn,'Accept: text/plain');
477 l_ret_val := utl_tcp.write_line(l_conn,'Content-type: application/x-www-form-urlencoded');
478
479 -- get the length of the clob
480 l_postbody_length := DBMS_LOB.GETLENGTH(p_postbody);
481 --l_content_len := 0 ;
482 l_content_len := l_postbody_length;
483 l_ret_val := utl_tcp.write_line(l_conn,'Content-length: '||l_content_len);
484 l_ret_val := utl_tcp.write_line(l_conn);
485
486
487
488 -- splitting the clob into varchar2 and posting it.
489 WHILE (l_pos <= l_postbody_length) LOOP
490 l_len := 32767;
491 DBMS_LOB.READ(p_postbody,l_len,l_pos,l_buff);
492 l_pos := l_pos + length(l_buff);
493 --dbms_output.put_line('Read :' || l_buff);
494 l_ret_val := utl_tcp.write_text(l_conn,l_buff,null);
495 END LOOP;
496
497
498 BEGIN
499 LOOP
500 l_html := substr(utl_tcp.get_line(l_conn,TRUE),1,30000);
501 l_html := LTRIM(RTRIM(l_html));
502 --dbms_output.put_line('Response length:' || length(x_htmldoc));
503 --dbms_output.put_line('Response frag : "' || l_html||'"');
504 --dbms_output.put_line('Response frag length: "' || length(l_html)||'"');
505 if (length(l_html) is null) then
506 --dbms_output.put_line('Raising Exception...');
507 raise utl_tcp.end_of_input;
508 end if;
509 --x_htmldoc := x_htmldoc || l_html;
510
511 --dbms_output.put_line('Response Param: "' || ltrim(rtrim(substr(l_html ,1,instr(l_html,':')-1)))||'"');
512 --dbms_output.put_line('Response Value: "' || ltrim(rtrim(substr(l_html ,instr(l_html,':')+1,length(l_html)+1)))||'"');
513 x_names(i) := ltrim(rtrim(substr(l_html ,1,instr(l_html,':')-1)));
514 x_values(i) := ltrim(rtrim(substr(l_html ,instr(l_html,':')+1,length(l_html)+1)));
515 i := i + 1;
516
517 END LOOP;
518 EXCEPTION
519 WHEN utl_tcp.end_of_input THEN
520 NULL; -- end of input
521 END;
522
523 --dbms_output.put_line('Final Response length :'|| length(x_htmldoc));
524 utl_tcp.close_connection(l_conn);
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 /* Return a status of -1 to the calling API to indicate
529 errors in unpacking html body results. */
530 --dbms_output.put_line('error in unpacking procedure');
531 x_status := -1;
532 x_errcode := to_char(SQLCODE);
533 x_errmessage := SQLERRM;
534 END;
535
536
537
538 --------------------------------------------------------------------------------------------
539 /* UTILITY PROCEDURE #7: POST_REQUEST
540 Procedure to call HTTP_UTIL.POST_REQUEST and handle exceptions thrown by it
541 */
542 --------------------------------------------------------------------------------------------
543
544 PROCEDURE post_request(p_url IN VARCHAR2,
545 p_postbody IN VARCHAR2,
546 x_htmldoc OUT NOCOPY VARCHAR2
547 ) IS
548 l_ret_val PLS_INTEGER;
549 l_content_len NUMBER := 0;
550
551 l_url VARCHAR2(1000);
552 l_walletpath VARCHAR2(1000);
553 l_line VARCHAR2(4000);
554
555 l_httpreq UTL_HTTP.Req;
556 l_httpresp UTL_HTTP.Resp;
557 l_sent_req BOOLEAN := false;
558 l_got_resp BOOLEAN := false;
559
560 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.post_request';
561 BEGIN
562 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
563
564 IF (SUBSTR(p_url,1,6)='https:') THEN
565 iby_debug_pub.add('SSL url; setting wallet',
566 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
567
568 iby_utility_pvt.get_property
569 (iby_security_pkg.C_SHARED_WALLET_LOC_PROP_NAME,l_walletpath);
570 l_walletpath := iby_netutils_pvt.path_to_url(l_walletpath);
571 utl_http.set_wallet(l_walletpath,NULL);
572 END IF;
573 set_proxy(p_url);
574
575 iby_debug_pub.add('starting req',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
576 l_httpreq := utl_http.begin_request(p_url,'POST',null);
577
578 iby_debug_pub.add('set headers',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
579 utl_http.set_header(l_httpreq,'Accept','text/plain');
580 utl_http.set_header(l_httpreq,'Content-type',
581 'application/x-www-form-urlencoded');
582 utl_http.set_header(l_httpreq,'Content-length',
583 TO_CHAR(length(p_postbody)));
584
585
586 iby_debug_pub.add('writing body',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
587
588 utl_http.write_line(l_httpreq,p_postbody);
589 l_sent_req := true;
590 iby_debug_pub.add('reading resp',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
591
592 l_httpresp := UTL_HTTP.get_response(l_httpreq);
593 l_got_resp :=true;
594 iby_debug_pub.add('resp status code:=' || l_httpresp.status_code,
595 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
596
597 BEGIN
598 LOOP
599 utl_http.read_text(l_httpresp,l_line,4000);
600 x_htmldoc := x_htmldoc || l_line;
601 END LOOP;
602 EXCEPTION
603 WHEN utl_http.end_of_body THEN
604 NULL; -- end of input
605 END;
606
607 UTL_HTTP.end_response(l_httpresp);
608
609 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
610 EXCEPTION
611 WHEN OTHERS THEN
612 iby_debug_pub.add('err code=' || utl_http.get_detailed_sqlcode,
613 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
614 iby_debug_pub.add('err msg=' || SUBSTR(utl_http.get_detailed_sqlerrm,1,150),
615 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
616
617 IF (l_got_resp) THEN
618 UTL_HTTP.end_response(l_httpresp);
619 iby_debug_pub.add('close resp',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
620 iby_debug_pub.add('resp status code:=' || l_httpresp.status_code,
621 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
622 ELSIF (l_sent_req) THEN
623 UTL_HTTP.end_request(l_httpreq);
624 iby_debug_pub.add('close req',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
625 END IF;
626 RAISE;
627 END;
628
629 --------------------------------------------------------------------------------------------
630 /* UTILITY PROCEDURE #2: CHECK_MANDATORY
631 Procedure to take in given URL string: p_url,
632 name-value pair strings: p_name, p_value
633 Check if p_value is NOT NULL. If not NULL, then append p_name=p_value to p_url.
634 If p_value is NULL, then an exception is raised and passed to the
635 calling program.
636 NOTE: This procedure checks only for MANDATORY INPUT parameters.
637 Decision on which should be mandatory is decided by the business logic.
638
639 NLS ARGS (used to encode the parameters that go into the URL):
640
641 p_local_nls - the NLS value of the local system (as pulled
642 from DB)
643 p_remote_nls - the NLS value for the remote system
644
645 */
646 --------------------------------------------------------------------------------------------
647 PROCEDURE check_mandatory (p_name IN VARCHAR2,
648 p_value IN VARCHAR2,
649 p_url IN OUT NOCOPY VARCHAR2,
650 p_local_nls IN VARCHAR2 DEFAULT NULL,
651 p_remote_nls IN VARCHAR2 DEFAULT NULL
652 ) IS
653 l_url VARCHAR2(2000) := p_url;
654 BEGIN
655 /* Logic:
656 1. Check if value is null. if null, then raise exception to pass to ECApp;
657 3. If not null, then append to URL.
658 */
659
660 IF (p_value is NULL) THEN
661 --Note: Reused an existing IBY message and token.
662 FND_MESSAGE.SET_NAME('IBY', 'IBY_0004');
663 FND_MESSAGE.SET_TOKEN('FIELD', p_name);
664 FND_MSG_PUB.Add;
665
666 -- jleybovi [10/24/00]
667 --
668 -- should return an expected error exception here as missing input is
669 -- considered a "normal" error
670 --
671 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
672 RAISE FND_API.G_EXC_ERROR;
673 ELSE
674 --Append this <name>=<value> to the input URL
675 p_url := p_url||p_name||'='||escape_url_chars(p_value,p_local_nls,p_remote_nls)||'&';
676 END IF;
677
678 -- ??? who installed the exception catch below???
679 -- keep it for the time being as it allows a better error message, code
680 -- to be returned than by aborting within PL/SQL
681 -- [jlebovi 11/29/2001]
682 EXCEPTION
683
684 WHEN OTHERS THEN
685 p_url := l_url;
686
687 END check_mandatory;
688 --------------------------------------------------------------------------------------------
689 /* UTILITY PROCEDURE #3: CHECK_OPTIONAL
690 Procedure to take in given URL string: p_url,
691 name-value pair strings: p_name, p_value
692 Check if p_value is NOT NULL, If NOT NULL, append p_name=p_value to p_url.
693 Otherwise, if p_value is NULL, then p_url is unchanged.
694 NOTE: This procedure checks only for OPTIONAL INPUT parameters and does not
695 validate MANDATORY INPUT parameters.
696
697 NLS ARGS (used to encode the parameters that go into the URL):
698
699 p_local_nls - the NLS value of the local system (as pulled
700 from DB)
701 p_remote_nls - the NLS value for the remote system
702
703 */
704 --------------------------------------------------------------------------------------------
705 PROCEDURE check_optional (p_name IN VARCHAR2,
706 p_value IN VARCHAR2,
707 p_url IN OUT NOCOPY VARCHAR2,
708 p_local_nls IN VARCHAR2 DEFAULT NULL,
709 p_remote_nls IN VARCHAR2 DEFAULT NULL
710 ) IS
711 l_url VARCHAR2(2000) := p_url;
712 BEGIN
713
714 /* Logic:
715 1. check value if null.
716 if null then don't do anything.
717 2. If not null, then append to URL.
718 */
719
720 IF (p_value IS NULL) THEN
721 p_url := l_url;
722 ELSE
723 --Append this <name>=<value> to the input URL
724 p_url := p_url||p_name||'='||escape_url_chars(p_value,p_local_nls,p_remote_nls)||'&';
725 END IF;
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 p_url := l_url;
730
731 END check_optional;
732
733
734 FUNCTION path_to_url(p_path IN VARCHAR2) RETURN VARCHAR2
735 IS
736 BEGIN
737 --
738 -- URLs have '/' as the path separator, regardless of
739 -- platform
740 --
741 RETURN iby_netutils_pvt.G_FILE_PROTOCOL
742 || iby_netutils_pvt.G_NET_PATH_SEP
743 || replace(p_path,'\',iby_netutils_pvt.G_NET_PATH_SEP);
744 END path_to_url;
745
746 END IBY_NETUTILS_PVT;