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