DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Othmar Lippuner

Othmar has posted 1 posts at DZone. View Full User Profile

PL/SQL treads zerolength string as NULL values

08.22.2012

| 2686 views |

<code>
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;
</code>

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

Tags: