I’m not Steven Feuerstein, should I even bother blogging?

If you’re not into PL/SQL, substitute Jonathan Lewis, Tanel Poder, Maria Colgan, or any other technology great you might respect. Doesn’t even need to be Oracle related.

The answer, quite simply, is yes.

First – it’s not a competition. If it were, then yes, I concede to the guy that literally wrote book on “Oracle PL/SQL Programming.” I don’t blog to show I know more or even as much as he does. I blog to share examples, interesting quirks, and use cases I’ve encountered. For all of the syntax descriptions in the Oracle manuals and all of the code downloads and explanatory videos out there, maybe my contribution will be the one that clicks with some reader googling for a particular problem. The point of my code isn’t that nobody else could have written what I did, but simply that I did write it, and maybe somebody else can benefit from it.

So, if you’ve written something you thought was interesting, by all means share it. Maybe it’s something with nested table collections that has been covered a thousand times before. Post it anyway, maybe your specific example, with your choice of descriptive phrasing will be the one that catches the next reader’s eye.

Second – more diverse voices matter. I’ve been told my style of writing is too casual and sometime unprofessionally conversational. I’ve also been told my writing helps make new syntax and/or complicated problems more approachable. So even if my content might be exactly what someone needs, if my delivery doesn’t work for them maybe the message gets lost. I’d feel a little bad about that; but it happens. Some of it just implicit. I only read one language well, English, so I only read blogs and watch videos in English. I’m sure I miss out on some good content because of that limitation. Others might too.

So, maybe you have something to say about collections that you know for sure Feuerstein has covered before. Go ahead, say it anyway. Use your code, your words, your language. Get your message, explained your way out there. If you happen to be a polyglot, even better. Translate your own work.

Third – what you’re posting is probably new to someone I’ve been coding in PL/SQL since version 7. I’ve written tens of thousands of lines of code. I don’t believe there is anything I’ve written that couldn’t have been written by lots of other people, but nobody is writing everything. You might be surprised with what others will look for. Month after month, the hottest article on my blog (by far) from search engines is one I wrote several years ago on using DBMS_PARALLEL_EXECUTE to run parallel pl/sql . I posted it because I thought it was a kind of quirky thing to do. I had no idea so many other people would search for exactly that. I’m going to guess I’m not the first or only person to do it; but I bothered to blog about it, so now that article is a resource out there for others. If I picked my blog articles based on what I thought would garner the most “hits” then I never would have written that article. My goal is one. If one person likes a post then I’ve done my job. Two and up is just bonus.

So, if you’ve got some strange use case and think it’s too obscure to be helpful to anyone else – post it anyway. Even if you’re right that it can’t be used in your exact form in other code; your approach, or general idea behind your solution might be the inspiration somebody else needs to get their problem solved.

Fourth – blogging is also helpful to the author. The act of of writing an instructional blog about code is different than the act of writing the code itself. It’s also different than writing comments for the code. When you write a blog article about some block of code you are removing that code from the context in which it was originally written. So, comments you might leave in the code for yourself or future maintainers might be meaningless when read with a stand-alone example. Similarly, intent may be obvious within the context of an application, but in isolation a code block could be opaque. Also, you don’t know your audience, so you need to write in such a way that it aids the intended reader. This may mean exploring syntax minutia and documenting features that are or are not used in order to give scope to your code. These extra steps improve your skill as a writer; but you will often find you learn more about your own code as you start fleshing out the areas around it.

So, if you got some code or practice that you think you have some expertise, challenge yourself to write about it so a novice can understand it. Or, you can flip it around. If there is something you want to learn about for yourself, start writing about it. You don’t have to publish immediately at each twist, turn, trial and error; but as you reach milestones – write about them. Your own blog can then be a reference for yourself as well.

Fifth – start the conversation If it’s your first line of code or your millionth, you have something to say about what you’re doing. Write it down. If you’ve got something good – show it off. If you’ve got a question – ask it. Maybe it’s something in between, a little code block, nothing fancy, but explained well. Put the code out there, get some feedback. You might get kudos and thanks, you might get questions, you might get corrections. Any kind of feedback is good. You might get nothing. That’s fine too. Most people don’t comment, “like”, or “vote” on posts. It’s ok, post anyway. When I started I was getting 1 or 2 visits per month, then a few more, then a dozen, then a few dozen, then a hundred, and another hundred, and another hundred, and so on. The audience is growing in silence but growing. But, let’s say it didn’t. A lack of traffic would itself be feedback. Maybe you need a different topic, different writing style, different audience. Maybe there are no problems with your content, but simply your blog’s interaction with search engines.

