Posts

Showing posts from March, 2018

Rankings for 2017 PL/SQL Championship on the Oracle Dev Gym

Image
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.

RankNameTotal Time% CorrectTotal Score1li_bao (4)27 m78%55922mentzel.iudith (4)44 m78%55203NielsHecker (4)43 m76%53744Oleks…

Qualified expressions (aka, constructor functions) for collections and records in 18c

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, workouts a…

Mining Application Express data dictionary views: find unconditional processes

Image
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 authorization scheme…