PL/SQL Puzzle: What Code Can Be Removed?

DZone 's Guide to

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.

· Database Zone ·
Free Resource

Image title

PL/SQL Puzzle

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?

You might also like:  PL/SQL Puzzle: Add One Statement to Stop Exceptions

I neglected to mention in my original tweet a few important assumptions:

  1. You are running this code on Oracle Database 10g or higher
  2. The server output is turned on
  3. 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:

   TYPE objects_t IS TABLE OF all_objects.object_name%TYPE;
   l_objects   objects_t;
     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
      DBMS_OUTPUT.put_line (l_objects (indx));

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?

Further Reading

Don’t Test PL/SQL Features With Trivial Code

PL/SQL — Don’t Mix and Match Scope

code puzzle, database, livesql, oracle database, plsql, removed code

Published at DZone with permission of Steven Feuerstein , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}