PL/SQL Reddit Browsing (Crude, but effective at making me look like I'm doing work in the office)

gistfile1.sql

CREATE OR REPLACE PACKAGE SCOTT.np_reddit IS
  g_xmldata         XMLTYPE;

  PROCEDURE get_top_25 (p_subreddit VARCHAR2 default null);
  PROCEDURE get_comments (p_item NUMBER);
  PROCEDURE get_image (p_item NUMBER, p_scale NUMBER default 10, p_end VARCHAR2 default null);
END;
/




CREATE OR REPLACE PACKAGE BODY SCOTT.np_reddit IS

  PROCEDURE get_top_25 (p_subreddit VARCHAR2 default null) IS
    l_http_req        utl_http.req;
    l_http_resp       utl_http.resp;
    l_buffer_size     NUMBER(10) := 512;
    l_raw_data        RAW(512);
    l_clob_response   CLOB;
    
    CURSOR item_cursor IS
        SELECT EXTRACTVALUE(column_value, '/item/title/text()') title
        , EXTRACTVALUE(column_value, '/item/description/text()') description
        , EXTRACTVALUE(column_value, '/item/link/text()') link
        , EXTRACTVALUE(column_value, '/item/pubDate/text()') pubDate
        , rownum
        FROM TABLE(XMLSEQUENCE(EXTRACT(g_xmldata, '/rss/channel/item')));
  BEGIN
    l_http_req := utl_http.begin_request('http://www.reddit.com/' || p_subreddit || '.xml', 'GET');
    l_http_resp := utl_http.get_response(l_http_req, TRUE);

    BEGIN
        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_resp, 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_resp);
    END;

    IF l_http_req.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_req);
    END IF;
   
    IF l_http_resp.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_resp);
    END IF;

    g_xmldata := XMLTYPE(l_clob_response);
    
    FOR item in item_cursor
    LOOP
        DBMS_OUTPUT.put_line(item.rownum || ': ' || item.title || ' ' || substr(item.description, instr(item.description, '[', -1), instr(item.description, ']', instr(item.description, '[', -1))-instr(item.description, '[', -1)+1));
    END LOOP;
  END;

  PROCEDURE get_comments (p_item NUMBER) IS
    l_http_req        utl_http.req;
    l_http_resp       utl_http.resp;
    l_buffer_size     NUMBER(10) := 512;
    l_raw_data        RAW(512);
    l_clob_response   CLOB;
    l_comment_xml     XMLTYPE;
    l_comment_source  VARCHAR2(4000);
    
    CURSOR comment_cursor IS
        SELECT EXTRACTVALUE(column_value, '/item/title/text()') title
        , EXTRACTVALUE(column_value, '/item/description/text()') description
        , EXTRACTVALUE(column_value, '/item/link/text()') link
        , rownum
        FROM TABLE(XMLSEQUENCE(EXTRACT(l_comment_xml, '/rss/channel/item')));
  BEGIN
    SELECT EXTRACTVALUE(g_xmldata, '/rss/channel/item['||p_item||']/link/text()')
    INTO l_comment_source
    FROM dual;

    l_http_req := utl_http.begin_request(l_comment_source || '.xml', 'GET');
    l_http_resp := utl_http.get_response(l_http_req, TRUE);

    BEGIN
        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_resp, 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_resp);
    END;

    IF l_http_req.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_req);
    END IF;
   
    IF l_http_resp.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_resp);
    END IF;

    l_comment_xml := XMLTYPE(l_clob_response);

    DBMS_OUTPUT.put_line(' ');
    DBMS_OUTPUT.put_line(' ');
    DBMS_OUTPUT.put_line('Comments:');
    DBMS_OUTPUT.put_line('=========');
    FOR comnt in comment_cursor
    LOOP
        DBMS_OUTPUT.put_line(comnt.rownum || ': ' || comnt.title || ': ' || comnt.description);
    END LOOP;
  END;
  
  PROCEDURE get_image (p_item NUMBER, p_scale NUMBER default 10, p_end VARCHAR2 default null) IS
    l_http_req        utl_http.req;
    l_http_resp       utl_http.resp;
    l_buffer_size     NUMBER(10) := 512;
    l_raw_data        RAW(512);
    l_clob_response   CLOB;
    l_image_url  VARCHAR2(4000);
  BEGIN
  
    SELECT EXTRACTVALUE(g_xmldata, '/rss/channel/item['||p_item||']/description/text()')
    INTO l_image_url
    FROM dual;
    SELECT REGEXP_SUBSTR(l_image_url, '"([^"]+)">\[link\]')
    INTO l_image_url
    FROM dual;
    SELECT SUBSTR(l_image_url, 2, INSTR(l_image_url, '"', -1)-2)
    INTO l_image_url
    FROM dual;
    DBMS_OUTPUT.put_line('img: '||l_image_url);

    l_http_req := utl_http.begin_request('http://sandbox.palmnet.me.uk/ascii/main.php?pure=1&scale='||p_scale||'&image='||l_image_url||p_end, 'GET');
    l_http_resp := utl_http.get_response(l_http_req, TRUE);

    BEGIN
        <<response_loop>>
        LOOP
            UTL_HTTP.read_raw(l_http_resp, 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_resp);
    END;

    IF l_http_req.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_request(l_http_req);
    END IF;
   
    IF l_http_resp.private_hndl IS NOT NULL THEN
        UTL_HTTP.end_response(l_http_resp);
    END IF;


    DBMS_OUTPUT.put_line(' ');
    DBMS_OUTPUT.put_line(' ');
    DBMS_OUTPUT.put_line('Image:');
    DBMS_OUTPUT.put_line('======');
    DBMS_OUTPUT.put_line(l_clob_response);
  END;
END;
/