Obsessed with Oracle PL/SQL

Just fielded this question: Hi, Is there any option to drop messages to different user? I would love to take help from all the top users from here. Yes, I am sure you would love that! Hundreds of really sharp Oracle Database developers answer quizzes on the PL/SQL Challenge. Wouldn't it be great if you could get in touch with them, with a question? And the answer is: "You can! But only if they have enabled that option." For example, the top player in the PL/SQL Challenge in terms of overall points is Stelios Vlasopoulos. He has also taken over 2,000 quizzes - no, wait a minute! over 3,100 quizzes! Wow, Stelio! And if you click on his name in the Player tab (or anywhere else in the site where his name appears as a link), you will see: The clue "Contact" link is there because in Stelios' Public Profile Settings, he has enabled contact: So you can go right ahead and get in touch with Stelios. Of course, if many players abuse this privileg...

Generally, you should use a simple loop if you always want the body of the loop to execute at least once. You use a WHILE loop if you want to check before executing the body the first time. Since the WHILE loop performs its check “up front,” the variables in the boundary expression must be initialized. The code to initialize is often the same code needed to move to the next iteration in the WHILE loop. This redundancy creates a challenge in both debugging and maintaining the code: how do you remember to look at and update both? If you find yourself writing and running the same code before the WHILE loop and at end of the WHILE loop body, consider switching to a simple loop. Here's an example. I write a procedure to calculate overdue charges for books; the maximum fine to be charged is $10, and I will stop processing when there are no overdue books for a given date. Here is my first attempt at the procedure body: DECLARE    l_fine PLS_INTEGER := 0;   ...

Waaaaaay back on June 27, we opened up the doors to the Oracle Dev Gym, our next generation website built on the quiz platform that powers the PL/SQL Challenge . One of the big changes for Dev Gym is a de-emphasis on the competitive aspect of quizzes, combined with making it much, much easier for a visitor to quickly take a quiz from our library of 2000+ quizzes. So far, overall, feedback has been quite positive, and we are very pleased. But a number of longtime players have offered feedback like the following: About Dev Gym: It is much easier to find what you want to do with this layout. The only thing I miss is the Dashboard where I can see at a glance the results of the latest quiz and maybe "Top Players (and You)", especially when I'm not at the first page of the Leaderboard :-) The home page of the PL/SQL Challenge offers both a dashboard (sorry about this "empty" example, but as a site admin, I cannot compete): and a Top Players (and You...

I learned an important lesson over the last few days. You all probably know this already, but as you may also know I am generally not reticent to expose my relative ignorance. So I follow a standard of adding four audit columns to my tables, populated by triggers, which keep track of who inserted/updated the row, and when: I certainly did this for the qdb_users table, which is the users table for the PL/SQL Challenge and Oracle Dev Gym. So far, so good. But recently a player complained that she was not receiving emails with results of her quizzes. I checked and found that the preference was turned off. Had she modified her user profile lately or had my code done something to  her row? It was pretty much impossible to tell, because we keep track of the user's last visit to the site - which means the app itself updates the qdb_users.changed_by/on columns every time a user comes to the site. This would overwrite whatever the user's last changed_on value was....

The PL/SQL compiler does more than compile - it also: automatically optimizes your code to run faster offers advice in the form of compile-time warnings to improve the quality and/or performance of your code allows you to conditionally include or exclude portions of your code for compilation That's just fantastic - but it can now and then result in some confusing moments for the Oracle Database developer (well, at least this developer). Recently, I was looking over the warnings I had gotten for a new package I'd written and saw this: Wait - my "procedure" user_goals_cur was removed ? I could tell by the name that it was not a procedure - it was a cursor. So clearly the warning message hasn't been customized to the type of code removed. OK, that's no big deal - I can deal with that.  But when I see the PLW-06006 warning, it has meant that I'd written a nested subprogram in a procedure or function but it was no longer used. It was...