5.11 Implicit casting in SQL

< Day Day Up >

In addition to syntax differences, we should highlight differences in how Oracle and DB2 handle data type casting. Whether you know it or not, Oracle performs implicit casting of data types (as required) while DB2 is strongly typed. Consider the following example:

    [1] create table t1 (c1 number);    [2] insert into t1 ('1')    [3] select * from t1 where c1='1'

In the first line, we create a table T1 which has a numeric column. In line two, however, the character value 1 can be inserted into T1 without error. The value was implicitly casted by Oracle from varchar2 to number. In line three, we have yet another example of implicit casting as the predicate c1 (numeric type) and 1 (character type) are allowed to be compared. Certainly there are many more cases than this. Implicit casting can happen in a variety of cases.

Because DB2 is strongly typed, SQL may have to be slightly rewritten to perform explicit casts. Implicit casting can be good and bad. It certainly makes SQL programming easier, but it can also be dangerous, and incurs some overhead, which is why purists will discourage its use.

Following the previous example, we can rewrite the series of SQL as follows:

    [1] create table t1 (c1 INT);    [2] insert into t1 (1)    [3] select * from t1 where c1=1

Of course, these are the easiest cases to solve because it is obvious where the problem lies. Another common place where implicit casting must be resolved is in functions and operators. For example:

    create procedure echo_input (v_num in numver(9,0), v_echo out varchar2)    as    begin        v_echo := ' Input number is: ' || v_num;    end;

In this case, the concatenation operator (||) in Oracle will automatically cast v_num to varchar2 so that the strings can be combined. In DB2, v_num will have to be explicitly cast to a character type. If you convert this code through MTK, it will help you identify all places where implicit casting is occurring by providing code to perform explicit casting. If you decide to perform a conversion without tools, it will take more time to find them.

Based on past experience, among the most time consuming porting activities will revolve around converting implicit casting to explicit casting in dynamic SQL. Because dynamic SQL, in general, cannot be fully resolved until runtime, MTK is not able to determine the proper casting functions to apply.

Consider the following example:

    create procedure dyn_cast    as        val varchar2(100) := '100';    begin        EXECUTE IMMEDIATE 'CREATE TABLE T1 (C1 number)';        EXECUTE IMMEDIATE 'INSERT INTO T1 VALUES ( ''' || val || ''')';    end;

Here we create a table T1 with a numeric column C1. When you convert to DB2, the procedure will build with very few changes. The final SQL statement as submitted to the database engine will look like this:

    INSERT INTO T1 VALUES ('100');

It will fail, however, at runtime because implicit casting is occurring in the INSERT statement.

Dealing with implicit casting will be quite troublesome at first, but as you troubleshoot these problems, you will learn to quickly identify these situations. The upside is that it will ultimately make your applications cleaner and in some cases, perform better (especially in high volume SQL statements).

< Day Day Up >