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

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

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;
>>/
>
Received on Fri Apr 26 2002 - 09:52:18 CDT