Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tom, how serious is that problem - ltrim/rtrim changed with regards to NULL'ness

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

From: julio <julio33_at_whomail.com>
Date: 26 Apr 2002 12:09:24 -0500
Message-ID: <3cc98933$0$20803$45beb828@newscene.com>

But Tom how can ltrim or rtrim ('x', null) be null. The doc says

'LTRIM removes characters from the left of char, with all the leftmost characters that appear in set removed'

Null is not present in x, how can removing it return null, should it not return x. That is unless there is a null in x that is part of varchar2 and removing it clears out the value. Barring that souldn't the way that pl/sql does it be the correct one?

In article <aabsnm0t7k_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_oracle.com> wrote:
>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;
>>>>/
>>>
>
Received on Fri Apr 26 2002 - 12:09:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US