Obsessed with Oracle PL/SQL

Almost certainly, whenever you write a procedure or function, you make certain assumptions. Some of them are quite reasonable, such as "I assume my database is up and running." Some of them are scary, such as "I assume my users will never change their minds." But many simply go unnoticed. You don't even realize you are making an assumption until it smacks you in face, most likely in production, when an unexpected error exposes the assumption. So in this PL/SQL puzzle, as I state on Twitter : The procedure shown below compiles without error. What assumptions am I making so that when it executes, it does not terminate with an exception? White space so you do not immediately see my answers.  :-) OK, let's dive in. I provide below all of the assumptions I was aware, and also some others that were provided in Twitter on the very active discussion that followed. As usual, I learned something new from the community! Line 3: by hardcoding the dataty...

On December 3 for our AskTOM Office Hours session , three deeply experienced and highly respected APEX prosshared their wisdom on how best to write and manage PL/SQL code for Oracle Application Express projects. You can watch the recording and follow links to related resources here . Our experts were: Karen Cannell Karen promotes APEX topics and best practices through local, regional and national user group presentations and papers. A devoted user group volunteer since 2007, she is especially active in ODTUG, where she serves as the editor of the ODTUG Technical Journal. She is former Associate Editor of IOUG SELECT Magazine. She is co-author of Agile Oracle Application Express, Expert Oracle Application Express and Beginning Oracle Application Express 4. Her most recent presentation at Kscope15 focused on APEX Interactive Reports with a Deep Dive: APEX 5 New Features and Upgrade Cheat Sheet. Karen has delivered Application Express solutions since its Web DB and HTMLDB b...

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