So, everyone had something to share, go ahead and share it. You might feel self-conscious about posting some code you’re not sure about it. You can either take a little more time to refine it or go ahead and post it, and ask for improvements. Maybe you’re afraid you’ll post your awesome toolkit and somebody better will come along and tear it to shreds, detailing hole after hole. For one, that’s probably not going to happen. If it does, and it’s bad enough, you can delete the article and repost later after applying corrections, or simply append to the article showing before and after. The work of changing could itself become an article series. I once wrote a 3-part series exploring syntax variations of a single query to solve a question posed by a friend. All but the very last example in the third article are objectively bad ways to do the work; but the point of the series was to explore and explain different approaches to the problem. I intentionally posted answers that I would not recommend for the sake of the exploration. Had i jumped immediately to the simple solution I gave him it wouldn’t have been as interesting or educational. When my friend asked me how to do it, he already assumed I could. When he read all the different ways I came up with he responded “I didn’t know you could do that!” I both solved the problem and showed him something new, making the whole exercise worthwhile.

In conclusion, if you’re thinking about starting a blog, just start. Some blog every day, some once a week, some once a month. Some are regular, some in bursts. It doesn’t matter, just start. Push out some content, talk to some people. Who knows? The next person to read your blog might be a potential employer that didn’t know you existed until he or she saw your work.

Go, put fingers to keyboard and welcome aboard!

Fast * Many = Slow, or, the Problem of Fast Code

Several years ago I made a little math joke about a process that performed millions of look-ups and took forever to complete. That joke, just a short one-liner: “Fast Times Many Equals Slow” has since become a bit of a mantra.

The problem can be particularly pernicious in that a problematic set of code might be well designed, extensively tested, and extremely fast. All three points are desirable and, in some cases, indisputable for a given routine. The performance problem arises when this fast code is executed repeatedly. The problem can sneak up on you. Maybe you write a small routine that executes in 14 microseconds and then you invoke it a thousand times. That means your code will contribute 0.014 total seconds to your process. Without any other context, that sounds pretty good to me and would often be considered acceptable, maybe even excellent.

However, if the process is ramped up to a billion iterations, now that fast routine adds almost 4 hours to your processing time.

0.000014 * 1000000000 = 14000 seconds = 3:53:20

If 4 hours (plus whatever time it takes to perform the rest of the process) is considered slow… is the problem that your routine is slow or is the problem that you ran it many times?

While I’ve seen this problem repeatedly over the years, it’s been particularly prevalent lately across a variety of applications from multiple sources (i.e. it wasn’t just one developer implementing the same design over and over.) I’m sure it’s merely coincidence that so many instances were revealed in a short period of time; but that doesn’t negate the underlying cause.

Iteration is a common programming technique and is often necessary. Sometimes you can mask it by hiding behind an API that performs the iteration for you, but doing so doesn’t remove the problem, it simply shoves it somewhere else. In some cases though, pushing the work somewhere else might be the right solution. There is nothing inherently wrong with iteration itself; but rather the choice of what is included within each cycle. A few weeks ago I wrote about the importance of timing instrumentation and how I used it solve a performance problem.

The problem discussed in that article was caused by misplaced iteration. Program A would read information from an external source and pass it to Program B to parse and process the individual records retrieved from the remote service. The iteration issue occurred because A would only feed the data in small increments to B. So B still needed to iterate over each record. That part was unavoidable (at some point “somebody” needed to act on each record) the problem was B already had iteration built in and A simply added extra work by pre-iterating. Not only that, each cycle carried additional overhead in a new database call, additional network traffic and additional response processing. Internal caching benefits within B were reduced because it was forced to start over with each new invocation.

It might have been possible to make the iteration in A and/or B a little faster, or rewrite B to support multi-call caching when processing within a larger set, but the simplest and most beneficial change was to simply pass all of the data in one call from A to B. This completely eliminated the overhead of making distinct calls. The internal work of B didn’t change at all. It iterated the same as it always did. Admittedly, it’s internal caching did become more effective but the reduction of the back and forth communication with A was the biggest savings. The entire process improved by about a factor of 10.

It’s important to note this was not a scaling factor in the billions as in my earlier example. While such volumes are not uncommon, many processes have no need to cycle that many times. In this example the code was only iterating over tens of thousands of rows, a magnitude many people and processes would consider “normal.” It’s also important to note that no one cycle was slow. The A program was able to parse quickly and split quickly. B was able to parse and process the records quickly.

As noted above, this is why iteration problems can be a subtle threat. Everything passed unit testing and seemed fast, because it was. It was only in the larger scale testing with full external volumes that the problem revealed itself.

So, what is one to do about it?

  1. Check to see if you are iterating needlessly. As seen in my parsing example, there was no need to add an extra layer of iteration. In the context of databases are you iterating row-by-row when you could be performing set-based operations? The database engine may still need to iterate across a data set; but it’s highly optimized for doing so. Adding row-by-row processing in your client is just another instance of adding iteration on top of iteration already in place. This is sometimes the hardest path to implement because it’s essentially a redesign; but it can be the most effective though by completely negating some steps.
  2. Upon what are you iterating? If you are processing a year’s worth of data and loop through each month, do you perform duplicate work in each cycle? If so, can you move some of that work into a step of its own and only do that work once? This is a good place to check for unnecessary “fast” steps such as key-value look-ups. Maybe for a given customer id you do a lookup for the customer name. It may be very fast with a nice, indexed, unique key retrieval; but if you do it for every order, every day, of every month. It adds up, especially when you do all of that again for the next customer, and the next, and so on. If you must iterate, can you sort the data before starting to remove the need to process identical data repeatedly. If you sort orders by customer, you can lookup the customer information once on the first order for that customer and reuse it until all of that customer’s data is exhausted.
  3. Is each cycle as fast as it can be? This may seem obvious; but it’s still worth a look. The same multiplicative factor making iteration painful can be used to your advantage as well. I worked with another DBA on a process iterating on hundreds of millions of rows. While it would have been nice to ask the vendor to redesign their product; that wasn’t really feasible as an immediate solution. Instead we shaved a few milliseconds off each iteration. One millisecond saved for one million executions is over 16 minutes removed from the total processing time. Multiply that by hundreds and we get DAYS of processing time improvement. Again, this was still not ideal, but it was appreciated by the end users waiting on the data.
  4. Are you including extra overhead in your iterations? Can you consolidate steps? This check is a special case combination of the previous two. As an example, a process that reads a web service, then pass the results to another process, and then take the results of that an pass them to a third process to insert or update some repository – a fairly straight forward ETL process. If you watch the data in an ETL flow, do you “E”xtract one row, “T”ransform that row, and then “L”oad that row? Might it be faster to Extract all of the rows, or a large volume of them, then pass those to a transform process, which then passes them in bulk to a loader? You can eliminate the overhead of invoking each step. This overhead could take make forms, including sql vs pl/sql context switches, network latency, file open/close operations, and more. Maybe instead of processing 1000 rows individually, you can process all 1000 together in each step, or if that’s too expensive then maybe 10 batches of 100, 4 batches of 250, 20 batches of 50… whatever the hardware can handle.
  5. Last option – can you parallelize? If individual iteration is required can you do 2 or more at the same time? Parallelizing is the most resource intensive plan of attack but that doesn’t necessarily mean it’s inappropriate. If you can isolate data into independent chunks and have the hardware available to process them, then why not? The most appealing part of parallel processing is it can be combined with any or all of the other solutions. The most important thing to remember when implementing parallelization is it doesn’t actually fix anything. If your process is flawed, it might be possible to throw enough hardware (money) at the problem to reduce the wall-clock time to something acceptable but doing so means your scaling is determined entirely by your wallet and it’s usually not going to be linear. That is, doubling the hardware doesn’t mean you’ll double the processing speed. There are problem sets where parallelizing adds additional benefits beyond reducing processing time. If you have independent data, such as regional sales, it makes sense to process the East region separately from the West region. Not only can you do both sets of work at the same time, but you also get a bonus of resiliency. A failure in the East doesn’t necessarily impact the West processing.

