When programming in a certain language there are certain some DOES and DON’T’s. That’s not different in PL/SQL. In TOAD there is a code analysis tool, that parses PL/SQL code and highlights specific problem areas. Trivadis has added some of these rules to their coding guidelines.
One rules says: Never use zero-length strings to substitute NULL.
You can go even a bit further and ask: What is a zero-length string in PL/SQL?
You define a string and initialize it to an empty string:
str := '';
This is legal PL/SQL code, easily swallowed by the compiler. But is str now a zero-length string?
From your experience in other high level programming languages you would expect it to be so, and of course you expect then a zero-length strings to have a length = 0.
Beware, you expectations will be disappointed:
declare str varchar2(128);
begin
str := null;
dbms_output.put_line('sample-1>>' || length(str)|| '<<');
str := '';
dbms_output.put_line('sample-2>>' || length(str)|| '<< !!!');
if length(str) = 0 then
dbms_output.put_line('String length is 0');
else
dbms_output.put_line('String length is undefined');
end if;
dbms_output.put_line
('Where as non-empty strings perform as excepted');
str := 'A';dbms_output.put_line( str || '>>' || length(str));
str := 'Beware of dealing with zerolenght strings in PL/SQL ';
dbms_output.put_line( str || '>>' || length(str)|| '<<');
end;
Output
sample-1>><<
sample-2>><< !!!
String length is undefined
Where as non-empty strings perform as excepted
A>>1
Beware of dealing with zero-lenght strings in PL/SQL >>52<<
A an empty string does not have a length of zero, Up to Oracle 11g, there is no such thing as a zero-length string variable in PL/SQL. After assigning a empty string literal to a string, the string is immediately converted to a NULL-String. A NULL-String variable is a undefined string, and thus also it’s length is undefined, e.g. NULL. Bear this in mind when coding.