21 September 2006

CHAR and VARCHAR Diffrences In Oracle

CHAR and VARCHAR types are not the same whereas some thinks they are. When you create a CHAR variable with some size and you do not fill the variable completely, Oracle fills the empty slots with blank characters. So if you want to compare a CHAR string with NULL you can confuse. Follow the ex:

Connected to Oracle Database 10g Enterprise Edition Release
Connected as SYS

  2    char_string    CHAR(5) := '';
  3    varchar_string VARCHAR(5) := '';
  4  BEGIN
  6    IF char_string IS NULL THEN
  7      dbms_output.put_line('char_string is null');
  8    END IF;
 10    IF varchar_string IS NULL THEN
 11      dbms_output.put_line('varchar_string is null');
 12    END IF;
 14    IF rtrim(char_string) IS NULL THEN
 15      dbms_output.put_line('rtrim(char_string) is null');
 16    END IF;
 18  END;
 19  /

varchar_string is null
rtrim(char_string) is null

PL/SQL procedure successfully completed


No comments: