Name

DAT-11: Beware of and avoid implicit datatype conversions.

Synopsis

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. There are at least two big problems with relying on PL/SQL to convert data on your behalf:

  • Conversion behavior can be non-intuitive. 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 RDBMS-wide parameters, such as NLS_DATE_FORMAT.

You can convert explicitly using any of the following built-in functions: TO_DATE, TO_CHAR, TO_NUMBER, and CAST.

Example

The declaration of the my_birthdate variable is a sterling example of the drawbacks of implicit conversion. 

DECLARE
   my_birthdate DATE := '09-SEP-58';

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) in the parameter initialization file—well out of the control of most PL/SQL developers. It can also be modified for a specific session. A much better approach is: 

 DECLARE
   my_birthdate DATE := 
      TO_DATE ('09-SEP-58', 'DD-MON-RR'),

Benefits

The behavior of your code is more consistent and predictable, since you aren’t relying on something external to your code. Explicit conversions, for example, would have avoided the vast majority of Y2K issues in PL/SQL code.

Resources

bool.pkg : A package to convert between Booleans and strings, since Oracle doesn’t offer any built-in utilities to do this.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset