utl_http How do I read pages from the net with oracle?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: utl_http How do I read pages from the net with oracle?

  1. #1
    Join Date
    Mar 2002
    Posts
    2
    I have just installed Oracle 9i Enterprise edition on my pc. I am running win 2000 prof edition. I would like to use my database to pull down http from the net. I have seen sample code like
    select utl_http.request('http://www.oracle.com/')
    from dual;
    UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/')

    this seems to work but if I sub a different URL I get this series of errors:
    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1556
    ORA-12545: Connect failed because target host or object does not exist
    ORA-06512: at line 1

    I get the same types of errors when I use the classic (show_url) sample code. I do not have AIS, only the default http server that is installed along with the 9i database. I have no idea what kind of configuring I need to do in order to make the database be able to "see" the net.

    Here is a copy of the show_url code:
    CREATE OR REPLACE PROCEDURE show_url
    (
    url IN VARCHAR2,
    username IN VARCHAR2 DEFAULT NULL,
    password IN VARCHAR2 DEFAULT NULL
    ) AS
    req utl_http.req;
    resp utl_http.resp;
    name VARCHAR2(256);
    value VARCHAR2(1024);
    data VARCHAR2(255);
    my_scheme VARCHAR2(256);
    my_realm VARCHAR2(256);
    my_proxy BOOLEAN;
    BEGIN
    -- When going through a firewall, pass requests through this host.
    -- Specify sites inside the firewall that don't need the proxy host.
    utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');

    -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
    -- rather than just returning the text of the error page.
    utl_http.set_response_error_check(FALSE);

    -- Begin retrieving this web page.
    req := utl_http.begin_request(url);

    -- Identify ourselves. Some sites serve special pages for particular browsers.
    utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

    -- Specify a user ID and password for pages that require them.
    IF (username IS NOT NULL) THEN
    utl_http.set_authentication(req, username, password);
    END IF;

    BEGIN
    -- Now start receiving the HTML text.
    resp := utl_http.get_response(req);

    -- Show the status codes and reason phrase of the response.
    dbms_output.put_line('HTTP response status code: ' || resp.status_code);
    dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);

    -- Look for client-side error and report it.
    IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN

    -- Detect whether the page is password protected, and we didn't supply
    -- the right authorization.
    IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN
    utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy);
    IF (my_proxy) THEN
    dbms_output.put_line('Web proxy server is protected.');
    dbms_output.put('Please supply the required ' || my_scheme ||
    ' authentication username/password for realm ' || my_realm ||
    ' for the proxy server.');
    ELSE
    dbms_output.put_line('Web page ' || url || ' is protected.');
    dbms_output.put('Please supplied the required ' || my_scheme ||
    ' authentication username/password for realm ' || my_realm ||
    ' for the Web page.');
    END IF;
    ELSE
    dbms_output.put_line('Check the URL.');
    END IF;

    utl_http.end_response(resp);
    RETURN;

    -- Look for server-side error and report it.
    ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN

    dbms_output.put_line('Check if the web site is up.');
    utl_http.end_response(resp);
    RETURN;

    END IF;

    -- The HTTP header lines contain information about cookies, character sets,
    -- and other data that client and server can use to customize each session.
    FOR i IN 1..utl_http.get_header_count(resp) LOOP
    utl_http.get_header(resp, i, name, value);
    dbms_output.put_line(name || ': ' || value);
    END LOOP;

    -- Keep reading lines until no more are left and an exception is raised.
    LOOP
    utl_http.read_line(resp, value);
    dbms_output.put_line(value);
    END LOOP;
    EXCEPTION
    WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
    END;

    END;
    /
    SET serveroutput ON
    -- The following URLs illustrate the use of this procedure,
    -- but these pages do not actually exist. To test, substitute
    -- URLs from your own web server.
    exec show_url('http://www.oracle.com/no-such-page.html')
    exec show_url('http://www.oracle.com/protected-page.html')
    exec show_url('http://www.oracle.com/protected-page.html', 'scott', 'tiger')


    I typically comment out the proxy server stuff because we don't use a proxy server here. What I get when I run this is like follows:

    SQL> exec show_url('http://www.google.com')
    BEGIN show_url('http://www.google.com'); END;

    *
    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1022
    ORA-12545: Connect failed because target host or object does not exist
    ORA-06512: at "SYS.SHOW_URL", line 24
    ORA-06512: at line 1

    Assuming I have done no further configuration than just completing a basic install.
    What do I need to do to make this work?
    Thanks,
    Clu

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    If you want to execute in the sqlplus you need to turn on the html option

    SQL> ? set

    To be able to pull it on the web, you would first have to start the appache server, then configure the directory and create DCD for the oracle user and then you would be able to view all your pages on the browser.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Mar 2002
    Posts
    2

    OK

    I found out the reason the first select statement was failing when I used a different Url. I was failing to include a trailing /. But I still can't seem to get the Procedure show_url to work. I feel like I just don't have something set quite right in the http server or something butI just don't know. I comment out the proxy server line... I get an object not found error, I list my http servers IP address as the proxy server... I get an object not found error. I just don't even know where to look for support on this. Any suggestions are appreciated.
    THX.
    Clu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width