Problem

Unnecessary Data Type Conversions

It's a very small improvement, no doubt! But when you implement an algorithm, you might want to make sure you get everything out of your Oracle database, especially if it's so easy to do. You'll see that once you remember it, it's not big deal to use it.

Assume you got a variable declared as VARCHAR2 and you want to put content in it. Look at these examples:

DECLARE
   v VARCHAR(5);
BEGIN
   -- Slow because it's converted implicitly
   v := 42;

   -- A bit better but still slow, it's converted explicitly
   v := TO_CHAR(42);

   -- Fast because no conversion is done
   v := '42';
END;

The same goes for numbers, there's even a small detail which a lot of developers never thought about, let's have a look at these lines of code:

DECLARE
   n NUMBER;
BEGIN
   -- Slow because it's converted implicitly, not the same data type!
   n := n + 42;

   -- Fast because nothing is converted, same data type
   n := n + 42.0;
END;

Solution

Recipe #1 - Avoid conversions when possible

This code box shows the proper use of variable assignment:

DECLARE
   n NUMBER;
   c VARCHAR(5);
BEGIN
   n := n + 42.0;
   v := '42';
END;