A day late but still fun


Yesterday Kim Berg Hansen posted a couple entries to his blog about scraping and parsing the Oracle Developer Choice Awards voting pages and building summaries of the results.

I’ve been doing something similar since the voting began but took a slightly different approach. I do like the idea of storing them in a table to be able to derive a history but I never did that, I instead simply looked at a snapshot in time and didn’t bother to keep trends.

Kim took two approaches, one using an apex call which he has said didn’t work very reliably and another where he simply pastes the html himself.

My method is similar but I use UTL_HTTP calls to extract the html.
Also, my function returns the raw html and then I do cleanup in my sql statement, he cleans his up within his scraping function.
Since he’s storing the results that makes sense, why incur the cost of cleanup each time he read his table?

Here is my version of the scraping function

CREATE OR REPLACE FUNCTION read_oracle_dev_choice(p_category IN VARCHAR2)
    RETURN CLOB
IS
    v_http_request    UTL_HTTP.req;
    v_http_response   UTL_HTTP.resp;
    v_buffer          VARCHAR2(32767);
    v_clob            CLOB;
    v_req_ctx         UTL_HTTP.request_context_key;
BEGIN
    v_req_ctx := UTL_HTTP.create_request_context('file:/home/oracle/devchoice', 'pa55w0rd', FALSE);

    v_http_request :=
        UTL_HTTP.begin_request('https://community.oracle.com/community/database/awards/' || p_category || '-voting/',
                               'GET',
                               'HTTP/1.1',
                               v_req_ctx);

    v_http_response := UTL_HTTP.get_response(v_http_request);
    --DBMS_OUTPUT.put_line('Response status code: ' || v_http_response.status_code);
    --DBMS_OUTPUT.put_line('Response reason phrase: ' || v_http_response.reason_phrase);
    --DBMS_OUTPUT.put_line('Response HTTP version: ' || v_http_response.http_version);

    DBMS_LOB.createtemporary(v_clob, TRUE);

    BEGIN
        LOOP
            UTL_HTTP.read_text(v_http_response, v_buffer, 32767);
            DBMS_LOB.writeappend(v_clob, LENGTH(v_buffer), v_buffer);
        END LOOP;
    EXCEPTION
        WHEN UTL_HTTP.end_of_body
        THEN
            UTL_HTTP.end_response(v_http_response);
    END;

    UTL_HTTP.destroy_request_context(v_req_ctx);

    RETURN v_clob;
END;

