The Oracle database is possible with the package UTL_HTTP UTL_RAW and send/receive queries from a Web server. On a small example demonstrates how to use these packages. (The examples use the package UTL_RAW on the ground that some Web sites, perhaps in combination with the version of Oracle database, Oracle generates EXCEPTION if you use a function READ_TEXT, sometimes generate a coredump).

[Česká verze článku – Balíky UTL_HTTP, UTL_RAW a jejich využití pro metodu HTTP GET]

Easy to use data tightening (demonstrated by the GET query to the server martin-mares.com)

DECLARE
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_buffer_size    NUMBER(10) := 512;
    l_line_size      NUMBER(10) := 70;
    l_lines_count    NUMBER(10) := 10;
    l_string_request VARCHAR2(512);
    l_line           VARCHAR2(128);
    l_raw_data       RAW(512);
    l_clob_response  CLOB;

BEGIN
    UTL_HTTP.set_transfer_timeout(60);
    l_http_request := UTL_HTTP.begin_request(url => 'https://martin-mares.com', method => 'GET', http_version => 'HTTP/1.1');
    UTL_HTTP.set_header(r => l_http_request, name => 'User-Agent', value => 'Mozilla/4.0');
    UTL_HTTP.set_header(l_http_request, 'Host', 'martin-mares.com');
    l_http_response := UTL_HTTP.get_response(l_http_request);
    DBMS_OUTPUT.put_line('Response> status_code: "' || l_http_response.status_code || '"');
    DBMS_OUTPUT.put_line('Response> reason_phrase: "' ||l_http_response.reason_phrase || '"');
    DBMS_OUTPUT.put_line('Response> http_version: "' ||l_http_response.http_version || '"');

    BEGIN

        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_response, l_raw_data, l_buffer_size);
            l_clob_response := l_clob_response || UTL_RAW.cast_to_varchar2(l_raw_data);
        END LOOP response_loop;

        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(l_http_response);
    END;
    DBMS_OUTPUT.put_line('Response> length: "' || LENGTH(l_clob_response) || '"');
    DBMS_OUTPUT.put_line(CHR(10) || '=== Print first ' || l_lines_count || ' lines of HTTP response... ===' || CHR(10) || CHR(10));

    <<print_response>>
    FOR i IN 0..CEIL(LENGTH(l_clob_response) / l_line_size) - 1 LOOP
        l_line := SUBSTR(l_clob_response, i * l_line_size + 1, l_line_size);
        DBMS_OUTPUT.put_line('[' || LPAD(i, 2, '0') || ']: ' || SUBSTR(TRIM(l_line),1,50) || '...');
        EXIT WHEN i > l_lines_count - 1;
    END LOOP print_response;

    IF l_http_request.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_request);
    END IF;

    IF l_http_response.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_response);
    END IF;

END;
/

The output of this call (when “set serveroutput on” in SQL*Plus) will:

DBMS Output (Session: [1] SCOTT@TEST_DB.WORLD at: 20.08.2010 00:43:57):
----------------------------------------------------------------------------
Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "28003"

=== Print first 10 lines of HTTP response... ===

Response> status_code: "200"
Response> reason_phrase: "OK"
Response> http_version: "HTTP/1.1"
Response> length: "28003"

=== Print first 10 lines of HTTP response... ===

[00]: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Trans...
[01]: ://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt...
[02]: http://www.w3.org/1999/xhtml" dir="ltr" lang="cs-C...
[03]: head profile="http://gmpg.org/xfn/11">
	<title>Ma...
[04]: itle>
	<meta http-equiv="content-type" content="t...
[05]: F-8" />
	<link rel="stylesheet" type="text/css" h...
[06]: com/wp-content/themes/the-erudite/css/erudite.css"...
[07]: 6]> <link rel="stylesheet" href="http://martin-mares.co...
[08]: /the-erudite/css/ie6.css" type="text/css">
	<sty...
[09]: edia="screen">
		.hr {behavior: url(https://since7...
[10]: mes/the-erudite/library/iepngfix.htc); }
	</style...

Next time we’ll look at how to consume data via HTTP POST from a WebService Weather (CDYNE)

Post to Twitter

Leave a comment

Your email address will not be published. Required fields are marked *