Beware of Dealing with Zero-length Strings in PL/SQL

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>