Tuesday, December 6, 2016

PL/SQL 201: Ensuring backward compatibility in your PL/SQL code

Unlike other code in the stack, database-level programs such as those built in PL/SQL, tend to have a very long half-life. In part, that's because companies are far less likely to change their database technology than user interface language.

And that means that it is especially important for Oracle Database programmers to prioritizing writing maintainable, well-documented programs. It is also critical that as we make changes to our code base, we don't break programs that have been working for years.

In other words, whenever possible, enhanced code should be backward compatible with earlier versions of code. That's called backward compatibility. In this post, I will explore features of PL/SQL that make it easy to ensure your latest and greatest code has maximum compatibility with "the past."
If a tree falls in a forest and no one is around to hear it, does it make a sound?
You've heard that before, right? Well, how about this one:
If you write a program and no one ever uses it, did you really write that program?
I ask myself this a lot. I've written hundreds of "demo" programs over the years, for trainings, for play, for libraries. But, hey, maybe nobody ever used them or ran them. Did I ever really write it? Oh, sure I did! Did it really matter? Maybe just to me.

Anyway, chances are that you don't write a program unless it is going to be used. And as soon as "someone" uses your code (it could even by you, with a new packaged procedure calling an existing subprogram), you've implicitly signed a contract with that person (or other program unit), stating:
I will do everything in my power to ensure that as I make changes in the future, I will honor how the program works for you now.
So let's see how we can keep our promises, by taking a look at what I just had to do today to my code.

Most of the production PL/SQL code I've written since 2010 is for the PL/SQL Challenge, a website offering weekly quizzes on SQL, PL/SQL and more (try it out!). The backend code uses the "qdb" prefix for "Quiz DataBase". And I created a package to manage player information.

You can play weekly quizzes competitively or just for fun. So I created the set_competitive_status procedure to toggle between them. In other words, the first time it is called for my user ID, it sets up to compete (my answers are ranked). The next it is called, my status is set to "for fun only."

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

And for six years it worked just fine. In other words, it is used, both in the Oracle Application Express "front-end" and in other packages. Here's an example from the backend:

PROCEDURE qdb_process_answers
   AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in          => 1506,
      competition_id_in   => 15709);
END;

And from APEX:


We are now, though, working on a new "skin" for the PL/SQL Challenge: the Oracle Dev Gym. This site has a different flow when taking a quiz, and I needed to be able to force the competitive status to "for fun only" - even if it was the first time, even if it was already set that way. In other words, my "toggle" procedure should not toggle.

First, let's see what happens if I push ahead somewhat blindly, paying no attention to the past. "Hey, no problem, I will just add a parameter!"

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

I compile the package with the modified specification. No errors! Exciting! Even before I get to changing the implementation of this enhancement, though, I notice that the QDB_PROCESS_ANSWERS procedure has an INVALID status. That makes sense - I changed a program unit (QDB_PLAYER_MGR) on which the procedure relies.

No worries. I will just recompile the procedure. But when I do, I see this error:

PLS-00306: wrong number or types of arguments in call to 'SET_COMPETITIVE_STATUS' 

Um, still no worries? Well....I guess not. I will just change QDB_PROCESS_ANSWERS to pass a Boolean value for the third argument:

PROCEDURE qdb_process_answers AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in           => 1506,
      competition_id_in    => 15709,
      force_non_compete_in => FALSE);
END;

Now it compiles without errors. Problem solved.

No! No! No! That is almost certainly the wrong thing to do. Why?

1. This procedure does not need the "force non compete" functionality. It's fine the way it is.

2. What if there are hundreds of invocations of this procedure in my code base? Am I going to search them all out (which I can do quite nicely with PL/Scope) and change each one, even though they do not need the new functionality?

3. Even worse, am I going to deal with the outraged howls of other developers on my team whose code now will not compile?

So what can I do instead?

1. Provide a default value for the new parameter: if it is a trailing IN parameter, then I can assign a default value, and all existing invocations of that subprogram will recompile successfully:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN DEFAULT FALSE);
END;

2. Create an overloading of that procedure (same name, different parameter list):

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

Now, when QDB_PROCESS_ANSWERS is recompiled, the PL/SQL compiler will automatically determine which of the SET_COMPETITIVE_STATUS subprograms "match" the invocation in the procedure.

