PL/SQL sample for HTTP access

PLSQL_WWW_GET_SAMPLE.sql

/*
    PL/SQL sample for HTTP access (Oracle 11g R2)

1. Execute /u01/app/oracle/product/11.2.0/xe/rdbms/admin/utlhttp.sql to use UTL_HTTP package
   Run the following command in shell in the DB server
    $ cd /u01/app/oracle/product/11.2.0/xe/rdbms/admin/
    $ sqlplus SYS/passwd@localhost:1521/XE AS SYSDBA @utlhttp.sql

2. Grant the connect and resolve privileges for all hosts to the user 'SCOTT'
   Run the following commands in SQL*Plus

    BEGIN
        DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('www.xml', 'WWW ACL', 'SCOTT', TRUE, 'connect');
        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('www.xml', 'SCOTT', TRUE, 'resolve');

        -- All
        DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('www.xml', '*');
    END;

  See:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm
    http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html
    http://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_networkacl_adm.htm (ja)

3. Execute this script
    $ sqlplus SCOTT/TIGER@localhost:1521/XE @PLSQL_WWW_GET_SAMPLE.sql
*/
CREATE TABLE WWW_DATA (num NUMBER, dat CLOB)
/
CREATE OR REPLACE PROCEDURE WWW_GET(url VARCHAR2)
IS
    request UTL_HTTP.REQ;
    response UTL_HTTP.RESP;
    n NUMBER;
    buff VARCHAR2(4000);
    clob_buff CLOB;
BEGIN
    UTL_HTTP.SET_RESPONSE_ERROR_CHECK(FALSE);
    request := UTL_HTTP.BEGIN_REQUEST(url, 'GET');
    UTL_HTTP.SET_HEADER(request, 'User-Agent', 'Mozilla/4.0');
    response := UTL_HTTP.GET_RESPONSE(request);
    DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || response.status_code);

    IF response.status_code = 200 THEN
        BEGIN
            clob_buff := EMPTY_CLOB;
            LOOP
                UTL_HTTP.READ_TEXT(response, buff, LENGTH(buff));
		clob_buff := clob_buff || buff;
            END LOOP;
	    UTL_HTTP.END_RESPONSE(response);
	EXCEPTION
	    WHEN UTL_HTTP.END_OF_BODY THEN
                UTL_HTTP.END_RESPONSE(response);
	    WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
                DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
                UTL_HTTP.END_RESPONSE(response);
        END;

	SELECT COUNT(*) + 1 INTO n FROM WWW_DATA;
        INSERT INTO WWW_DATA VALUES (n, clob_buff);
        COMMIT;
    ELSE
        DBMS_OUTPUT.PUT_LINE('ERROR');
        UTL_HTTP.END_RESPONSE(response);
    END IF;
  
END;
/
SHOW ERRORS
/
-- Get Twitter public timeline (XML)
EXEC WWW_GET('http://api.twitter.com/1/statuses/public_timeline.xml')
/
-- Get Twitter public timeline (JSON)
EXEC WWW_GET('http://api.twitter.com/1/statuses/public_timeline.json')
/
QUIT;