Monday, October 5, 2015

Oracle Database Developer Choice Awards Voting Status

The Oracle Database Developer Choice Awards voting will go on until October 15th 2015. If you haven't already, you should go vote now!

Here is the present status of how many votes have been submitted per category, including how many votes the category has increased with since "approximately yesterday" (since last time I downloaded the data, which I plan to do approximately daily ;-), and including how many votes the category has increased with since I started collecting data on October 2nd.

----------- --------- ----- ---------- ----------
Oct 09, 06h sql        1172         81        194
Oct 09, 06h plsql       657         13         96
Oct 09, 06h apex        489         47        150
Oct 09, 06h db-design   425         50        182
Oct 09, 06h ords        386          1         25

The SQL category clearly has gathered most votes, but since an APEX developer probably also has an opinion in PL/SQL and SQL, but not necessarily vice versa, this could have a natural explanation ;-)

And here is the present status of the individual nominees within each category with these data:

  • Score (points)
  • Total votes
  • Negative votes
  • Positive votes
  • Percent votes that are positive
  • Rank by score
  • Rank by positive votes
  • Increase in score "since yesterday"
  • Increase in positive votes "since yesterday"

----------- --------- ----------------- ----- ----- ---- ---- ------- ------- ------- ------- -------
Oct 09, 06h apex      Morten Braten       880    98    5   93      95       1       1      80       8
Oct 09, 06h apex      Juergen Schuster    720    78    3   75      96       2       3      50       6
Oct 09, 06h apex      Kiran Pawar         660    76    5   71      93       3       4      70       7
Oct 09, 06h apex      Jari Laine          590   103   22   81      79       4       2     -50       1
Oct 09, 06h apex      Karen Cannell       440    48    2   46      96       5       5      60       6
Oct 09, 06h apex      Paul MacMillan      320    48    8   40      83       6       6      60       6
Oct 09, 06h apex      Trent Schafer       280    38    5   33      87       7       7      60       6
Oct 09, 06h db-design Heli Helskyaho      970   171   37  134      78       1       1      50      13
Oct 09, 06h db-design Michelle Kolbe      940   118   12  106      90       2       2     130      13
Oct 09, 06h db-design Rob Lockard         760    86    5   81      94       3       3      90       9
Oct 09, 06h db-design Mark Hoxey          380    50    6   44      88       4       4      90       8
Oct 09, 06h ords      Dietmar Aust       1260   134    4  130      97       1       1       0       0
Oct 09, 06h ords      Dimitri Gielis      820    94    6   88      94       2       2      10       1
Oct 09, 06h ords      Morten Braten       440    58    7   51      88       3       3       0       0
Oct 09, 06h ords      Kiran Pawar         400    48    4   44      92       4       4       0       0
Oct 09, 06h ords      Anton Nielsen       250    33    4   29      88       5       5       0       0
Oct 09, 06h ords      Tim St. Hilaire     130    19    3   16      84       6       6       0       0
Oct 09, 06h plsql     Adrian Billington  1200   128    4  124      97       1       1      30       3
Oct 09, 06h plsql     Roger Troller      1060   136   15  121      89       2       2      10       1
Oct 09, 06h plsql     Sean Stuber         930   105    6   99      94       3       3      10       1
Oct 09, 06h plsql     Patrick Barel       780    98   10   88      90       4       4      60       6
Oct 09, 06h plsql     Morten Braten       650    77    6   71      92       5       5      10       1
Oct 09, 06h plsql     Kim Berg Hansen     460    60    7   53      88       6       6      10       1
Oct 09, 06h plsql     Bill Coulam         410    53    6   47      89       7       7       0       0
Oct 09, 06h sql       Emrah Mete         3440   412   34  378      92       1       1     380      38
Oct 09, 06h sql       Sayan Malakshinov  1860   252   33  219      87       2       2      60       7
Oct 09, 06h sql       Sean Stuber        1030   143   20  123      86       3       3      50       6
Oct 09, 06h sql       Kim Berg Hansen     660   112   23   89      80       4       4     -40       1
Oct 09, 06h sql       Erik Van Roon       490    79   15   64      81       5       5      70       7
Oct 09, 06h sql       Matthias Rogel      370    55    9   46      84       6       7      50       5
Oct 09, 06h sql       Justin Cave         360    64   14   50      78       7       6      50       5
Oct 09, 06h sql       Stew Ashton         310    55   12   43      78       8       8      50       5