3. Create an entirely new procedure for this new requirement, as in:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE turn_off_ranking (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

So which should you do? It depends (when doesn't it "depend"? :-) ).

You will not be able to simply add a parameter with a default value if the new parameter is an OUT or IN OUT parameter.(only IN parameters can have default values). In this case, you will need an overloading. For example:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      new_status_out OUT VARCHAR2);
END;

If adding that single new IN parameter actually means making a whole lot of changes in the existing procedure, it will likely make the most sense to create a new subprogram dedicated to that strikingly different algorithm.

To Conclude (and offer some additional advice)

1. When changing the header of a subprogram already in use, honor the current signature, and do everything you can to avoid having to change existing invocations to match the new signature. Use default parameter values or overloading. Which, by the way, is another reminder of a baseline best practice for PL/SQL: put all your procedures and functions in packages. Do not create schema-level procedures and functions. You can't overload at the schema level.

2. Determine whether the changes made to the implementation of the subprogram (its executable section) may affect the way the subprogram is currently used. For example, if it is a function that is executed from within a SQL statement, then you could cause all sorts of problems by inserting a SQL statement into the function, especially non-query DML (inserts, updates, deletes).

3. If a subprogram that is currently in use is being replaced entirely by a new, shinier, better version, mark the original subprogram as deprecated with the DEPRECATE pragma (new to 12.2).


Tuesday, November 15, 2016

PL/SQL Programming Joke #2: Don't Use Built-In Packages, Because - Definer Rights

As my 25th winter in Chicago approaches (and after the 2016 elections), I attempt to cheer myself up with jokes. Programmer jokes.



Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. 

Here's my second joke for your enjoyment. And just in case you have any doubts, this is a true story. As in not "based on a true story." True. All the way through.

After I left Oracle Corporation back in 1992, I spent a few years consulting before I wrote the first edition of Oracle PL/SQL Programming (and my life changed big time). For about six months, I commuted to downtown Chicago to work at an insurance company.

I was, as you might expect, part of the development team, writing PL/SQL packages and building user interfaces in Oracle's wonderful SQL*Forms 3.

This particular insurance company had been acquired by another, even larger insurance company. And it turned out that all the DBAs worked in an entirely separate department in the parent company. As you might expect, that amount of physical and organization distance did not contribute to good relations between the developers and DBAs.

Well, I'd been there for a month or two, when I came into work Monday morning to find that chaos reigned. Almost of the code was broken and would not compile successfully. 

The problem?

The DBAs had, over the weekend, revoked privileges on all the built-in packages (DBMS_SQL, UTL_FILE, DBMS_OUTPUT, DBMS_UTILITY, etc.) from our development schemas.

Yes, that would make it hard for program units to compile.

Why would a DBA take such drastic action? They were eager to explain:
When you execute a stored subprogram, it uses the privileges of the defining schema. So when you run a subprogram in a package owned by SYS (like all the built-ins), you are running with SYS authority. That is a major security hole, and we plugged it.
Hmmmm. Well, gee, that sounds kind of reasonable so maybe….wait just a minute. That might "sound" reasonable, but it also sounds like absolute nonsense. There is no way that Oracle would have designed the database, and the built-in packages that are necessary for building our apps, with such a security problem.

And once again, we find a nugget of truth inside this ludicrous action: it is absolutely true that when you compile a program unit using definer rights (which happens when you include the AUTHID DEFINER clause in the program unit header, or when you leave out the AUTHID clause entirely), the privileges of that defining schema (the owner of the program unit) will be used to resolve references to database objects and therefore control what you can do when you call that program.
The AUTHID clause was added to PL/SQL in Oracle8i. We were on Oracle8 at the insurance company. So no AUTHID and what does that mean? That all of the code we (all PL/SQL programmers, all of us) wrote in that version of Oracle were "definer rights" programs. So the DBAs were right?

No, no, no!

OK, it's true that the AUTHID clause was not available to PL/SQL programmers. That has nothing to do, however, with how Oracle itself could set up its built-in packages. And so, of course, packages like DBMS_SQL executed with the privileges of the invoker, not the definer. You could not drop data dictionary views using DBMS_SQL, not in Oracle7, Oracle8 or any other version of the Oracle Database.

Silly DBAs. We got them to reverse their revocation of privileges that day. Developers 1 - DBAs 0.
Hey, but if you'd like to test your DBA, ask them to grant you EXECUTE authority on a package named DBMS_SYS_SQL because you need to write some dynamic SQL method 4. 

DBMS_SYS_SQL is the package that DBMS_SQL itself calls to perform its dynamic SQL operations, and if you use that package, you will execute SQL statements with the privileges of SYS.

So if your DBA says "OK, you can use DBMS_SYS_SQL"....then quick make sure your manager knows that person is in drastic need of training and/or mentoring.

Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, do not use any built-in packages. 'Cause they're owned by SYS and therefore you can....

NO

That was a joke, OK? If you have execute authority on a package owned by SYS, then you can use it.

Monday, November 14, 2016

PL/SQL Programming Joke #1: Don't Use Packages, Says My DBA

As my 25th winter in Chicago approaches, I attempt to cheer myself up with jokes.

Programmer jokes.

Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.

Here's my first joke for your enjoyment.

No Packages for You!

Packages should be the foundation, the building blocks of any PL/SQL-based applications. I believe that you should not create schema-level procedures and functions, but instead define them as subprograms in one or more packages.

So you can imagine it came as quite a shock to me when an attendee at one my of trainings came up over the break and said to me:
My DBAs won't let me put my code in packages. Everything has to be defined as a procedure or function at the schema level. They tell me that packages take up too much memory and so they cannot be used.
Wow! Packages take up too much memory....

Who would've thought it?

Now, as with most lies, exaggerations and extremely bad advice, there is a small kernel of truthiness that led this person's DBA to take this outlandish position.

That small kernel is the fact that when you invoke any subprogram of a package, the entire package is loaded into SGA memory.

Given this fact, it is certainly possible to imagine a worst-case scenario about which a DBA should be concerned.

Worst case scenario: All of the backend, PL/SQL code for the application is implemented in a single, enormous package, containing hundreds - no, thousands - of subprograms. So to run anything in that backend requires that Oracle load all the partially-compiled code into the SGA, consuming memory unnecessarily and perhaps causing other data in the SGA to be pushed out of the cache, through application of the least recently used algorithm.

I hope you would all agree quite readily that just because you can come up with a worst-case scenario, you should not necessarily reject the feature involved in that scenario.

No, you just need to make sure you utilize the feature properly.

So let's revisit the DBA's concern: "Packages use too much memory."

What he was really saying was: "If you only need to run procedure X, why use up memory to also load procedures Y and Z, as well?"

This is a valid concern - if your package is filled up with subprograms that support a wide range of disconnected requirements. Suppose, on the other hand, that your package is very focused and contains only those subprograms related to a specific area of functionality.

It is, therefore, quite likely that if you are running procedure X, you will also soon need to run procedures Y and Z.

If these three procedures are not in a package, then they have to be loaded and managed separately in the SGA. They will require almost exactly the same amount of memory as the three procedures in the package, but Oracle will have to consume more CPU cycles.

In other words, Oracle's memory management for packages - loading the entire package into memory - should help your application run more efficiently, and certainly not consume memory unnecessarily when packages are constructed properly.

So by all means use packages but follow these guidelines when doing so:

  • Create lots of small, tightly-focused packages. Group all related functionality in the same package.
  • Minimize dependencies between packages (especially in the package specification).
  • When a package gets big, check to see if its scope (the functionality implemented in the package) has broadened. If so, consider breaking up that single package into several, smaller packages.
Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, don't forget to put all your code in schema level procedures and functions. That way you will use less memory and the Schema Browser in SQL Developer will be about 100 miles long.

That was a joke, OK? All code in packages!

PL/SQL Programming Joke #3: Need to make my code compile faster!


As my 25th winter in Chicago approaches (and after the 2016 elections),, I attempt to cheer myself up with jokes. Programmer jokes.

Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision. 

Here's the most recent "joke" I heard from a developer, via an email back in July:
We just encountered a PL/SQL performance problem after migrating a database from 10g to 11g and I tought it might interest you. I isolated the problem using  the 10046 traces and DBMS_PROFILER. So I was able to reproduce the problem with a very simple PL/SQL testcase, but I cannot explain it.
He then pasted in 439 lines of code and output. So this is the "very simple" testcase? It might be, but still I wrote back:

"It may be simple, but it's long and I'd appreciate it if you would summarize for me what you believe you have discovered."

This way, at least, I didn't have to feel the least bit obligated to tangle with his problem until he replied.

But his response was even better and more entertaining than I'd hoped:
I just found out the cause of the strange behavior...Somebody had set the PLSQL_OPTIMIZE_LEVEL to 1 instead of 2 at the database level so that a massive recompilation of all packages would go faster.
When the optimization was set back to 2, the performance problem he noticed earlier disappeared. Hurray!

So now let's go back and parse that paragraph:

"Somebody had set" the optimization level - I sure hope that somebody else knows exactly who that original somebody is. You'd like to think that there aren't too many people with the authority to change the default optimization level on a database instance.

"…so that a massive recompilation of all packages would go faster."

Ah, that's just too delicious! Again, an element of truth that goes a loooong way towards mucking up application performance.

It is true that the higher you set the PL/SQL optimization level, it takes longer for your code to compile, because the compiler is doing more work analyzing and applying transformations to your code to improve runtime performance.

Hey, let's shrink that paragraph down to its essence:
Spend more time compiling your code so that code runs faster for your users.
Sounds like an excellent tradeoff. We wait a little longer for code to compile (and would we even notice the difference? Unlikely.) and in return our users are happier.

Not for that DBA, though. Maybe he had a hot lunch date or had signed up for one of Tom Kyte's amazingly popular webinars and trainings and that was about to begin. At which point he might learn about how important it is to keep that optimization at least at 2. 

Whatever the case, all he knew was that he needed to get that code complied fast.

I just hope it wasn't on a production instance.

Know Any Good "Jokes"?

I bet you've run into all sorts of hilariously truthy stories of developers or DBAs doing the most absurd things for the only slightly less absurd reasons. Please share them with me, either via comment below or by sending an email to steven dot feuerstein at oracle.com. 

In the meantime, don't forget to set the optimization level to 1. Or maybe 0. That way your code will compile faster. 

Giving you less time for that extra cup of coffee. Which is probably a good thing.

That was a joke, OK? Optimization level at 2 or higher, please! 


Originally published in ODTUG's Confessions of a Quick and Dirty Programmer series

Monday, November 7, 2016

On the importance of keeping algorithmic logic separate from display logic

On the PL/SQL Challenge, all times are shown in the UTC timezone. Weekly quizzes end on Friday, midnight UTC. So I recently decided that when I display the time that the quiz starts and ends, I should add the string "UTC".

Our quiz website is built in Oracle Application Express 5.0, so I opened up the process that gets the date and found this:

DECLARE
   l_play_date   DATE
      := qdb_quiz_mgr.date_for_question_usage (:p46_question_id);
BEGIN
   :p46_scheduled_to_play_on := TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI');

"OK, then," says Steven the Fantastic Developer to himself. "I know exactly what to do."

And I did it:

DECLARE
   l_play_date   DATE
      := qdb_quiz_mgr.date_for_question_usage (:p46_question_id);
BEGIN
   :p46_scheduled_to_play_on := 
      TO_CHAR (l_play_date, 'YYYY-MM-DD HH24:MI') 
      || ' UTC';

Ah, PL/SQL and APEX - so easy to use! :-)

