Reduce the volume of PL/SQL code you write with these tips

I'm not known for being concise. I'm best known in the world of Oracle Database for my "magnum opus" Oracle PL/SQL Programming, which checks in at 1340 pages (the index alone is 50 pages long).

But I've picked up a few tips along the way for writing PL/SQL code that is, well, at least not as long, as verbose, as it could have been. And certainly shorter than my books. :-)

You probably have some ideas of your own; please offer them in comments and I will add them to the post.

Qualified Expressions (new to 18c)

In the bad old days before Oracle Database 18c was released (and is now available for free in its "XE" form), if you wanted to initialize an associative array with values, you had to do in the executable section as follows:

DECLARE   
   TYPE ints_t IS TABLE OF INTEGER   
      INDEX BY PLS_INTEGER;   
   
   l_ints   ints_t;   
BEGIN   
   l_ints (1) := 55;  
   l_ints (2) := 555;  
   l_ints (3) := 5555;  
  
   FOR indx IN 1 .. l_ints.COUNT   
   LOOP   
      DBMS_OUTPUT.put_line (l_ints (indx));   
   END LOOP;   
END;

As of 18c, you can use a qualified expression (think: constructor function) as follows:

DECLARE  
   TYPE ints_t IS TABLE OF INTEGER  
      INDEX BY PLS_INTEGER;  
  
   l_ints   ints_t := ints_t (1 => 55, 2 => 555, 3 => 5555);  
BEGIN  
   FOR indx IN 1 .. l_ints.COUNT  
   LOOP  
      DBMS_OUTPUT.put_line (l_ints (indx));  
   END LOOP;  
END;

The same is true for user-defined record types. This feature not only leads to a reduction in lines of code but also allows you to write more intuitive code.

Check out my LiveSQL script for lots of examples and also my blog post on this topic.

Normalized Overloading (hey I think I just invented that term!)