It might be more accurate to say “Fast * Many = A lot of time” but “Fast * Many = Slow” is a better mnemonic. Plus, the phrase is used when there is a performance issue in some looping process, which is mostly commonly described as the process being “slow.” My point was not to suggest all iteration was bad; but rather to consider the larger scale impacts when it occurs and what, if anything, you can or should do about it.

This has been a bit of a rant; but hopefully I was able to include some useful information along the way.

Oracle ACE

Oracle gave me an unexpected present for Christmas this year by naming me an Oracle ACE!

I’m truly honored to be included in the ranks of so many I’ve read and learned from.
Of course I’m also extremely happy to know my efforts to return the favor and share my knowledge and experience has been appreciated.

Thank you!

Nontechnical Skills Important To Being A Good Developer

What’s it take to be a good developer?

Brains? Of course you have to be able to think.
Logic? Sure, you need to have logical constructions.
Detailed? Maybe, you do need to pick out the intricacies of requirements.
Thorough? Yeah, I’ll give you that. The cause of all bugs is failing to cover some scenario.

I could go on about features of good developers and I’m sure readers have their own favorites; but the thing that I think stands out among the best developers is a “need to be right.” Now, of course this doesn’t mean that a developer is always right. I certainly am not. Nor does it mean that a developer needs to win every argument, although we may try.

No, the need to be right is a pursuit of correctness or best. One of the nice features of the software world is that many of our problems have absolutes in correct or incorrect; or, if not, they at least have objective comparisons of better or worse.

Does your calculation produce the correct answer? yes or no?
Does your procedure/function/method fulfill the requirement? yes or no?
Does subroutine A consume more memory than subroutine B while getting the same answer?
Does query X perform more IO operations then query Y to produce the same result?

Of course, a good developer wants to write good code; so it can be humbling when a bug is found, or a better way obsoletes your method. The need to be right though, supersedes the challenge to your ego. The need to be right is a relentless curiosity. If you find a bug in my code, thank you, if you identify why the bug occurs and how to fix it… THANK YOU! If you and I both have functioning SQL statements but yours is more efficient, THANK YOU! When I learn from a mistake or a superior design I “become right.” The need to be right is not about somebody else being wrong, it’s about you having, provably, the most correct and most efficient method possible. If that means you have to revise your solution based on new information, so be it.

One of the danger phrases that worries me is “I got it to work.” There’s nothing wrong, per se, with that phrase. I use it myself after a difficult challenge. The worrisome nature of it is that it’s often used as a declaration of finality. The task strained the brain and finally after much struggle, there is a successful result. “I got it to work, YAY!” But… does it work well? If you have the need to be right then you’ll want that answer. If you don’t, then you’ll call it done, check it in, and ship it off to production.

I’ve had many people ask me why I think it’s not a success. That’s simple. The problem was difficult, right? So difficult that you found it to be monumental when you got a successful run out of it. So then, what makes you think the thing that was so hard it couldn’t even produce the correct results until just now has simultaneously achieved maximum efficiency? Were you so busy tuning it while it was still wrong that it couldn’t possibly be any faster or less resource consuming? If so, that’s kind of an odd work style. Maybe that’s why it seemed so difficult?

It’s all related to the classic engineering joke/truism: Good/Fast/Cheap – pick 2. I believe in a corollary to that rule, it’s cheaper to doing something right once than twice wrong. So, while it might take more effort and cost to build a correct and efficient solution. In the long run that will end up begin cheaper because you don’t have to revisit as often.

However, there are traps in the need to be right. First, and probably the most obvious – you might fool yourself. If you try options A, B, C and determine C is best, that’s great; but if you never thought to look at option D you still might not be right. All you’ve done is prove you’re not the most wrong.

Second, there are problems that don’t have absolutes. “Most easily supportable”, “Best documentation”, “Most pleasing UX” – these ideals have leanings. There are approaches that seem more reasonable (follow some kind of standard, documentation that actually describes the objects, fewer interruptions in navigation) but at some point the differences just become preferences.

Third, you can be a jerk about it. If you forget that being “right” isn’t about somebody else being wrong. If you fall into this trap then you’re no longer helpful to your team even if you do happen to be “right” because attitude precludes successful cooperation.

