Skip to main content

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). :-)

Comments

  1. We're hoping to put some of these common snippets in an open source utility: https://github.com/OraOpenSource/oos-utils For those reading and have ideas, submit an issue with your feedback...

    ReplyDelete
  2. @Martin/Steven: Yeah, or you can just head over to the Alexandria Utility Library for PL/SQL, which already has all sorts of stuff like this - https://github.com/mortenbra/alexandria-plsql-utils . Since the whole point of the blog post is "reuse stuff"... :-)

    ReplyDelete
  3. @Martin: checked the site however I think that you need to ask yourself and the people who are contributing ideas one important question. Which database version you want to support with this activity? Lot of companies are still using 10g, some are still using even 8i. Based on the answer people can think about what they would like to have...

    @Steven: great article. If you are working long enough with this stuff you just get lazy just writing the same stuff all over again and again. Personally I try to put all repeating stuff to some package (currently one, which is not very wise probably), because I don't want to reinvent a wheel each time I change the project. I'm going crazy when I see that somebody is copying logging procedure from one package to another or when they are putting to SVN multiple version of files like file_01.sql, file_02.sql etc.

    ReplyDelete
  4. Steven shows what ideally should be a supplied PL/SQL built-in overload for To_Char() with what boils down to this body:

    return
    case b
    when true then 'true'
    when false then 'false'
    else 'null'
    end;

    I filed ER 5637635 to this effect on 01-Nov-2006. It's sitting in a queue. Feel free, everybody, to work through Oracle Support to get your Customer name attached to this ER.

    ReplyDelete
  5. I use this simple SQL Dev template:
    dbms_output.put_line(case [b] when true then 'true' when false then 'false' else 'null' end);

    The square brackets will be automatically hilighted when the template is selected

    ReplyDelete
  6. A built-in (yet limited to returning number and not string) function that can be used is SYS.DIUTIL.BOOL_TO_INT:

    DECLARE
    l_boolean BOOLEAN := TRUE;
    BEGIN
    dbms_output.put_line(sys.diutil.bool_to_int(l_boolean));
    END;
    /

    ReplyDelete

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