Smoothing rough edges for productive programming

No language is perfect and while PL/SQL is an incredibly "tight fit" for building applications on top of Oracle SQL (and, of course, Oracle Database), it also is not yet quite perfect.

Maybe in Oracle Database 13(?) - (?) being whichever letter Larry decides will best reflect the main theme of that version....

In any case, in the meantime, and to paraphrase a saying:

You write code with the language you've got, not the language you want.

So the key thing is to maximize your productivity any way you can, all along the way.

And sometimes the steps you can and should take in this area can be very small, but they can still add up.

Case in point: displaying a Boolean value with DBMS_OUTPUT.PUT_LINE.

As anyone who's spent time with PL/SQL knows, DBMS_OUTPUT.PUT_LINE is the procedure provided by Oracle to display text from within a PL/SQL block on your screen.

It accepts a string (and anything that can be implicitly converted to a string) and sends it to the DBMS_OUTPUT buffer. Then when your PL/SQL block completes, the host environment in which you executed that block reads the buffer and shows you the text.

Simple enough.

And that takes us back to:

DBMS_OUTPUT.PUT_LINE accepts a string (and anything that can be implicitly converted to a string)....

As of 12.1, PL/SQL does not support implicit conversion of Booleans (which have just three values: TRUE, FALSE and NULL - well, I guess that's two values and one non-value) into anything at all.

So when I try to display a Boolean using DBMS_OUTPUT.PUT_LINE, all hell breaks loose:

Well, that's a bummer. What's a programmer to do?

What too many of us do is sigh and remember that this happened last week, too, and then spend another minute or so converting the code into this:

DECLARE
   l_boolean BOOLEAN := TRUE;
BEGIN
   IF l_boolean
   THEN
      DBMS_OUTPUT.put_line ( 'true' );
   ELSE
      DBMS_OUTPUT.put_line ( 'false' );
   END IF;
END;

Then we see our value. And then we have wasted our time. Again. And again.

Argh. No. Do not do this. Do not waste your time in this way. Life is too short.

Instead, accept that you will have this experience over and over again unless you take action. In this case, a very small action:

Save that pattern of code somewhere so you can access it quickly and easily the next time you need it.

Now, there are at least two ways to do this.

Save Pattern As Reusable Snippet or Template

If you are using an editor that's a bit smarter than Notepad, it likely allows you to save code snippets, and then grab them as needed.

In SQL Developer, you have choices in this arena: Code Snippet and Code Template.

Code Snippets offer the ability to create sets of code you access via drag-n-drop. Jeff Smith, SQL Developer Product Manager offers a fine explanation of how to use those on his blog.

For this kind of situation, I prefer to use Code Templates, for one simple reason: you can pull the template into your current editor with the auto-complete keystroke (Control-Space by default). This is a much smoother and faster way to suck in small chunks of commonly used code, and keep on coding.

Briefly here are the steps to go through to set up a new code template:

1. Open up SQL Developer Preferences and click on SQL Editor Code Templates under Database:

2. Then click on Add Template, type in a very short name for your template (just a suggestion :-) ) , and then paste in the code:

Save it, close Preferences, and from that point on, when you are writing code, type (in my case) "b2v", press Control-Space and voila! Your code will appear. Nice.

Save Pattern As Reusable Code

You could also save the pattern as reusable code: encapsulate the entire IF statement inside a procedure (in essence, extending the PL/SQL language), and then call that procedure as needed.

CREATE OR REPLACE PROCEDURE display_boolean (
      boolean_in IN BOOLEAN)
   AUTHID DEFINER
IS
BEGIN
   CASE boolean_in
      WHEN TRUE THEN DBMS_OUTPUT.put_line ( 'TRUE' );
      WHEN FALSE THEN DBMS_OUTPUT.put_line ( 'FALSE' );
      ELSE DBMS_OUTPUT.put_line ( 'NULL' );
   END CASE;
END;
/

Or you could get rather "fancy" and build yourself an entire package to help manage Booleans in a consistent manner in your code:

CREATE OR REPLACE PACKAGE boolean_pkg AUTHID DEFINER
IS  
   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   /* Consistency with existing TO_CHAR functions, though 
      of course you still need to preface it with 
      "boolean_pkg". */

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN;

   FUNCTION true_value
      RETURN VARCHAR2;

   FUNCTION false_value
      RETURN VARCHAR2;
      
   PROCEDURE put_line (boolean_in IN BOOLEAN);
END boolean_pkg;
/

CREATE OR REPLACE PACKAGE BODY boolean_pkg
IS
   /* Change strings to language appropriate values! */
   c_true    CONSTANT VARCHAR2 (4) := 'TRUE';
   c_false   CONSTANT VARCHAR2 (5) := 'FALSE';

   FUNCTION bool_to_str (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN
      CASE boolean_in
         WHEN TRUE THEN c_true
         WHEN FALSE THEN c_false
         ELSE NULL
      END;
   END bool_to_str;

   FUNCTION to_char (boolean_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN bool_to_str (boolean_in);
   END;

   FUNCTION str_to_bool (string_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN 
      CASE string_in 
        WHEN c_true THEN TRUE
        WHEN c_false THEN FALSE
        ELSE NULL
      END;

      /* Or you could be "nicer" and support many
         different common values...
      RETURN 
      CASE string_in 
        WHEN string_in IN (c_true, 'T', 'Y', 'YES') THEN TRUE
        WHEN string_in IN (c_false, 'F', 'N', 'NO') THEN FALSE
        ELSE NULL
      END;
      */
   END str_to_bool;

   FUNCTION true_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_true;
   END true_value;

   FUNCTION false_value
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_false;
   END false_value;
   
   PROCEDURE put_line (boolean_in in boolean)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE (bool_to_str (boolean_in));
   END;
END boolean_pkg;
/

Yes, sure, you can copy and paste this code and use it. Be my guest. It sure isn't rocket science. :-) You can also try it out and download it from LiveSQL.

And that's the whole point. It usually doesn't come down to anything elaborate or sophisticated to avoid this repetitive coding disorder. Just a little bit of discipline, getting things set up.

Oh and then remembering how to access your reusable stuff (the name of your snippet/template, the actual existence of that reusable code). :-)