Obsessed with Oracle PL/SQL

Thirty-six Oracle Database technologists competed on March 22nd in the 2017 PL/SQL Annual Championship at the Oracle Dev Gym . With five tough quizzes by yours truly, the competition was fierce! Congratulations first and foremost to our top-ranked players: 1st Place:  li_bao  of Russia 2nd Place:  mentzel.iudith  of Israel 3rd Place:  NielsHecker  of Germany Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community. In the table below of results for this championship, the number next to the player's name is the number of times that player has participated in a championship. Below that table, you will find another list showing the championship history of each of these players. Rank Name Total Time % Correct Total Score 1 li_ba...

As anyone who has followed me over the years knows, I like the Oracle PL/SQL language. Sure, it's not the newest, coolest kid on the block (it probably never  was). But then, either am I. :-) PL/SQL is, on the other hand, a delightfully straightforward, easy to learn and write language that serves its purpose well: implement APIs to data (SQL) and business logic, right inside the database. To serve that purpose, of course, PL/SQL needs to support lots of "big ticket" functionality: super-smooth and easy native dynamic SQL , canonicalization of static SQL to minimize the need for hard-parsing, invoker rights (AUTHID CURRENT_USER) and so much more. But I must confess: the features of PL/SQL that I love the best are the relatively "little" things that make it easy for me to be productive as I churn out the packages (and, yes, I still do write lots of PL/SQL code, most lately for the Oracle Dev Gym , an "active learning" website featuring quizzes, wor...

I ran into a problem yesterday on the  Oracle Dev Gym  (offering quizzes, workouts and classes on Oracle technologies). A number of rows of data were incorrectly deleted. I was able to use  Flashback Query  to restore them (thank you, thank you, Flashback Query!). Crisis averted.  But how did this come about? I recruited Chris Saxon to help me figure out how this could have happened. In relatively short order, we narrowed down the culprit to a process in the Dev Gym Application Express definition that was unconditionally "removing previews" - but was in fact removing all rows, "previews" or not. Ugh. So we fixed that. But it got me wondering and worrying: what other processes in my app are unconditional? And should they be? While some processes fire unconditionally on a page (for example, to get the data from tables and display them on the screen), many are (or should be!) restricted to a button press, the result of a conditional expression, or an ...