Over a million developers have joined DZone.

PL/SQL treads zerolength string as NULL values

·

There is no such thing as a zero-length string in Oracle PL/SQL. A zero-length string would always result in a NULL-String. The code above tests and proves this.

Cautionary statement

You should not use a comparison operator like =, <>, <, > before asserting that both strings used in the expression are not NULL. See the further comment on:

http://www.knowledgeware-consulting.ch/plsql/beware-of-dealing-with-zero-length-strings-in-plsql 

If your coding in different languages is parallel, this statement is especially important to be aware of.

Regards,

Othmar Lippuner

SQL Reporting specialist 

 

 

 

 

 


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;

Topics:

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}