utl_http and begin_request failure

From: Dave <daverawding_at_attbi.com>
Date: 28 Jul 2003 11:44:50 -0700
Message-ID: <6f89a035.0307281044.1809b2c0@posting.google.com>

Hi, In Oracle Enterprise 9.0.1 on Solaris, I am getting this message when I call utl_http.begin_request with any url that is 'out there' on the internet;

Request_Failed: ORA-12545: Connect failed because target host or object does not exist

If I use an address that is on an internal network, I get:

Request_Failed: ORA-12535: TNS:operation timed out

Both errors are related to tns. How does Oracle use a stored procedure in the database to communicate with port 80 and the outside world? By the way, running telnet to port 80 to Yahoo.com (for example) works from this box.

Any documentation on utl_http takes for granted that utl_http.begin_request will be successful. Perhaps it wasn't installed correctly?

A 100% working example would be greatly appreciated so I can just plug it in and not have to worry about my coding. Here is what I have been using to try up to now:

create or replace procedure dartest1 is

   req Utl_Http.req;
   resp Utl_Http.resp;

   NAME    VARCHAR2 (255);
   VALUE   VARCHAR2 (1023);
   v_msg   VARCHAR2 (80);
   v_url   VARCHAR2 (32767) :=

'http://b2bconnectoritg.compaq.com/invoke/CPQ_Ariba_PO_cXML1_0/receiveCXML'; BEGIN
   DBMS_OUTPUT.put_line ('begin req');
   req := Utl_Http.begin_request (url => v_url, method => 'GET', http_version => utl_http.HTTP_VERSION_1_0);

   DBMS_OUTPUT.put_line ('set header');
   Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');

   resp := Utl_Http.get_response (r => req);

   DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);    DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);

   FOR i IN 1 .. Utl_Http.get_header_count (r => resp)    LOOP
      Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
      DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);    END LOOP;    BEGIN

      LOOP
         Utl_Http.read_text (r => resp, DATA => v_msg);
         DBMS_OUTPUT.put_line (v_msg);
      END LOOP;
   EXCEPTION
      WHEN Utl_Http.end_of_body
      THEN
         NULL;

   END;    Utl_Http.end_response (r => resp);
EXCEPTION
   WHEN Utl_Http.request_failed
   THEN

      DBMS_OUTPUT.put_line (
         'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm
      );

/* raised by URL http://xxx.oracle.com/ */
   WHEN Utl_Http.http_server_error
   THEN

      DBMS_OUTPUT.put_line (
         'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm
      );

/* raised by URL http://otn.oracle.com/xxx */
   WHEN Utl_Http.http_client_error
   THEN

      DBMS_OUTPUT.put_line (
         'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm
      );

/* code for all the other defined exceptions you can recover from
*/

   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
end dartest1;

thanks, Dave
dave_at_cybergrants.com Received on Mon Jul 28 2003 - 13:44:50 CDT