Once this scraping function has been created and you have your corresponding ACL and wallet entries, you can extract the results with a single SQL statement.

  SELECT *
    FROM (SELECT x.*, RANK() OVER(PARTITION BY category ORDER BY (score_rank + upvote_rank + uppct_rank)) avg_rank
            FROM (SELECT x.*,
                         RANK() OVER(PARTITION BY category ORDER BY score DESC) score_rank,
                         RANK() OVER(PARTITION BY category ORDER BY upvotes DESC) upvote_rank,
                         RANK() OVER(PARTITION BY category ORDER BY uppct DESC) uppct_rank
                    FROM (SELECT x.*, ROUND(100 * upvotes / totalvotes, 2) uppct
                            FROM (SELECT category,
                                         finalist,
                                         score,
                                         (score + 10 * votes) / 20 upvotes,
                                         (10 * votes - score) / 20 downvotes,
                                         votes totalvotes
                                    FROM (   SELECT cat category,
                                                    TO_NUMBER(REGEXP_SUBSTR(score, '[0-9]+')) score,
                                                    TO_NUMBER(REGEXP_SUBSTR(votes, '[0-9]+')) votes,
                                                    TRIM(finalist) finalist
                                               FROM (SELECT 'sql' cat, read_oracle_dev_choice('sql') html FROM DUAL
                                                     UNION ALL
                                                     SELECT 'plsql', read_oracle_dev_choice('plsql') FROM DUAL
                                                     UNION ALL
                                                     SELECT 'apex', read_oracle_dev_choice('apex') FROM DUAL
                                                     UNION ALL
                                                     SELECT 'db-design', read_oracle_dev_choice('db-design') FROM DUAL
                                                     UNION ALL
                                                     SELECT 'ords', read_oracle_dev_choice('ords') FROM DUAL),
                                                    XMLTABLE(
                                                        '//div[@class="jive-content-ideas-list-item clearfix"]'
                                                        PASSING xmltype(
                                                                    REGEXP_REPLACE(
                                                                        REGEXP_REPLACE(
                                                                            REGEXP_REPLACE(
                                                                                html,
                                                                                '^.+?
', '', 1, 0, 'n'), '.+$', '', 1, 0, 'n'), CHR(38) || '[^;]+?;')) COLUMNS score VARCHAR2(20) PATH './div/div/strong', votes VARCHAR2(20) PATH './div/span/span[@class="idea-vote-count"]', finalist VARCHAR2(20) PATH './div[@class="jive-content"]/div/div[@class="jive-content-title"]') x)) x) x) x) ORDER BY category DESC, score DESC;

And this produces results like this:


CATEGORY  FINALIST                  SCORE    UPVOTES  DOWNVOTES TOTALVOTES      UPPCT SCORE_RANK UPVOTE_RANK UPPCT_RANK   AVG_RANK
--------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
sql       Emrah Mete                 3010        335         34        369      90.79          1           1          1          1
sql       Sayan Malakshinov          1780        210         32        242      86.78          2           2          2          2
sql       Sean Stuber                 790         98         19        117      83.76          3           3          3          3
sql       Kim Berg Hansen             680         86         18        104      82.69          4           4          4          4
sql       Erik Van Roon               400         55         15         70      78.57          5           5          6          5
sql       Justin Cave                 300         44         14         58      75.86          6           6          8          7
sql       Matthias Rogel              290         38          9         47      80.85          7           7          5          6
sql       Stew Ashton                 260         38         12         50         76          8           7          7          8
plsql     Adrian Billington          1080        112          4        116      96.55          1           2          1          1
plsql     Roger Troller              1030        117         14        131      89.31          2           1          4          2
plsql     Sean Stuber                 910         97          6        103      94.17          3           3          2          3
plsql     Patrick Barel               690         79         10         89      88.76          4           4          5          4
plsql     Morten Braten               620         68          6         74      91.89          5           5          3          4
plsql     Kim Berg Hansen             440         51          7         58      87.93          6           6          7          6
plsql     Bill Coulam                 400         46          6         52      88.46          7           7          6          7
ords      Dietmar Aust               1240        128          4        132      96.97          1           1          1          1
ords      Dimitri Gielis              800         86          6         92      93.48          2           2          2          2
ords      Morten Braten               430         50          7         57      87.72          3           3          4          3
ords      Kiran Pawar                 350         39          4         43       90.7          4           4          3          4
ords      Anton Nielsen               240         28          4         32       87.5          5           5          5          5
ords      Tim St. Hilaire             130         16          3         19      84.21          6           6          6          6
db-design Heli Helskyaho             1030        119         16        135      88.15          1           1          2          1
db-design Michelle Kolbe              630         75         12         87      86.21          2           2          3          2
db-design Rob Lockard                 520         57          5         62      91.94          3           3          1          2
db-design Mark Hoxey                  160         23          7         30      76.67          4           4          4          4
apex      Jari Laine                  720         78          6         84      92.86          1           1          4          1
apex      Morten Braten               680         73          5         78      93.59          2           2          3          2
apex      Juergen Schuster            560         58          2         60      96.67          3           3          1          2
apex      Kiran Pawar                 430         48          5         53      90.57          4           4          5          5
apex      Karen Cannell               280         30          2         32      93.75          5           5          2          4
apex      Paul MacMillan              130         21          8         29      72.41          6           6          7          6
apex      Trent Schafer               120         17          5         22      77.27          7           7          6          7

I hope you find this helpful and a fun exercise to pursue.

If you haven’t voted, I encourage you to do so here…

I’m a finalist in the SQL and PL/SQL Categories and would, of course, appreciate a vote, be sure to check the other categories though too and vote up the finalists there that have helped you in some way.
Also note, you can vote up more than one person in a category, so by all means vote up as many as you want, including the other finalists in my categories if you want.

p.s. – an interesting “feature” of the web scraping is the URL works better when it includes a trailing slash “/”. I have no idea why this should help, but without it the returned CLOB will sometimes (often) contain “Unexpected Error Occurred” rather than the expected results. Occasionally the reverse happens though. If anyone can explain why this is, I would appreciate it.

Advertisements

Questions and Comments always welcome

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: