PL/SQL Puzzle: What Code Can Be Removed?
This article takes a look at a PL/SQL puzzle and explores which lines of code can be removed from a code block and not affect the outcome of the program.
Join the DZone community and get the full member experience.Join For Free
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
- The 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 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 no 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 — Collections are empty after declaring, always. So no reason to delete.
12-15 — Invoking the LAST method on an empty collection always returns NULL, so that call too DBMS_OUTPUT.PUT_LINE will never happen.
17 — This line has no impact on the behavior of the program because SELECT-BULK COLLECT-INTO always wipes out whatever was in the target collection before filling it.
28 — You don't need (and shouldn't use) an EXIT statement inside a FOR loop. It will automatically terminate when all the index values in the collection have been touched.
And here are pieces of code that can be removed from remaining lines:
5 — We do not have to declare this as an associative array. We can remove "INDEX BY PLS_INTEGER", which makes it a nested table. A SELECT-BULK COLLECT-INTO always initializes and extends nested tables and varrays.
8 — ":= l_empty" There is no need to initialize a collection with an empty one. It is automatically set to that state.
In which case, the end result is nothing more than this:
DECLARE TYPE objects_t IS TABLE OF all_objects.object_name%TYPE; l_objects objects_t; BEGIN SELECT object_name BULK COLLECT INTO l_objects FROM all_objects WHERE object_name LIKE '%TABLE%' ORDER BY object_name; FOR indx IN 1 .. l_objects.COUNT LOOP DBMS_OUTPUT.put_line (l_objects (indx)); END LOOP; END;
You can run (and play around with) both versions on LiveSQL with this script.
Did I miss anything? Do you disagree with any of my removals?
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.