Normalization of code is just as important as normalization of data. Don't repeat your data, and don't repeat your code (a.k.a., DRY - don't repeat yourself, and SPOD - single point of definition).

A great example of how important this can be is with overloading. Overloading, also known as static polymorphism (sorry, just couldn't help throwing that in), means you have two or more subprograms with the same name, but different parameter lists or program type (procedure vs function).

It's a very nice feature when it comes to reducing the number of moving parts in your API (package specification), and making it easier for developers to use your code. Usually, those multiple subprograms with the same name are doing almost the same thing, which means most of their implementation will be the same, which means....watch out for redundant code!

Here's an example from the thread (discussion) manager package of the Oracle Dev Gym backend. I start off with a single procedure to insert a new thread:

PACKAGE BODY qdb_thread_mgr
IS
   PROCEDURE insert_thread (
       user_id_in               IN PLS_INTEGER
    ,  parent_thread_id_in      IN PLS_INTEGER
    ,  thread_type_in           IN VARCHAR2
    ,  subject_in               IN VARCHAR2
    ,  body_in                  IN CLOB)
   IS
   BEGIN
      INSERT INTO qdb_threads (...) VALUES (...);
   END;
END qdb_thread_mgr;

That's great, but I now run into a situation in which I need to get the new thread ID back to use in another step of a process. The easiest thing to do is cut and paste.

PACKAGE BODY qdb_thread_mgr
IS
   PROCEDURE insert_thread (
      user_id_in               IN PLS_INTEGER
    ,  parent_thread_id_in      IN PLS_INTEGER
    ,  thread_type_in           IN VARCHAR2
    ,  subject_in               IN VARCHAR2
    ,  body_in                  IN CLOB)
   IS
   BEGIN
      INSERT INTO qdb_threads (...) VALUES (...);
   END;

   PROCEDURE insert_thread (
      user_id_in               IN     PLS_INTEGER
    ,  parent_thread_id_in      IN     PLS_INTEGER
    ,  thread_type_in           IN     VARCHAR2
    ,  subject_in               IN     VARCHAR2
    ,  body_in                  IN     CLOB
    ,  thread_id_out               OUT PLS_INTEGER)
   IS
   BEGIN
      INSERT INTO qdb_threads (...) VALUES (...);
        RETURNING thread_id
             INTO thread_id_out;
   END;
END qdb_thread_mgr;

It's not hard to argue, in this case, "so what, why not?" After all, the procedure consists of just a single INSERT statement. Why not copy-paste it? I get that, but here's the thing to keep in mind always with code:

It's only going to get more complicated over time.

That one statement will grow to three statements, then to 25 statements. And each time, along the way, you must remember to keep the two procedures in synch. And what if there are five of them?

It makes so much more sense to have a single "reference" procedure or function with all of the common logic in it. Each overloading then takes any actions specific to it before calling the reference procedure, followed by any finishing-up actions.

For the thread manager package, this means that the procedure returning the new primary key is the "reference" implementation, and the original procedure that ignores the new primary key

PACKAGE BODY qdb_thread_mgr
IS
   PROCEDURE insert_thread (
      user_id_in               IN     PLS_INTEGER
    ,  parent_thread_id_in      IN     PLS_INTEGER
    ,  thread_type_in           IN     VARCHAR2
    ,  subject_in               IN     VARCHAR2
    ,  body_in                  IN     CLOB
    ,  thread_id_out               OUT PLS_INTEGER)
   IS
   BEGIN
      INSERT INTO qdb_threads (...)
           VALUES (...)
        RETURNING thread_id
             INTO l_thread_id;
   END;

   PROCEDURE insert_thread (
      user_id_in               IN PLS_INTEGER
    ,  parent_thread_id_in      IN PLS_INTEGER
    ,  thread_type_in           IN VARCHAR2
    ,  subject_in               IN VARCHAR2)
   IS
      l_id   PLS_INTEGER;
   BEGIN
      insert_thread (
         user_id_in               => user_id_in
       ,  parent_thread_id_in      => parent_thread_id_in
       ,  thread_type_in           => thread_type_in
       ,  subject_in               => subject_in
       ,  body_in                  => body_in
       ,  thread_id_out            => l_id);
   END;
END qdb_thread_mgr;

This is straightforward stuff, not rocket science. It just comes down to discipline and an aversion to repetition. Of course, sometimes it's a bit more of an effort to identify all the common logic and corral it into its own procedure. But it is a refactoring project that is well worth the effort.

CASE Expressions Not Statements

One of things I like best about CASE over IF is that it comes in two flavors: a statement (like IF) and an expression. CASE expressions help me tighten up my code (check out this LiveSQL script for examples).

Consider the following function, which returns the start date for the specified period (month, quarter or year) and date.

FUNCTION start_date (
   frequency_in   IN VARCHAR2,
   date_in        IN DATE DEFAULT SYSDATE)
   RETURN VARCHAR2
IS
BEGIN
   IF frequency_in = 'Y'
   THEN
      RETURN TO_CHAR (ADD_MONTHS (date_in, -12), 'YYYY-MM-DD');
   ELSIF frequency_in = 'Q'
   THEN
      RETURN TO_CHAR (ADD_MONTHS (date_in, -3), 'YYYY-MM-DD');
   ELSIF frequency_in = 'M'
   THEN
      RETURN TO_CHAR (ADD_MONTHS (date_in, -1), 'YYYY-MM-DD');
   END IF;
END;

Hmmm. Methinks there's some repetition of logic in there. CASE expression to the rescue!

BEGIN
   RETURN TO_CHAR (
             CASE frequency_in
                WHEN 'Y' THEN ADD_MONTHS (date_in, -12)
                WHEN 'Q' THEN ADD_MONTHS (date_in, -3)
                WHEN 'M' THEN ADD_MONTHS (date_in, -1)
             END,
             'YYYY-MM-DD');
END;

Now I have a single RETURN statement (that always makes me breath a sigh of relief when I have to debug or maintain a function). But wait! I still see some repetition. Let's take another pass at this one.

BEGIN
   RETURN TO_CHAR (
             ADD_MONTHS (
                date_in,
                CASE frequency_in
                   WHEN 'Y' THEN -12
                   WHEN 'Q' THEN -3
                   WHEN 'M' THEN -1
                END),
             'YYYY-MM-DD');
END;

Now all repetition has been removed and CASE expression simply converts a period type to a number of months to "go back."

You can probably see that using CASE expressions is unable to result in some massive reduction in code volume (same with qualified expressions).

But:

• Every little bit counts.
• The more you get into the habit of paying attention to unnecessary code and finding ways to get rid of it, the more examples you will find.

Well, I bet you've got your own ideas for writing lean PL/SQL code. Let me know!