depends what you think null is. I think. In my mind null means unknown.
removing unknown characters from a string must be give an unknown result
no? put another way you say NULL is not present in the string 'x'. How do
you know?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"julio" <julio33_at_whomail.com> wrote in message
news:3cc98933$0$20803$45beb828_at_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 - 14:17:44 CDT
