I published a PL/SQL puzzle on Twitter on November 6 2019. I asked the following question:
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 - 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:
Did I miss anything? Do you disagree with any of my removals?
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 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 - 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;
Did I miss anything? Do you disagree with any of my removals?
I’ve solved my own confusion and replied to you by Twitter. Now my current question is how to write a plsql procedure comparing the output content of two types of PLSQL codes?
ReplyDelete