Obsessed with Oracle PL/SQL

Another Oracle Dev Gym championship completed! Find below the rankings for the PL/SQL Challenge Championship for 2019. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players. Congratulations first and foremost to our top-ranked players: 1st Place: MarkusId 2nd Place: mentzel.iudith 3rd Place: NielsHecker Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, we will make the championship as a whole available as a workout, so you can take it just like these players did. Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community. Rank Name Total Time % Correct ...

You will find below the rankings for the Logic Annual Championship for 2019, held in February 2020 on the Oracle Dev Gym . The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players. Congratulations first and foremost to our top-ranked players: 1st Place: Stelios Vlasopoulos 2nd Place: Sartograph 3rd Place: NielsHecker Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes through the Practice feature. We will also make the championship as a whole available as a Workout, so you can take it just like these players did. Finally, many thanks to Eli Feuerstein, Logic Quizmaster who provided a very challenging set of quizzes...

Throughout 2019, over 1200 Oracle Database developers participated in the Oracle Dev Gym PL/SQL Challenge weekly tournament. The top 50 ranked players shown below will compete for top honors in a championship on February 18, 2020. The number in parentheses after their names are the number of championships in which they have already participated. As you can see, there are some very dedicated players here! Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! Name Rank Stelios Vlasopoulos (16) 1 mentzel.iudith (19) 2 NielsHecker (20) 3 Chad Lee (16) 4 Peterman (6) 5 siimkask (19) 6 _tiki_4_ (12) 7 Chase Mei (5) 8 Ludovic Szewczyk (4) 9 li_bao (7) 10 MarkusId (0) 11 Andrey Zaytsev (8) 12 Michal P. (3) 13 Arjun Barath (0) 14 Ivan Blanarik (13) ...

I published yet another PL/SQL puzzle on Twitter yesterday. Generated lots of interest and interesting replies. I don't think any single person caught everything , but as usual the community came through. I will repeat the puzzle here. If you haven't already seen it on Twitter, please try to solve it yourself before looking at my answer. What text can be removed from lines 3 though 12 in the code below so that after the anonymous block is executed, "121212" is still displayed on the screen? White space so you do not immediately see my answers.  :-) OK, let's dive in. Notice, first of all, that I asked about text  that can be removed, not lines. So you can remove entire lines or portions of lines. I refuse to accept that whitespace is text, so blank lines don't count. :-) Here are the opportunities for removal that I found: 4 - Remove the IN keyword. That's the default for parameters (though I generally always include it in my cod...

I published a PL/SQL puzzle on Twitter on November 6 2019. I asked the following question: Which lines of code can be removed (either entirely or in part) from the block below and not affect the output of the program in any way? I neglected to mention in my original tweet a few important assumptions: You are running this code on Oracle Database 10g or higher. Server output is turned on. Whitespace (spaces, tabs, new-lines) don't count. Here's the code. I will publish it as an image, just as I did on Twitter, so that you can give it a go yourself, before taking a look at the answers from me and others below that. Check out the Twitter conversation for all the answers that were submitted. It's a fun read! Here are the full lines that I believe can be removed: 2 - There is not need to declare the iterator used in a FOR loop, numeric or cursor versions. 7 - There is no need to declare an "empty" collection to be used to initialize l_objects. 10 - ...

Hello there, Dev Gym members! We plan to release a new version of the Dev Gym (19.3 - we are aligning with the overall version schema followed by Oracle Database and sister sites, like Oracle LiveSQL ). Mainly, it is a tweak of the UI, to bring our site in line with developer.oracle.com and also to make better use of our "real estate." Before we apply the changes, though, we'd love to get some feedback from our users. So when you have a moment or two, please scroll down the page, compare current and 19.3 pages, then let us know what you think. Thanks very much, Steven Home Page Current Site A few things to point out: Orange theme Large banner with search bar Dev Gym 19.3 Home Page Things to note: Switch to black-grey-red theme (just a little bit of red) Much smaller banner Search bar moved to top of site, independent of page Cleaner display of quizzes (we'd like to think) Moving on, in the workouts and classes pages, we've r...

Waaaaay back in 2010, on April 8 to be specific, I started a website called the PL/SQL Challenge. It featured a daily PL/SQL quiz (yes, that's right - a new quiz every weekday!) and gave Oracle Database developers a way to both deepen and demonstrate their expertise. Players were ranked and competed for top honors in our annual championships. Not quite as waaaaay back, in 2014, I rejoined Oracle Corporation after 22 years away (from the company, not from the technology). The PL/SQL Challenge came with me, and a year later we rebranded it as the Oracle Dev Gym . Today, we offer quizzes on SQL, PL/SQL, database design, logic, Java and Application Express. We've added workouts and classes. Yesterday we celebrated the ninth anniversary of the Dev Gym / PL/SQL Challenge. And my oh my but Oracle Database developers have been busy! Here are some stats from those nine years: Almost 35,000 developers and DBAs have taken quizzes on the site, a total of 1.27M answers subm...

Logic is at the very heart of programming, so we complement our quizzes on SQL, PL/SQL and so on with a weekly Logic tournament. And then at the end of the year, the top 50 ranked players qualify for our annual championship. The following players will be invited to participate in the Logic Annual Championship for 2018, currently scheduled to take place on 19 February. The number in parentheses after their names are the number of championships in which they have already participated. Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition! Name Rank Stelios Vlasopoulos (5) 1 Pavel Zeman (4) 2 mentzel.iudith (5) 3 Ludovic Szewczyk (1) 4 James Su (5) 5 Chad Lee (5) 6 Tony Winn (3) 7 Rytis Budreika (5) 8 ted (5) 9 Kanellos (4) 10 Cor van Berkel (4) 11 Köteles Zsolt (4) 12 V...

A new year has arrived, and that means that it's time (or will soon be time) for the PL/SQL Challenge Championship, when up to fifty top players from last year's PL/SQL tournament quizzes compete for top honors. The following players will be invited to participate in the PL/SQL Challenge Championship for 2018, currently scheduled to take place on 21 March (hey, it takes some time to put together five advanced quizzes without any mistakes in them!). The number in parentheses after their names are the number of championships in which they have already participated (note: from 2010 through 2013, we held quarterly championships for our then daily PL/SQL quiz!). Congratulations to all listed below on their accomplishment and best of luck in the upcoming championship! Name Rank Stelios Vlasopoulos (15) 1 mentzel.iudith (18) 2 Tony Winn (7) 3 NielsHecker (19) 4 Andrey Zaytsev (7) 5 patch72 (5) ...

Over the weekend of April 21, we upgraded the Oracle Dev Gym site to v3 (code name: ORANGE). Here's the v2 home page: and now v3: Now you see the reason for the code name. It's orange! Here are the key changes you will find on the Dev Gym: Orange theme: all that red was hurting our eyes, but the main reason to switch to orange was to make it visually clear that this site, as with AskTOM , is part of the broader Oracle Developer initiative . Site search: type in a keyword, such as "FORALL" or "listagg" in the search bar on the home page, and we will find all quizzes, workouts and classes that match your criteria. You can further hone your search on the results page. The tournament quizzes are now offered on the home page; no need to click on the Tournaments tab to see them. These quizzes are produced fresh each week, and often focus on the latest features in SQL, PL/SQL and Oracle Database. Your recent activity on the site is a...

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

We upgraded the Oracle Dev Gym site this past weekend to 2.2. Why should you care? Because the Dev Gym offers a great active learning complement to reading doc and blogs, and watching videos. We help you deepen and broaden your expertise through exercise: taking quizzes that reinforce newly gained knowledge. Quizzes At the heart of the Dev Gym is a library of over 2,400 quizzes on SQL, PL/SQL, Oracle Application Express, Database Design and Logic. Almost all these quizzes were first played as part of our daily and then weekly tournaments - which means they've been reviewed by experts and taken by hundreds and in some cases thousands of developers. They are usually code-based quizzes that strengthen you ability to read and understand code (rather than "try it and see" by running the code). The Dev Gym home page offers a selection of favorite quizzes hand-picked each week by our quizmasters from this library. But that's just the tip of the iceberg. Looking to st...

As some of you may know, we've been working on a new "skin" for plsqlchallenge.oracle.com , one that is more modern and responsive, and that makes it easier to quickly take a quiz. The Oracle Dev Gym is still in an "early adaptor" state; you can take quizzes, including our weekly competitive quizzes, there. You can set up workouts and so forth. But we are not yet offering it as a day-by-day alternative to the "traditional" PL/SQL Challenge. Here's a quick update: 1. The URL devgym.oracle.com is now configured, so you can more easily go directly to the Oracle Dev Gym (instead of going "through" the PL/SQL Challenge. 2. We've come up with a simpler, more immediate design for our home page. Rather than having to choose "Take a Quiz" from the home page, and then go through another layer of selection from there, the home page now offers immediate access to quizzes. The previous home page: The new home page: ...

Back in April 2010, I started (with the help of many others!) the PL/SQL Challenge , which offered daily quizzes on PL/SQL, along with weekly quizzes on other technologies. In 2014, Oracle acquired the website and I suspended the daily quiz. Writing five new quizzes each week is a lot of work, and I had a big stack of new responsibilities! But the weekly quizzes continue(d): SQL, PL/SQL, Database Design, Logic and most recently a re-started weekly quiz on Oracle Application Express, made possible by Insum . And Oracle Database technologists kept on using the site to hone their skills. We are rapidly approaching 1,000,000 answers on the site. That all sounds good, right? So why is the title of this post "Check out the Oracle Dev Gym!"? Because the PL/SQL Challenge has, for a while, been in dire need or a "reboot"; not just a redesign built around the massively improved APEX5 , but also a rethinking of how this quiz platform could best serve the needs of the co...