It’s very easy to get and analyze voters list using my new XT_HTTP package 🙂
We can get up-voters list by the URL:
https://community.oracle.com/voting-history.jspa?ideaID=NNNN&start=0&numResults=1000
where NNNN is Idea ID from nomenee’s page.
For example my page – https://community.oracle.com/ideas/6901 so my voters page will be https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000
BTW, though this page is called “VotingHistory”, but it shows up-voters only 🙂
-- create table upvoters as
with
finalists(category, userid, name) as (
-- SQL~ Voting:
------ ------------------ -------------------------------------------------------------------------------------
select 'SQL' , 6899, 'Stew Ashton ' from dual union all
select 'SQL' , 6900, 'Sean Stuber ' from dual union all
select 'SQL' , 6901, 'Sayan Malakshinov' from dual union all
select 'SQL' , 6902, 'Matthias Rogel ' from dual union all
select 'SQL' , 6903, 'Kim Berg Hansen ' from dual union all
select 'SQL' , 6904, 'Justin Cave ' from dual union all
select 'SQL' , 6905, 'Erik Van Roon ' from dual union all
select 'SQL' , 6906, 'Emrah Mete ' from dual union all
-- PL/SQL~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'PL/SQL' , 6907, 'Sean Stuber ' from dual union all
select 'PL/SQL' , 6908, 'Roger Troller ' from dual union all
select 'PL/SQL' , 6909, 'Patrick Barel ' from dual union all
select 'PL/SQL' , 6910, 'Morten Braten ' from dual union all
select 'PL/SQL' , 6911, 'Kim Berg Hansen ' from dual union all
select 'PL/SQL' , 6912, 'Bill Coulam ' from dual union all
select 'PL/SQL' , 6913, 'Adrian Billington' from dual union all
-- ORDS ~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'ORDS' , 6881, 'Tim St. Hilaire ' from dual union all
select 'ORDS' , 6882, 'Morten Braten ' from dual union all
select 'ORDS' , 6883, 'Kiran Pawar ' from dual union all
select 'ORDS' , 6884, 'Dimitri Gielis ' from dual union all
select 'ORDS' , 6885, 'Dietmar Aust ' from dual union all
select 'ORDS' , 6886, 'Anton Nielsen ' from dual union all
-- APEX ~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'ORDS' , 6887, 'Trent Schafer ' from dual union all
select 'ORDS' , 6888, 'Paul MacMillan ' from dual union all
select 'ORDS' , 6889, 'Morten Braten ' from dual union all
select 'ORDS' , 6890, 'Kiran Pawar ' from dual union all
select 'ORDS' , 6891, 'Karen Cannell ' from dual union all
select 'ORDS' , 6893, 'Juergen Schuster ' from dual union all
select 'ORDS' , 6894, 'Jari Laine ' from dual union all
-- DatabaseDesign ~ Voting
------ ----------------- -------------------------------------------------------------------------------------
select 'DbDesign', 6896, 'Michelle Kolbe ' from dual union all
select 'DbDesign', 6897, 'Mark Hoxey ' from dual union all
select 'DbDesign', 6898, 'Heli Helskyaho ' from dual union all
select 'DbDesign', 6919, 'Rob Lockard ' from dual
)
,finalists_with_voters_pages as (
select finalists.*
,xt_http.get_page('https://community.oracle.com/voting-history.jspa?ideaID='||to_char(userid,'fm0000')||'&start=0&numResults=1000') page
from finalists
where rownum>0
)
,finalists_with_voters as (
select fp.category
,fp.userid
,fp.name
,voters.column_value as voter
from finalists_with_voters_pages fp
,table(cast(multiset(
select regexp_substr(page,'alt="([^"]+)"',1,level,'c',1)
from dual
connect by level<=regexp_count(page,'<li>')
) as ku$_vcnt)) voters
)
select *
from finalists_with_voters