Now, there are lots of things you could say about the change I made above, but here's one thing that is undeniably true:
P46_SCHEDULED_TO_PLAY_ON will never by NULL.
Right? Right. Of course, right.

So that's fine, though. Because that's what I wanted: to have "UTC" always show up, and there's always going to be a date when the question is used in a quiz, right?

Well, no. In fact, this code is part of our Quiz Editor page, and on that page we offer a button that allows you to easily and quickly schedule a quiz for play.

But only if it hasn't already been scheduled. If it hasn't already been scheduled, then the date is, oh wait, um, NULL.

And that's why we have a condition on that button:


And that's why Eli Feuerstein, the fine fellow who does most of the work on the PL/SQL Challenge and it's cool new sister, Oracle Dev Gym, reported an issue with this page:
The Schedule button never appears on the page!
Awwwwwwwwwww......

So two lessons learned (re-learned, and learned again, then forgotten, then re-learned, then learned again....):

1. When I am about to make a change, ask myself: "What impact might this have?" 

In the world of APEX, it's pretty easy: search for the string "P46_SCHEDULED_TO_PLAY_ON" and see how it is used in the application. 

2. Keep completely separate the data (in this case, APEX items) that is used for algorithmic logic and the data that is used for display purposes.

I could create a separate item for display purposes, or a different item to be used in conditions and other PL/SQL blocks. 

