Thursday, November 20, 2014

Extra space (blanks padded) with data

Most of us experience blank padded with data, when we deal with database, experienced developer will look the data type is varchar or not and it's size defined as per the requirement and fix the issue quickly.

Let me share another experience with extra padded space that may occur even when we carefully select the data type.

We deployed tested code in production, but it got failing since there is extra space in the value when we retrieve from the oracle database table. Same code work fine in SIT environment,

So debugging was found to be easy as it is environmental issue.

We verify the column type in both environment, surprisingly both are char(2).

Then what will be the issue?
why it works fine in SIT not in production, Although we define data type char of size 2, we are getting data as "01    " instead of  "01"

When we dig into deep we realize that even though both are char(2) there is difference in both environment, 

In production it was CHAR(2 CHAR) but in SIT it is CHAR(2 BYTE)

root cause is When neither BYTE nor CHAR is specified, the setting of NLS_LENGTH_SEMANTICS at the time of column creation determines which is used.

so we alter the table column data type and it got working fine :-)