Ever since my first KScope in 2012 I felt the need to grab the data in the list of KScope-sessions as published by ODTUG and do what I do best with data: Query it, in stead of scrolling through pages and pages of it.
If you feel that same need to easily find the KScope sessions that match your criteria by use of a SQL query, you landed on the right page.
Execute queries on the KScope site
Every year ODTUG makes a beautiful website with, among other things, a list of presentations and a summary of their contents.
And by the time KScope will start we usually get a new conference app.
But looking at such a webpage I can’t help but thinking: “The questions I have about this data would be answered so much easier if it was in a table in the database”.
So, In 2012 I build a simple package that reads the source of the presentations-page of the KScope website, extracts the relevant data en presents it as a pipelined table function.
In 2013 I rebuild it so it supported that years site, and the year after that again for KScope14.
In 2015 I changed the code to make it support every KScope of which the site is still live.
The code can be downloaded further below.
Disclaimer:
Remember,
I developed this because I wanted the data to be queryable, and I can imagine I’m not the only one. Plus, I simply had a lot of fun working on this.
I did not make this public to show off my brilliant code. It’s not. It’s quite simple, and because of it depending on the structure of the code of the webpage, rather unstable.
Changes to the page ODTUG might make, can render my code in it’s current state useless.
If that happens, I will try to repair it and publish a new version.
If you choose to use my code and one day you notice it stopped working, please let me know (I might not have noticed yet myself), so I can upload a new version that supports the new layout of the webpage.
I would never, ever build something like this for a production environment, nor do I want to encourage anyone to do so.
Examples:
Below examples are for data from KScope15
Supply the ‘number’ of the edition of KScope you’re interested in to the function en the sessions are returned.
For KScope15 that number obviously is 15.
|
1 2 3 4 5 6 7 |
SELECT * FROM TABLE(ero$kscope_sessions.kscope_sessions(15)) ORDER BY starttime , session_id , topic , subtopic ; |
Now you can easily answer questions like:
– Which speakers can not seem to squeeze their presentation into a one hour session.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT presenter , session_id , starttime , title , description FROM TABLE(ero$kscope_sessions.kscope_sessions(15)) WHERE UPPER(title) LIKE '%PART %' ORDER BY starttime , session_id ; |
– Which speakers this year did not present at the previous couple of KScopes.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT presenter FROM TABLE(ero$kscope_sessions.kscope_sessions(15)) MINUS SELECT presenter FROM TABLE(ero$kscope_sessions.kscope_sessions(14)) MINUS SELECT presenter FROM TABLE(ero$kscope_sessions.kscope_sessions(13)) MINUS SELECT presenter FROM TABLE(ero$kscope_sessions.kscope_sessions(12)) ; |
Code
| Zip file containing scripts with all create statements for the package, types and ACL needed to execute SQL queries on the kscope site. Just unzip and run driving_script.sql The download also contains scripts for materialized views (one for each supported kscope). The mviews are called MV_ERO_KSCOPE##_SESSIONS, where ## means the number of the kscope. Execute procedure ero$kscope_sessions.mv_refresh without parameters to refresh all kscope mviews. Enter a parameter value (for example 15) to refresh only the mview for that specific kscope. After a refresh you can query the data through the mview a lot faster than through the pipelined table function. Refresh again if (you suspect) the sessionlist has changed. |
||
| Version | Date | Changes |
| 2017-03 | May 21, 2017 | New version. Changes:
|
| 2017-02 | March 08, 2017 | New version, small change: html code for bullets is ow replaced by an asterisk (*). The only things changed are the package body and (the version number in) the specification. |
| 2017-01 | December 28, 2016 | New version, supports KScope17 sessions Site does not yet show date/time data,so obviously that is not returned by the code yet. When the date/time data is published the code will, if needed, be changed to support it. Plus: Added functionality to handle html ascii codes and some tags in session title and description. |
| 2016-02 | June 05, 2016 | New version. An ’empty session’ appeared in the list of sessions on the site, which caused all starttime, endtime and session-id data to disappear from all sessions. The package is altered, so now it handles this situation correctly. |
| 2016-01 | February 01, 2016 | New version, supports KScope16 sessions KScope site now shows date/time data, code did not need to be adjusted so, still version 2016-1 |
| 2015.03 | May 21, 2015 | – New code to support new sourcecode of KScope15 webpage. – Introduced materialized views + mv_refresh procedure. Fetch data once, query often. |
| 2015.02 | April 23, 2015 | No code changes. But the $ sign is removed from the scriptnames (not the objectnames!) because some experienced problems with this on unix/linux. |
| 2015.01 | January 25, 2015 | As Kim pointed out: the previous zip file was incomplete 🙁 |
Static table
I can imagine you would rather have a static table, or that you don’t have access to a schema with the privileges needed to create the acl.
That’s why I did a create table as select for you and supply it’s result here as both an Oracle table and an Excel file:
Obviously they are static. So come back for new versions if the sessionlist changes, or …. download the pipelined table function 🙂
| These two downloads contain the KScope17 data that was valid on June 17, 2017
Note, as of may 21, 2017:
|
|
| Excel file | Oracle table + insert statements |