What is the length of a CHAR or a VARCHAR2 variable when its contents are null ?
When you use the LEN function to determine the length of a variable that contains null the result returned is $null. If you try to assign the result of the LEN function to a numeric receiving variable, that variable is unchanged. Finally, if you compare result of the LEN function to another numeric variable, the comparison will always fail as LEN does not return a number. In order to establish whether a variable that accepts nulls has a valid length, you need to first check whether that variable contains null before invoking the LEN function. 1> 2> define c : oracle char(20) allow nulls 3> 4> * the variable clen is initialized to 8 5> define clen : numeric value 8 6> 7> * the LEN function returns $NULL 8> setvar c = $null 9> print “c=”; len(c) 10> 11> * clen is not set by the setvar operation since len(c) returns $null 12> setvar clen = len(c) 13> print “clen=”;clen 14> 15> * len(c) returns a non-numeric 16> if clen = len(c) 17> print ‘equals’ 18> else if clen < len(c) 19> print ‘less
