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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…