Best Practices for PL/SQL Variables - Programmers are (or should be) control freaks
(Page 3 of 5 )
Beware of and avoid implicit datatype conversions.
Problem: PL/SQL performs implicit conversions—but they’re not always what you want.
Sometimes, PL/SQL makes life just too darn easy for us developers. It will, for example, allow you to write and execute code like this:
DECLARE my_birthdate DATE := '09-SEP-58';
In this case, the runtime engine automatically converts the string to a date, using the default format mask.
You should, however, avoid implicit conversions in your code (Figure 4-1 shows the types of implicit conversions that PL/SQL attempts to perform). There are at least two big problems with relying on PL/SQL to convert data on your behalf:
Conversion behavior can be unintuitive PL/SQL may convert data in ways that you don’t expect, resulting in problems, especially within SQL statements.
Conversion rules aren’t under the control of the developer These rules can change with an upgrade to a new version of Oracle or by changing database-wide parameters, such as NLS_DATE_FORMAT.
You can convert explicitly using one of the many built-in functions, including TO_DATE, TO_CHAR, TO_NUMBER, and CAST.
Solution: Perform explicit conversions rather than relying on implicit conversions.
Let’s see how I would move from implicit to explicit conversion of the previous declaration.
This code raises an error if the default format mask for the instance is anything but DD-MON-YY or DD-MON-RR. That format is set (and changed) by a database initialization parameter—well beyond the control of most PL/SQL developers. It can also be modified for a specific session. Amuch better approach, therefore, is:
DECLARE my_birthdate DATE := TO_DATE ('09-SEP-58', 'DD-MON-RR');
Taking this approach makes the behavior of my code more consistent and predictable, since I am not making any assumptions about factors external to my program. Explicit conversions, by the way, would have prevented the vast majority of Y2K issues found within PL/SQL code.
Figure 4-1. Implicit datatype conversions attempted by PL/SQL
Resources
bool.pkg is a package file available on the book’s web site that you can use to convert between Booleans and strings. You will find this code useful particularly since the Oracle database doesn’t offer any built-in utilities to perform these operations.
