I didn’t realize that Oracle pl/sql and sql treat varchar2 empty strings as null values. Couldn’t figure out why an IF statement was returning a particular value. Apparently, this is common knowledge, see
Jeff Kemp and Paweł Barut on the topic.

Furthermore, within PL/SQL, you can assign an empty string to a variable, and then a test won’t recognize it.

Before I got to those sites, I had cooked up a similar test in SQL, see below. Pawel’s blog shows that PL/SQL will treat CHAR empty strings as NOT NULL, which appears to still be in the case in 11GR2 when I re-ran his snippet. So things are still inconsistent…

CHAR and VARCHAR2 in SQL

SQL> create table foo (f1 char(3), f2 varchar2(3));

Table created.

SQL> insert into foo values ('HEY', 'SAY');

1 row created.

SQL> insert into foo values (null, null);

1 row created.

SQL> insert into foo values ('', '');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from foo where f1 is not null;

F1  F2
--- ---
HEY SAY

1 row selected.

SQL> select * from foo where f2 is not null;

F1  F2
--- ---
HEY SAY

1 row selected.

SQL> select * from foo where f2 is null;

F1  F2
--- ---



2 rows selected.

SQL> select * from foo where f1 is null;

F1  F2
--- ---



2 rows selected.

SQL>

Here are the pl/sql examples I tried.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2  my_var char(2);
  3  begin
  4  my_var := '';
  5  if my_var = '' then
  6  dbms_output.put_line ('An EMPTY STRING!');
  7  else
  8  dbms_output.put_line ('What is it?');
  9  end if;
 10* end;
SQL> /
What is it?

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

  1  declare
  2  my_var varchar2(2);
  3  begin
  4  my_var := '';
  5  if my_var = '' then
  6  dbms_output.put_line ('An EMPTY STRING!');
  7  else
  8  dbms_output.put_line ('What is it?');
  9  end if;
 10* end;
SQL> /
What is it?

PL/SQL procedure successfully completed.
Advertisements