But I should not use the same item for both.

Thursday, November 3, 2016

PL/SQL 101: Less code is better - avoid unnecessarily complex algorithms

With programmers new to PL/SQL (and SQL), it is not uncommon to find that they overcomplicate things, writing more code than is necessary, and putting too much logic into PL/SQL.  That problem can then be compounded by accidentally getting the "right answer" based on inadequate testing and test data.

So it is always good to be reminded: 
Do as much work as you can in SQL, and then finish up in PL/SQL. 
Where "finish up" means do whatever is appropriate within the database, and then make that available to whatever language is being used to write the UI!

A recent quiz on the PL/SQL Challenge explored this topic, and I offer it below as a learning exercise via blog post.

Suppose I have a table and dataset as follows:

CREATE TABLE plch_animals
(
   animal_id     INTEGER PRIMARY KEY,
   animal_name   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_animals (animal_id, animal_name)
        VALUES (1, 'Bonobo');

   INSERT INTO plch_animals (animal_id, animal_name)
        VALUES (2, 'Cockatoo');

   INSERT INTO plch_animals (animal_id, animal_name)
        VALUES (3, 'Spider');

   COMMIT;
END;
/

And I need to write a program that produces the following output:

Animals in Alphabetical Order
Bonobo
Cockatoo
Spider

Let's take a look at some ways to achieve this objective (some of them distinctly sub-optimal, holding off the best till last):

#1. Accidental Success Through Bad Test Data

DECLARE
   l_count   INTEGER;
   l_name    plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   SELECT COUNT (*) INTO l_count FROM plch_animals;

   FOR indx IN 1 .. l_count
   LOOP
      SELECT animal_name
        INTO l_name
        FROM plch_animals
       WHERE animal_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

You see the desired output, but only because the animal IDs happen to ascend in exactly the same sequence as an alphabetical ordering of the animal names.(see line in blue).

You certainly do not want to ever assume this will be the case with real data. Even if you look at the data today and can confirm that pattern. That's just for today. A warning sign that this code is problematic is the SELECT COUNT(*).

You should generally not need to do a separate query just to control the number of iterations of a loop. A cursor FOR loop will usually take care of that for you.

#2. Too Much SQL, Not Enough Data

DECLARE
   TYPE animal_ids_t IS TABLE OF plch_animals.animal_id%TYPE;

   l_animal_ids   animal_ids_t;
   l_name         plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   SELECT animal_id
     BULK COLLECT INTO l_animal_ids
     FROM plch_animals;

   FOR indx IN 1 .. l_animal_ids.COUNT
   LOOP
      SELECT animal_name
        INTO l_name
        FROM plch_animals
       WHERE animal_id = indx;

      DBMS_OUTPUT.put_line (l_name);
   END LOOP;
END;
/

Oh my. This choice is similar to #1, in terms of the basic, flawed assumptions (the animal IDs happen to ascend in exactly the same sequence as an alphabetical ordering of the animal names).

But it's even worse because I fetch all the animal IDs via BULK COLLECT into an array, then when I loop through the array, I execute a single row fetch to get the name.

And then it's even worse worse because the only reason this works is that the ID values happen to align with names in alphabetical order. So the results "look" OK when the test is run with this pathetic set of test data, but in the real world? Ugh.

That's a waste of code, PGA memory and CPU cycles.

#3. Having Too Much Fun With Collections

DECLARE
   TYPE animal_ids_t IS TABLE OF plch_animals.animal_id%TYPE
      INDEX BY plch_animals.animal_name%TYPE;

   l_animal_ids   animal_ids_t;
   l_index        plch_animals.animal_name%TYPE;
   l_name         plch_animals.animal_name%TYPE;
BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   FOR rec IN (  SELECT *
                   FROM plch_animals
               ORDER BY animal_name DESC)
   LOOP
      l_animal_ids (rec.animal_name) := rec.animal_id;
   END LOOP;

   l_index := l_animal_ids.FIRST;

   WHILE l_index IS NOT NULL
   LOOP
      DBMS_OUTPUT.put_line (l_index);

      l_index := l_animal_ids.NEXT (l_index);
   END LOOP;
END;
/

I very much enjoy collections (PL/SQL's version of arrays) and use them all over my code. And I especially appreciate string-indexed associative arrays, like I use above. But, really, there is such a thing as too much.

I use a cursor FOR loop to grab the rows from the animals table, and load the ID into my array, using the name as the index value. Then I use a WHILE loop to iterate through that array, simply displaying the index value. I never even use the ID!

It gets the job done, and sure performance will be fine unless you are looking at a very large volume, but this code is downright bewildering.

#4. SQL Simple

SELECT animal_name FROM plch_animals
 ORDER BY animal_name

Doesn't get much simpler than that. And if you need it inside PL/SQL....

#5. PL/SQL Simple

BEGIN
   DBMS_OUTPUT.put_line ('Animals in Alphabetical Order');

   FOR rec IN (SELECT animal_name FROM plch_animals
                ORDER BY animal_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.animal_name);
   END LOOP;
END;

Lessons Learned

1. Let SQL do the heavy-lifting, as much as possible (not that there was anything very "heavy" to lift in this exercise!)

2. Don't over-complicate matters.

3. Make sure your test data has enough volume and variety to truly exercise your algorithm.

4. If you find yourself thinking "Does it have to be this complicated?", almost certainly the answer is a resounding "No!" and you should take a step back, challenge your assumptions, and see how you can simplify your code.

And don't forget:

a. Follow me on Twitter: @sfonplsql
b. Subscribe to my YouTube channel: PracticallyPerfectPLSQL
c. Check out the PL/SQL home page: oracle.com/plsql

Tuesday, November 1, 2016

PL/SQL 101: Why can't I display a Boolean value with DBMS_OUTPUT.PUT_LINE?

DBMS_OUTPUT. PUT_LINE is the built-in procedure that PL/SQL developers use to display output on the screen.

Let's watch it do it's thing on LiveSQL:


So I displayed a string, a date, a date converted to a string, and a number. Cool. 

Now let's display a Boolean value (TRUE, FALSE or NULL):


Ouch! It did not like a Boolean value, that's for sure. But why not? To figure that out, we need to take a look at the specification of the DBMS_OUTPUT package.

That's easy in SQL Developer: just right-click and choose Popup Describe. 



Searching for "procedure put_line", I see:

create or replace package dbms_output authid definer as
...
  procedure put_line(a varchar2);

Huh. That's weird. It only accepts a string. So how could it display a number and a date with no problem, but then choke on a Boolean?

It all has to do with implicit conversions. Generally, Oracle Database in both SQL and PL/SQL will automatically and implicitly convert a value from one data type to another "when such a conversion makes sense." This matrix shows the implicit conversions that are supported, but I can summarize for you in terms of this post:

1. Dates, timestamps and numbers can be implicitly converted to strings.
2. Booleans cannot be converted to strings. 

In fact, the BOOLEAN datatype doesn't even appear in the matrix. That is probably in part because BOOLEAN is a PL/SQL-specific datatype; it's not supported at all in Oracle SQL.

Does that mean that it will never be possible for DBMS_OUTPUT.PUT_LINE to display a Boolean? Not at all. For example, the PL/SQL development team could add an overloading for PUT_LINE in the DBMS_OUTPUT package:

create or replace package dbms_output authid definer as
...
  procedure put_line(a varchar2);
  procedure put_line(a boolean);

And then they just have to figure out what to display. That's the "funny" thing about the Boolean values of TRUE and FALSE. They are in English. If you are using a different language, should the text displayed for a Boolean value change to the words for TRUE and FALSE in that language?

So many questions, so little time - but no matter how many questions there are, adding an overloading to this built-in package is well out of our control.

So what's a developer to do?

Well, first of all, please do not do this, over and over again throughout your code:


BEGIN
   IF my_boolean
   THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
   ELSIF NOT my_boolean
   THEN
      DBMS_OUTPUT.PUT_LINE ('FALSE');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('NULL');
   END IF;
END;

Instead, why not install a "Boolean Manager" in your environment that allows you to easily display Boolean values (and convert them from/to strings)?



A final note: we recommend that you avoid implicit conversions whenever possible, and tell Oracle Database exactly what you want, and how you want it done. A simple example demonstrating the benefit of being explicit has to do with displaying date values.

When I ask to display a date, as in:

BEGIN
   DBMS_OUTPUT.put_line (DATE '2016-10-31');
END;
/

31-OCT-16

Oracle Database uses the default NLS date format setting for my session to do the implicit conversion. The default format is DD-MON-YY, which honestly very few humans find helpful. But notice in particular that the time component is ignored.

Now let's try it again after changing the session date format:


ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
/

BEGIN
   DBMS_OUTPUT.put_line (DATE '2016-10-31');
END;
/

2016-10-31 00:00:00

So rather than assuming the session date format is what you hope or desire, make it explicit when you  ask to display a date, as in:

BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (DATE '2016-10-31'
      , 'YYYY-MM-DD HH24:MI:SS'));
END;
/

31-OCT-16