Skip to main content

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!

Comments

  1. You've said here that copying/pasting code here is bad, and I could not agree with you more. Real life question for you though: I have non-technical stakeholders that want me to copy and paste code into a new package, because it's easier/faster (in the short-term) than changing several pieces of Java that point to multiple Pl/SQL packages. How can I convince them that this is a terrible idea?

    Your blog is awesome! Thanks!!

    --Kaley

    ReplyDelete
    Replies
    1. Thanks for your kind words, Kaley.

      This is very interesting. A couple of thoughts come to mind:

      * Why are non-technical stakeholders even aware of PL/SQL packages and impact on Java code? :-)

      * And why would they have any sort of veto power over the technical team itself?

      * Generally this is a class example of "Pay me now or pay me later." The small amount of effort to change those Java classes now will be much smaller than the time to create the copied packages, then maintain both over time.

      * Perhaps more importantly for them: the more code you have, especially duplicates, the chances are that bugs will creep into the application, that you will fix things in one place, but not the other, and end up with much lower user satisfaction.

      * Worse case, agree to create the new packages, but have them simply be "pass throughs" to the existing code base. So no duplication, just another similar API (package specification). Minimum impact on you to maintain/fix such code.

      Delete
    2. I like "pass throughs" - every application that want's to call procedures from "my" application gets its own package. Within these packages calls are forwarded to the real code. This way it is easy to control access because every application only gets a GRANT EXECUTE on its own package.
      And for those callers that still want return codes it is easy to provide them.

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel