Interesting Decode!

If you have written SQL queries for Oracle database, you must have used Decode 'n' number of times.

SQL> variable x number;
SQL> exec :x := 1;

PL/SQL procedure successfully completed.

SQL> select decode(:x,null,1,:x) from dual
2 /

DECODE(:X,NULL,1,:X)
--------------------
1

Now what if you pass in a char type to x ?

SQL> variable x varchar2(3);
SQL> exec :x := 'A';

PL/SQL procedure successfully completed.

SQL> select decode(:x,null,1,:x) from dual;
select decode(:x,null,1,:x) from dual
*
ERROR at line 1:
ORA-01722: invalid number


SQL>

Wait!, whats happening here ? This because the SQL engine recognizes the decode function to return a number (because of presence of 1 which is the only thing which can be determined during parse) . So when you pass in a char value, Oracle internally tries to convert it to number by calling to_number and thus ORA-01722.

Slight change in the SQL and it works like a charm!.

SQL> select decode(:x,null,'1',:x) from dual;

DECODE(:X,NULL,'1',:X)
--------------------------------
A

SQL>

The bottom line being, just be doubly sure about what goes into a decode at the runtime, and have your decode return the same type by default. Else, you have a bug in your code which is ready to pop up anytime. You don't want to get a call on a weekend saying that there is a P1!!.