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;
String length is undefined
Where as non-empty strings perform as excepted
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.