These data I calculate via the methods I describe in my posts on Scraping and querying Oracle Database Developer Choice Awards votes Part 1 and Part 2.

I will endeavour to update this post daily until the voting period ends October 15th. If *you* haven't voted yet, go do so!

(There has been some discussion whether or not downvoting is fair in a competition like this. In my personal opinion it isn't, but technically this is a reuse of a voting system for ideas, so this is how it is and we'll live with it. But I think the final judges should perform these queries after the voting is ended, for if you look at the leading nominees in PL/SQL, Adrian ranks above Roger when ranking according to score, but Roger ranks above Adrian when ranking according to positive votes only. This is because Roger has been unlucky to get 11 more downvotes than Adrian. But a couple of days ago it wasn't an issue, Roger was in a clear leading position. Everything may change as more votes are entered, so we just check this again after the final vote is cast. UPDATE - Adrian is now in lead in both methods of ranking.)

Scraping and querying Oracle Database Developer Choice Awards votes - Part 2

In Part 1 I demonstrated how to "scrape" data from the live webpages with the votes of the Oracle Database Developer Choice Awards. Unfortunately those webpages are not ideal for scraping, so I promised a workaround. Here I do a semi-manual scraping af data by using a browser to retrieve the actual HTML, save it to a file, and then let the code parse out the data from the file.

Scraping and querying Oracle Database Developer Choice Awards votes - Part 1

You know about the Oracle Database Developer Choice Awards voting, right? If not, then go vote now, please, and then come back and read on :-)

I want to be able to query the voting status, who is ranked what in each category and so on. But I don't want to type in votes in a table, especially not when they change all the time. Instead I'd like to query the live data in the HTML webpages. Can that be done? Yes it can...

Thursday, September 24, 2015

Oracle Database Developer Choice Award Nominee = Me

Oracle is celebrating the Oracle Community by having us developers nominate and vote for
Oracle Database Developer Choice Awards to our fellow developers in five database development technologies:

The nomination period is over and it has been possible to vote since September 15th. Voting is open until October 15th and winners will be announced during Oracle OpenWorld 2015.

The nominees are all active in the Oracle Community helping others as they themselves have been helped when they started out in Oracle technology.

I am honored and humbled to be nominated in two categories:
- my absolute favorite technology: SQL
- as well as my second favorite: PL/SQL 

So if you think I am helpful to the Oracle Community, you can help me out by voting:

You can upvote me in the SQL category here.

You can upvote me in the PL/SQL category here.

But wait, why should you do that? Guess I'm not much of a politician, I haven't yet told you of how bad my competitors are and how brilliant I myself am ;-) Well, the first I won't ever do - my competitors are all deserving the honor too, many have helped me selflessly.

But how brilliant am I? Well, just middling, if I say so myself ;-) But I have an absolute passion for using SQL to the full utilizing the potential of the database to the max. I go against anyone who try to use the database as a simple black-box bit-bucket. So I will help anyone who wants to learn how to make their applications perform astoundingly fast by learning analytic functions, for example.

Similarly PL/SQL is awesome both to expand the database capabilities and to encapsulate business logic as APIs for UI code to call. That way of coding applications I have used with success since 2000 and I'm happy to teach others how this helps applications in the long term.

What have I done to help others share my knowledge?
  • I write weekly quizzes on the PL/SQL Challenge website.
    You can see the quizzes I have written here.
  • I do presentations at conferences.
    I have presented on analytic functions and external data at ODTUG Kscope and UKOUG.
    I will present at DOAG this year.
    I will be part of an expert PL/SQL panel at Oracle OpenWorld this year.
    You can see slides of my presentations here.
  • I write on this blog and from time to time answer questions on Oracle-L mailinglist and OTN forums.
