1 Package Body IBY_NETUTILS_PVT AS
2 /* $Header: ibynutlb.pls 120.8 2011/05/12 12:58:25 lmallick 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_postbody VARCHAR2(30000);
561
562 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.post_request';
563 BEGIN
564 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
565
566 IF (SUBSTR(p_url,1,6)='https:') THEN
567 iby_debug_pub.add('SSL url; setting wallet',
568 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
569
570 iby_utility_pvt.get_property
571 (iby_security_pkg.C_SHARED_WALLET_LOC_PROP_NAME,l_walletpath);
572 l_walletpath := iby_netutils_pvt.path_to_url(l_walletpath);
573 utl_http.set_wallet(l_walletpath,NULL);
574 END IF;
575 set_proxy(p_url);
576
577 iby_debug_pub.add('starting req',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
578 -- [lmallick] : Bug# 12547109
579 -- pass the session_id to the ECServlet to create a more meaninful
580 -- WebAppsContext
581 l_postbody := p_postbody||'&' || 'pSessionId=' || icx_sec.g_session_id;
582
583 l_httpreq := utl_http.begin_request(p_url,'POST',null);
584
585 iby_debug_pub.add('set headers',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
586 utl_http.set_header(l_httpreq,'Accept','text/plain');
587 utl_http.set_header(l_httpreq,'Content-type',
588 'application/x-www-form-urlencoded');
589 utl_http.set_header(l_httpreq,'Content-length',
590 TO_CHAR(length(l_postbody)));
591
592
593 iby_debug_pub.add('writing body',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
594
595 utl_http.write_line(l_httpreq,l_postbody);
596 l_sent_req := true;
597 iby_debug_pub.add('reading resp',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
598
599 l_httpresp := UTL_HTTP.get_response(l_httpreq);
600 l_got_resp :=true;
601 iby_debug_pub.add('resp status code:=' || l_httpresp.status_code,
602 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
603
604 BEGIN
605 LOOP
606 utl_http.read_text(l_httpresp,l_line,4000);
607 x_htmldoc := x_htmldoc || l_line;
608 END LOOP;
609 EXCEPTION
610 WHEN utl_http.end_of_body THEN
611 NULL; -- end of input
612 END;
613
614 UTL_HTTP.end_response(l_httpresp);
615
616 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
617 EXCEPTION
618 WHEN OTHERS THEN
619 iby_debug_pub.add('err code=' || utl_http.get_detailed_sqlcode,
620 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
621 iby_debug_pub.add('err msg=' || SUBSTR(utl_http.get_detailed_sqlerrm,1,150),
622 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
623
624 IF (l_got_resp) THEN
625 UTL_HTTP.end_response(l_httpresp);
626 iby_debug_pub.add('close resp',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
627 iby_debug_pub.add('resp status code:=' || l_httpresp.status_code,
628 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
629 ELSIF (l_sent_req) THEN
630 UTL_HTTP.end_request(l_httpreq);
631 iby_debug_pub.add('close req',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
632 END IF;
633 RAISE;
634 END;
635
636 --------------------------------------------------------------------------------------------
637 /* UTILITY PROCEDURE #2: CHECK_MANDATORY
638 Procedure to take in given URL string: p_url,
639 name-value pair strings: p_name, p_value
640 Check if p_value is NOT NULL. If not NULL, then append p_name=p_value to p_url.
641 If p_value is NULL, then an exception is raised and passed to the
642 calling program.
643 NOTE: This procedure checks only for MANDATORY INPUT parameters.
644 Decision on which should be mandatory is decided by the business logic.
645
646 NLS ARGS (used to encode the parameters that go into the URL):
647
648 p_local_nls - the NLS value of the local system (as pulled
649 from DB)
650 p_remote_nls - the NLS value for the remote system
651
652 */
653 --------------------------------------------------------------------------------------------
654 PROCEDURE check_mandatory (p_name IN VARCHAR2,
655 p_value IN VARCHAR2,
656 p_url IN OUT NOCOPY VARCHAR2,
657 p_local_nls IN VARCHAR2 DEFAULT NULL,
658 p_remote_nls IN VARCHAR2 DEFAULT NULL
659 ) IS
660 l_url VARCHAR2(2000) := p_url;
661 BEGIN
662 /* Logic:
663 1. Check if value is null. if null, then raise exception to pass to ECApp;
664 3. If not null, then append to URL.
665 */
666
667 IF (p_value is NULL) THEN
668 --Note: Reused an existing IBY message and token.
669 FND_MESSAGE.SET_NAME('IBY', 'IBY_0004');
670 FND_MESSAGE.SET_TOKEN('FIELD', p_name);
671 FND_MSG_PUB.Add;
672
673 -- jleybovi [10/24/00]
674 --
675 -- should return an expected error exception here as missing input is
676 -- considered a "normal" error
677 --
678 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
679 RAISE FND_API.G_EXC_ERROR;
680 ELSE
681 --Append this <name>=<value> to the input URL
682 p_url := p_url||p_name||'='||escape_url_chars(p_value,p_local_nls,p_remote_nls)||'&';
683 END IF;
684
685 -- ??? who installed the exception catch below???
686 -- keep it for the time being as it allows a better error message, code
687 -- to be returned than by aborting within PL/SQL
688 -- [jlebovi 11/29/2001]
689 EXCEPTION
690
691 WHEN OTHERS THEN
692 p_url := l_url;
693
694 END check_mandatory;
695 --------------------------------------------------------------------------------------------
696 /* UTILITY PROCEDURE #3: CHECK_OPTIONAL
697 Procedure to take in given URL string: p_url,
698 name-value pair strings: p_name, p_value
699 Check if p_value is NOT NULL, If NOT NULL, append p_name=p_value to p_url.
700 Otherwise, if p_value is NULL, then p_url is unchanged.
701 NOTE: This procedure checks only for OPTIONAL INPUT parameters and does not
702 validate MANDATORY INPUT parameters.
703
704 NLS ARGS (used to encode the parameters that go into the URL):
705
706 p_local_nls - the NLS value of the local system (as pulled
707 from DB)
708 p_remote_nls - the NLS value for the remote system
709
710 */
711 --------------------------------------------------------------------------------------------
712 PROCEDURE check_optional (p_name IN VARCHAR2,
713 p_value IN VARCHAR2,
714 p_url IN OUT NOCOPY VARCHAR2,
715 p_local_nls IN VARCHAR2 DEFAULT NULL,
716 p_remote_nls IN VARCHAR2 DEFAULT NULL
717 ) IS
718 l_url VARCHAR2(2000) := p_url;
719 BEGIN
720
721 /* Logic:
722 1. check value if null.
723 if null then don't do anything.
724 2. If not null, then append to URL.
725 */
726
727 IF (p_value IS NULL) THEN
728 p_url := l_url;
729 ELSE
730 --Append this <name>=<value> to the input URL
731 p_url := p_url||p_name||'='||escape_url_chars(p_value,p_local_nls,p_remote_nls)||'&';
732 END IF;
733
734 EXCEPTION
735 WHEN OTHERS THEN
736 p_url := l_url;
737
738 END check_optional;
739
740
741 FUNCTION path_to_url(p_path IN VARCHAR2) RETURN VARCHAR2
742 IS
743 BEGIN
744 --
745 -- URLs have '/' as the path separator, regardless of
746 -- platform
747 --
748 RETURN iby_netutils_pvt.G_FILE_PROTOCOL
749 || iby_netutils_pvt.G_NET_PATH_SEP
750 || replace(p_path,'\',iby_netutils_pvt.G_NET_PATH_SEP);
751 END path_to_url;
752
753 END IBY_NETUTILS_PVT;