Oracle PL/SQL Best Practices

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