The last trap is particularly tricky because it’s self-conflicting. Sometimes you should call it “good enough.” If the cost to improve some code is $1000 but you’ll only get $900 of benefit out of it, then don’t waste the money. It might indeed be possible to make a more efficient version; but if doing so is a net loss then don’t do it. Another problem with this approach is that it might not be that easy to produce an accurate cost/benefit analysis. If you call it quits early, you may be missing an opportunity. If you pursue too long you may be wasting time, money and other resources. When I run into this gray area I talk to my manager or client or whomever is footing the bill and give them an honest status. “I think this is working well, but could probably be improved. I’m willing to continue working on it; but it’s up to you how much to spend before accepting the results.” If you can give an estimate of cost to implement and expectation of improvement that’s even better.

Some might dismiss the idea of need to be right; that it’s just an obnoxious way of saying “try to write good code.” Any programmer that cares about their results will try to be both correct and efficient with their results. Yes, that’s true. I agree with that. However, the differentiating factor between merely being conscientious and having the need to be right is the proving.

If you ask any developer “Are you trying to write good code or bad code?” They will, of course, answer “good code.” In my opinion though, a good programmer is willing to show why their code is good. This “need” has two important benefits. First, by proving their design qualities they demonstrate they really do care about the quality of their output, they aren’t simply giving the desired response. Second, the act of proving correctness and efficiency requires technical insight and skill.

My favorite developers to work with always demonstrate this need to be right by showing their work and asking for review.

I shied away from writing a philosophical article because it’s one of those areas without an absolute.  This is merely an opinion; but I thought an interesting and important piece of successful development.  I hope you enjoyed reading.

 

Sean

 

The Curse of “Expertise”

Like everyone else, I make mistakes. While the results can sometimes be unfortunate, it’s also a truth that shouldn’t be ignored. A recurring problem though is that as a designated “expert” sometimes people don’t bother to test what I’ve given them. They just roll with it and then are surprised when their production installation goes awry.

I just ran into this situation again a few days ago. I was asked to help with a query that didn’t ever finish. I worked on it for a little while and came up with something that finished in a few seconds. Since the original didn’t finish, I didn’t have a predetermined set of results to test against. I manually walked through some sample data and my results seemed to tie out… so, it seemed like I was on the right track. I showed the client what I had and they were elated with the speed improvement.

I gave a brief description of what I had attempted to do and why it ran quickly. Then I asked them to test and contact me again if there were any questions.

The next day I got a message that they were very happy with the speed and were using it. I was glad to hear that but I also had been thinking that my query was extremely complicated, so even though it has apparently passed inspection I spent a few more minutes on it and came up with a simpler approach. This new method was almost as fast the other one but more significantly it returned more rows than my previous version. Clearly, at least one of them was incorrect.

With the simplified logic of the new version, it was much easier to verify that this second attempt was correct and the older more complicated version was wrong. I reached out to my client again and notified them of the change in query and problem I found. Then suggested they rerun more extensive tests anyway because I still could be wrong.

Fortunately, this second attempt did appear to be truly correct and the performance was still more than adequate.

See you in Las Vegas!

I’m flying out tomorrow for Collaborate 16.
Looking forward to another great conference.

I’m presenting again this year.
I’ll be speaking on Tuesday, at 2:15
“Why Developers Need to Think like DBAs, Why DBAs Need to Think like Developers”
Session 1355 in Jasmine C

Thank you, thank you, thank you!

A little while ago Oracle announced the winners of the Oracle Database Developer Choice Awards and I was a winner in both of categories I was nominated,

SQL and PL/SQL

I was surprised and overjoyed when I was notified that I had not only been nominated; but named a finalist.
I’m truly humbled by the supportive votes I received.

I’m also inspired to try to give back even more and I’m got a few ideas brewing for my next few articles.

Thank you again!

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.

Wow! I’m a finalist in the Oracle Developer Choice Awards!!!

Yesterday I was happily surprised by an email from Steven Feuerstein letting me know I hadn’t responded to my finalist announcement. Well, the reason for that was I never saw it! I’m glad he let me know though, what an honor.

I am a finalist in both the SQL and PL/SQL categories. If you’ve enjoyed any of my articles or I’ve answered a question for you or otherwise helped, I would appreciate and up-vote in the polls.

My profiles can be found here PL/SQL and here SQL.

Thank you very much for your support!