Re: Tom, how serious is that problem - ltrim/rtrim changed with regards to NULL'ness

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 26 Apr 2002 08:45:58 -0700
Message-ID: <aabsnm0t7k@drn.newsguy.com>

In article <3cc9690d$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com says...
>
>
>Thomas, how serious is that? How often do you ltrim/rtrim a column with
>nulls? A lot I bet. To me it seems like a very serious problem with possible
>widespread ramifications, since it changes in a fundatmental way the way ltrim
>and rtrim work
>

well, maybe -- maybe not. There was ambiguity here:

ops$tkyte_at_ORA815.US.ORACLE.COM> select ltrim('x',null) from dual;

L
-

ops$tkyte_at_ORA815.US.ORACLE.COM> exec dbms_output.put_line( ltrim('x',null) ) x

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA815.US.ORACLE.COM> begin
  2 for x in ( select ltrim('x',null) a, 'x' b from dual )   3 loop

  4     dbms_output.put_line ( 'x.a = ' || x.a );
  5     dbms_output.put_line ( 'x.b = ' || ltrim(x.b,null) );

  6 end loop;
  7 end;
  8 /
x.a =
x.b = x

PL/SQL procedure successfully completed.

The behavior of LTRIM in plsql *was wrong*.

That has been fixed.

As with all fixes -- that can sometimes affect you.... So,

>In article <aaad0l012ie_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com>
>wrote:
>>In article <3cc85ad7$0$20803$45beb828_at_newscene.com>, julio33_at_whomail.com
>> says...
>>>
>>>
>>>please!
>>>
>>>we have a procedure that runs fine in 8i but not in 9i. Should work in both.
>>>can someone run in 9i 9.0.1.0.0
>>>
>>
>>Interesting, the behavior of this
>>
>>function de_quote( p_str in varchar2, p_enc_by in varchar2 ) return varchar2
>>is
>>begin
>> return replace( ltrim( rtrim( p_str, p_enc_by ), p_enc_by ),
>> p_enc_by||p_enc_by, p_enc_by );
>>end de_quote;
>>
>>differes in 817 from 815 (started before 9i) IF p_enc_by is NULL.
>>
>>Suggested fix:
>>
>>
>>
>> if ( p_delim is NOT NULL )
>> then
>> for i in 1 .. l_n loop
>> g_words(i) := de_quote( g_words(i), p_delim );
>> end loop;
>> end if;
>>
>>
>>in the procedure PARSE. That'll fix it.
>>
>>
>>Looks like ltrim/rtrim changed with regards to NULL'ness
>>
>>
>>ops$tkyte_at_ORA815.US.ORACLE.COM> exec dbms_output.put_line( 'ltrim = ' ||
>>ltrim('x',null) );
>>ltrim = x
>>
>>PL/SQL procedure successfully completed.
>>
>>ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( 'ltrim = ' ||
>>ltrim('x',null) );
>>ltrim =
>>
>>PL/SQL procedure successfully completed.
>>
>>
>>and that is the underlying cause.
>>
>>>
>>>here is the statment that will run it
>>>
>>> select delimited.word('number1_at_number2@number3@', 1, '', '@')
>>>, delimited.word('number1_at_number2@number3@', 2, '', '@') from dual
>>>
>>>
>>>here is the source
>>>
>>>CREATE OR REPLACE package body delimited
>>>as
>>> type vcArray is table of varchar2(2000) index by binary_integer;
>>>
>>> g_words vcArray;
>>> g_empty vcArray;
>>> g_last_string varchar2(4096);
>>>
>>> function de_quote( p_str in varchar2, p_enc_by in varchar2 ) return
>>>varchar2
>>> is
>>> begin
>>> return replace( ltrim( rtrim( p_str, p_enc_by ), p_enc_by ),
>>> p_enc_by||p_enc_by, p_enc_by );
>>> end de_quote;
>>>
>>>
>>> procedure parse( p_str in varchar2,
>>> p_delim in varchar2,
>>> p_sep in varchar2 )
>>> is
>>> l_n number default 1;
>>> l_in_quote boolean default FALSE;
>>> l_ch char(1);
>>> l_len number default nvl(length( p_str ),0);
>>> begin
>>> if ( l_len = 0 ) then
>>> return;
>>> end if;
>>>
>>> g_words := g_empty;
>>> g_words(1) := NULL;
>>>
>>> for i in 1 .. l_len loop
>>> l_ch := substr( p_str, i, 1 );
>>> if ( l_ch = p_delim ) then
>>> l_in_quote := NOT l_in_quote;
>>> end if;
>>> if ( l_ch = p_sep AND NOT l_in_quote ) then
>>> l_n := l_n + 1;
>>> g_words(l_n) := NULL;
>>> else
>>> g_words(l_n) := g_words(l_n)||l_ch;
>>> end if;
>>> end loop;
>>>
>>> for i in 1 .. l_n loop
>>> g_words(i) := de_quote( g_words(i), p_delim );
>>> end loop;
>>> end parse;
>>>
>>>
>>> function word( p_str in varchar2,
>>> p_n in varchar2,
>>> p_enclosed_by in varchar2 default '''',
>>> p_separated_by in varchar2 default ',' ) return
>>>varchar2
>>> is
>>> begin
>>> if ( g_last_string is NULL or p_str <> g_last_string ) then
>>> g_last_string := p_str;
>>> parse( p_str, p_enclosed_by, p_separated_by );
>>> end if;
>>> return g_words( p_n );
>>> exception
>>> when no_data_found then return NULL;
>>> end;
>>>
>>>end delimited;
>>>/
>>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Apr 26 2002 - 10:45:58 CDT