But can you trust what I say? Well, I have a few testimonials ;-)
  • A user at the PL/SQL Challenge has been learning by taking quizzes from the library and has been kind enough to vote for me with this comment:
    "I voted for Kim because once I realized his login name kibeha was the same Kim Berg Hansen, then I knew to vote for him for coming up with some great quizzes on the PL/SQL Challenge website. The website has been great for me to expand my knowledge and to help prepare for my OCP Advanced PL/SQL Developer."
    I think it is awesome that my work can help prepare for an OCP certification :-)
  • An attendee at ODTUG Kscope 2012 met me in the corridors the day after my presentation on analytic functions and told me, that he used to find them difficult to understand but my examples helped a lot to make it clear to him how they worked.
    That made my day :-)
  • A reader of my blog read a post I wrote on RANGE versus ROWS BETWEEN in analytic functions and commented:
    Thanks! I've been reading the training kit for 70-461 but it didn't make this clear. You blog here lays it our perfectly.
    Again I am delighted I can help passing certification exams.
So I guess that's my sales pitch done for. Go and vote now! Go!
If you vote for me, I sincerely thank you for your help.
If you vote for others, that's also fine, just as long as you vote.

Remember it is OK to vote for more than one person in each category if you feel they deserve it :-)

Thursday, July 30, 2015

Row pattern matching nested within hierarchy

I've been playing around with MATCH_RECOGNIZE - the data pattern matching extension to SELECT that was introduced in version 12.

Most examples I've seen have used the default AFTER MATCH SKIP PAST LAST ROW as most often the logic dictates, that when we have found a match in a group of rows, we want to search for further matches after those rows to avoid unwanted "double" matches.

But can there be uses where we want overlapping or even nested matches? Well, I found at least one case where I think it makes perfect sense...

Friday, June 26, 2015

ODCI dynamic table function parser

Supposing you've got data as a text string with "row" and "column" delimiters and you would like to parse out those columns and rows. The external table syntax would actually be great for this, but that requires writing the text out to a file first, and then you can import it back again as columns and rows.

There are various other alternatives using SQL and/or XML manipulations, but one method that I don't see much used is to use the Data Cartridge functionality to in a sense expand the SQL language to do this. This solution is greatly inspired by an OTN forum post I stumbled upon, where Anton Scheffer demonstrates the use of ODCITable* functions.

Monday, June 22, 2015

Escope very early status

Just a brief summary of the status of Escope so far - I know it's very very early and too early to even tell whether it'll become real or not, but anyway ... ;-)

  1. For those who don't know yet, we're trying to see if it is possible to create a conference in Europe like the Kscope conference by ODTUG. See my early thoughts here:
  2. We're gathering feedback from as many European developers as we can via a survey designed to find out if there is a basis for such an Escope conference:
    Please share that link with all your colleagues and network of Oracle developers throughout Europe - the more responses we can get, the more valid conclusions we can make.
  3. As of today (21. june 2015) we have 80 responses - definitely mostly positive, thank you for that :-) And getting valuable information on what the European Oracle developers are looking for. We have started getting in touch with a little of the Hyperion/Essbase/EPM/BI community in Europe as well and hope to get responses from them as well as the DbDev and APEX developers that so far seem to have been the majority of responses. Opinions af all developers are valuable to us.
  4. We have some thoughts on what might be the challenges we have to overcome to make this dream come true. See some discussion points I've made here:
    I invite you all to pitch in with your own opinions and ideas as comments to that blog post.
  5. If you are attending Kscope15 right now, we can discuss the idea of Escope face to face.
    I will be in the exhibition hall nearby the ODTUG social media booth (booth 613) during happy hours monday as well as tuesday:
    - Monday 22. june 17:00 to 17:30 (or 5 PM to 5:30 PM if you prefer ;-)
    - Tuesday 23. june 18:00 to 18:30 (or 6 PM to 6:30 PM)
  6. You can sign up for a newsletter to be kept up to date on the progress of Escope here:
That's about it so far - we don't know yet if this will remain a dream or if it will take off and be a new shining star. You can help just by circulating the news and sharing the survey link as much as you can. Here it is again:

You'll be hearing more of Escope from me, don't worry :-)